Dim objRS As ADODB.Recordset Dim objCon As ADODB.Connection Dim getConString As String 'Variable for the connectionstring Dim sqlProvider As String Set objCon = New ADODB.Connection Set objRS = New ADODB.Recordset getConString = "Provider=SQLOLEDB.1" & _ ";Persist Security Info=False" & _ ";User ID=用户名" & _ ";Password=密码" & _ ";Initial Catalog=sqlserver数据库名" & _ ";Data Source=服务器名或IP" & _ ";Network Library=DBMSSOCN" GetCon Private Sub GetCon() If objCon.State = adStateOpen Then objCon.CloseWith objCon .ConnectionString = getConString .Open End With End Sub Sub GetRecordSet(strSource As String) If objRS.State = adStateOpen Then objRS.Close 'If the database is open close before getting a new recordset 'On Error GoTo Errorhandler 'I take care of eventual errorsSelect Case bolEditReadRS 'Tells which kind of recordset to get Case False With objRS .ActiveConnection = objCon .CursorLocation = adUseClient .CursorType = adOpenKeyset 'Move the cursor in any direction and bookable .LockType = adLockReadOnly 'Editing is not possible .Source = strSource 'What Recordset to get .Open .MoveFirst End With Case True With objRS .ActiveConnection = objCon .CursorLocation = adUseClient .CursorType = adOpenKeyset 'Move the cursor in any direction and bookable .LockType = adLockOptimistic 'Editing is possible .Source = strSource 'What Recordset to get .Open .MoveFirst End With End Select End Sub GetRecordSet ("sql语句")
Dim objCon As ADODB.Connection
Dim getConString As String 'Variable for the connectionstring
Dim sqlProvider As String
Set objCon = New ADODB.Connection
Set objRS = New ADODB.Recordset
getConString = "Provider=SQLOLEDB.1" & _
";Persist Security Info=False" & _
";User ID=用户名" & _
";Password=密码" & _
";Initial Catalog=sqlserver数据库名" & _
";Data Source=服务器名或IP" & _
";Network Library=DBMSSOCN"
GetCon
Private Sub GetCon()
If objCon.State = adStateOpen Then objCon.CloseWith objCon
.ConnectionString = getConString
.Open
End With
End Sub
Sub GetRecordSet(strSource As String)
If objRS.State = adStateOpen Then objRS.Close 'If the database is open close before getting a new recordset
'On Error GoTo Errorhandler 'I take care of eventual errorsSelect Case bolEditReadRS 'Tells which kind of recordset to get
Case False
With objRS
.ActiveConnection = objCon
.CursorLocation = adUseClient
.CursorType = adOpenKeyset 'Move the cursor in any direction and bookable
.LockType = adLockReadOnly 'Editing is not possible
.Source = strSource 'What Recordset to get
.Open
.MoveFirst
End With
Case True
With objRS
.ActiveConnection = objCon
.CursorLocation = adUseClient
.CursorType = adOpenKeyset 'Move the cursor in any direction and bookable
.LockType = adLockOptimistic 'Editing is possible
.Source = strSource 'What Recordset to get
.Open
.MoveFirst
End With
End Select
End Sub
GetRecordSet ("sql语句")