Public cn As New ADODB.ConnectionSub Main() dbopen End Sub Public Sub DBOpen()
On Error GoTo ErrorConnect: cn.ConnectionTimeout = 25 cn.Provider = "sqloledb" cn.Properties("Data Source").Value = sServerName cn.Properties("Initial Catalog").Value = sDatabaseName cn.Properties("User ID").Value = sUserName cn.Properties("Password").Value = sPassword Screen.MousePointer = vbHourglass cn.Open Screen.MousePointer = vbDefault Exit Sub ErrorConnect: msgbox "There is An Error on Connection!" End Sub
dim cn as adodb.connection cn.open "DSN=ODBC连接SQL名" '相当于连接字符串或者cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=."
Private conn As New ADODB.Connection Private rs As New ADODB.Recordset Private Connstr As String Connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & "Persist Security Info=False;Initial Catalog=" & databaseName & ";Data Source=" & ServerName conn.Open Connstr rs.Open tableName, conn, adOpenKeyset, adLockPessimistic
Option Explicit Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As Recordset Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCnn As String Dim sTokens() As String Set cnn = New ADODB.Connection
On Error GoTo ExecuteSQL_Error sTokens = Split(SQL) strCnn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & "Persist Security Info=False;Initial Catalog=" & databaseName & ";Data Source=" & ServerName cnn.Open strCnn 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 SQL, cnn, adOpenKeyset, adLockOptimistic Set ExecuteSQL = rst MsgString = "查询到" & rst.RecordCount & "条记录" Debug.Print MsgString End If ExecuteSQL_Exit: Set cnn = Nothing Set rst = Nothing Exit Function
ExecuteSQL_Error: MsgBox "没有连接到数据库", vbOKOnly + vbExclamation, "警告" Resume ExecuteSQL_Exit End Function
dbopen
End Sub
Public Sub DBOpen()
On Error GoTo ErrorConnect:
cn.ConnectionTimeout = 25
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = sServerName
cn.Properties("Initial Catalog").Value = sDatabaseName
cn.Properties("User ID").Value = sUserName
cn.Properties("Password").Value = sPassword
Screen.MousePointer = vbHourglass
cn.Open
Screen.MousePointer = vbDefault
Exit Sub
ErrorConnect:
msgbox "There is An Error on Connection!"
End Sub
cn.open "DSN=ODBC连接SQL名" '相当于连接字符串或者cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=."
Private rs As New ADODB.Recordset
Private Connstr As String
Connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" &
"Persist Security Info=False;Initial Catalog=" & databaseName & ";Data Source=" & ServerName
conn.Open Connstr
rs.Open tableName, conn, adOpenKeyset, adLockPessimistic
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strCnn As String
Dim sTokens() As String
Set cnn = New ADODB.Connection
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
strCnn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" &
"Persist Security Info=False;Initial Catalog=" & databaseName & ";Data Source=" & ServerName
cnn.Open strCnn
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 SQL, cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & "条记录"
Debug.Print MsgString
End If
ExecuteSQL_Exit:
Set cnn = Nothing
Set rst = Nothing
Exit Function
ExecuteSQL_Error:
MsgBox "没有连接到数据库", vbOKOnly + vbExclamation, "警告"
Resume ExecuteSQL_Exit
End Function