Public Function exec_procedure(sql As String, Optional i As Integer = 0) As String exec_procedure = "" If sql = "" Then Exit Function Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Set cnn1 = New ADODB.Connection Call cnn1.Open(connectstr()) Set rst1 = New ADODB.Recordset rst1.ActiveConnection = cnn1 On Error GoTo errorhandle Call rst1.Open(sql, cnn1, adOpenKeyset, adLockReadOnly) If i <> 555 And i <> 999 Then If rst1.RecordCount = -1 Then exec_procedure = CStr(rst1.Fields(i).value) If rst1.RecordCount = 1 Then exec_procedure = CStr(rst1.Fields(i).value) End If If i = 999 Then ReDim arrary_rpt(rst1.Fields.Count) As String If (rst1.RecordCount = 1 Or rst1.RecordCount = -1) And rst1.Fields.Count >= 1 Then Dim k As Integer For k = 0 To rst1.Fields.Count - 1 arrary_rpt(k) = rst1.Fields(k).value Next Else For k = 0 To rst1.Fields.Count - 1 arrary_rpt(k) = "" Next End If End If Set rst1 = Nothing cnn1.Close Set cnn1 = Nothing Exit Function errorhandle: Err.Clear Set rst1 = Nothing Set cnn1 = Nothing Exit Function End Function
Public Function ConnectString() _ As String 'returns a DB ConnectString 'the following is example of access database
ConnectString = "Provider=" & _ "Microsoft.Jet.OLEDB." & _ "4.0;Data Source=" & _ App.Path & "\NWind.mdb" End Function Public Function ExecuteSQL(ByVal SQL _ As String, MsgString As String) _ As ADODB.Recordset 'executes SQL and returns Recordset Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL) Set cnn = New ADODB.Connection cnn.Open ConnectString If InStr("INSERT,DELETE,UPDATE", _ UCase$(sTokens(0))) Then cnn.Execute SQL MsgString = sTokens(0) & _ " query successful" Else Set rst = New ADODB.Recordset rst.Open Trim$(SQL), cnn, _ adOpenKeyset, _ adLockOptimistic rst.MoveLast 'get RecordCount Set ExecuteSQL = rst MsgString = rst.RecordCount & _ " records found from SQL" End If ExecuteSQL_Exit: Set rst = Nothing Set cnn = Nothing Exit Function
ExecuteSQL_Error: MsgString = "ExecuteSQL Error: " & _ Err.Description Resume ExecuteSQL_Exit End Function
或者创建ADODB库的 connect对象。
Global gconSQLServer As ADODB.Connection '声明全局连接对象
'创建数据库连接全局变量,程序启动时开始连接,一直到程序结束时断开
strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=数据库名称;Data Source=数据库所在的计算机名称
Set gconSQLServer = New ADODB.Connection
gconSQLServer.Open strConnection '这样就建立了连接
先在odbc中配起来。
程序中只要写
Set conn = New ADODB.Connectionconn.ConnectionString = "dsn=;uid=;pwd=;"
conn.Open
Public Function exec_procedure(sql As String, Optional i As Integer = 0) As String
exec_procedure = ""
If sql = "" Then Exit Function
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Set cnn1 = New ADODB.Connection
Call cnn1.Open(connectstr())
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1
On Error GoTo errorhandle
Call rst1.Open(sql, cnn1, adOpenKeyset, adLockReadOnly)
If i <> 555 And i <> 999 Then
If rst1.RecordCount = -1 Then exec_procedure = CStr(rst1.Fields(i).value)
If rst1.RecordCount = 1 Then exec_procedure = CStr(rst1.Fields(i).value)
End If
If i = 999 Then
ReDim arrary_rpt(rst1.Fields.Count) As String
If (rst1.RecordCount = 1 Or rst1.RecordCount = -1) And rst1.Fields.Count >= 1 Then
Dim k As Integer
For k = 0 To rst1.Fields.Count - 1
arrary_rpt(k) = rst1.Fields(k).value
Next
Else
For k = 0 To rst1.Fields.Count - 1
arrary_rpt(k) = ""
Next
End If
End If
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
Exit Function
errorhandle:
Err.Clear
Set rst1 = Nothing
Set cnn1 = Nothing
Exit Function
End Function
wwwwwwwwwwwww
As String
'returns a DB ConnectString
'the following is example of access database
ConnectString = "Provider=" & _
"Microsoft.Jet.OLEDB." & _
"4.0;Data Source=" & _
App.Path & "\NWind.mdb"
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = rst.RecordCount & _
" records found from SQL"
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "ExecuteSQL Error: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function