VBA学习笔记


Dim Str, Val, n, Codes
Dim eyear As String
Dim etype As String
Dim ecode As String
Dim line As Integer

 1 Function SearchEvents(ByVal year As String, ByVal etype As String, ByVal code As String)
 2     Dim lastRows As Integer
 3      strSQL = "exec PROC_Evaluation_EvalProgress_Select_Backup " & year & "," & etype & "," & code
 4      'strSQL = "exec PROC_Evaluation_index_EvaluatorStatus "
 5        
 6     
 7   
 8         Call rsOpen
 9             If rs.RecordCount <> 0 Then
10                i = line
11                line = line + rs.RecordCount
12                 Sheet2.Cells(4 + i, 1).CopyFromRecordset rs
13                 
14             Else
15                 MsgBox Q004
16             End If
17         Call rsClose
18    
19 End Function

1 Function TypeChange1(ByVal Source As String, ByVal TargetType As String, ByVal Target As String) As String
2     If Source = TargetType Then
3         Source = Target
4     End If
5     TypeChange1 = Source
6 End Function

 1 Option Explicit
 2 
 3 '''''廋惓梡 Server
 4 'Public Const strIP        As String = "172.20.3.135"
 5 'Public Const strUser      As String = "develop"
 6 'Public Const strPasswd    As String = "develop"
 7 'Public Const strDB        As String = "KOUBAI"
 8 
 9 '''''嫵堢梡 Server
10 Public Const strIP      As String = "10.100.1.220"
11 Public Const strUser      As String = "sa"
12 Public Const strPasswd    As String = ""
13 Public Const strDB        As String = "dbEvaluation"
14 
15 '''''杮斣梡 Server
16 'Public Const strIP        As String = "172.20.5.68"
17 'Public Const strUser      As String = "sa"
18 'Public Const strPasswd    As String = "bpo"
19 'Public Const strDB        As String = "dbKoubai"
20 
21 
22 Public strSQL       As String
23 Public cn           As New ADODB.Connection
24 Public rs           As New ADODB.Recordset
25 
26 Public Sub cnOpen()
27 On Error GoTo err
28    If cn Is Nothing Then
29     Set cn = New ADODB.Connection
30    End If
31     If cn.State = 0 Then
32         With cn
33             .Provider = "SQLOLEDB.1"
34             .ConnectionString = "Persist Security Info=True;" & _
35                                 "Data Source=" & strIP & ";" & _
36                                 "Initial Catalog=" & strDB & ";" & _
37                                 "User ID=" & strUser & ";" & _
38                                 "Password=" & strPasswd
39             .ConnectionTimeout = 800
40             .CommandTimeout = 800
41             .CursorLocation = adUseClient
42             .Open
43          End With
44     End If
45     Exit Sub
46 err:
47     MsgBox "DB偲僐僱僋僩偱偒傑偣傫偱偟偨丅"
48     End
49 End Sub
50 
51 Public Sub cnClose()
52    If Not cn Is Nothing Then
53       If cn.State <> 0 Then cn.Close
54       Set cn = Nothing
55    End If
56 End Sub
57 
58 Public Sub rsOpen()
59    If rs Is Nothing Then
60       Set rs = New ADODB.Recordset
61    End If
62    If rs.State = 0 Then
63     If cn.State = adStateClosed Then cnOpen
64    
65     If cn.State = adStateOpen Then rs.Open strSQL, cn
66    End If
67    
68 End Sub
69 
70 Public Sub rsClose()
71    If Not rs Is Nothing Then
72       If rs.State <> 0 Then rs.Close
73       Set rs = Nothing
74    End If
75 End Sub

另外，在调用存储过程的时候，会遇到问题，数据库抽出数据时存放在ADODB.Recordset中的，每次抽出数据都会存放在rs中，但是，如果存储过程中含有临时表，那么，每执行一次临时表，ADODB.Recordset会自动关闭，因此外部使用ADODB.Recordset中的数据时便会报错，无法打开ADODB.Recordset，对于这种问题的解决方案是：将将存储过程中的临时表统统替换为sql语句，这样就不存在临时表，ADODB.Recordset也就会正常开关。