VBA/Excel/Access/Word/Access/Recordset Open
Содержание
- 1 adAsyncExecute - The source is executed asynchronously.
- 2 adAsyncFetchNonBlocking - The main thread never blocks when fetching.
- 3 adAsyncFetch - The initial quantity specified in the Initial Fetch Size property is fetched.
- 4 adCmdFile - The source is evaluated as a persisted recordset.
- 5 adCmdStoredProc - The provider evaluates the source as a stored procedure.
- 6 adCmdTable - A SQL query is generated to return all rows from the table named in the source.
- 7 adCmdTableDirect - The provider returns all rows in the table named in the source.
- 8 adCmdUnknown - The type of command in the source is unknown.
- 9 Connection error handler
- 10 Creating a Recordset Using a Connection Object
- 11 Creating a Recordset Using a Connection String
- 12 Creating a Recordset Using the ActiveConnection Property
- 13 Get Recordset content by calling Recordset.GetString
- 14 Opening a DAO Recordset Using a SQL SELECT Statement
- 15 Opening a DAO Recordset Using a Table
- 16 Opening a Recordset
- 17 Opening a Recordset Based on an SQL Statement
- 18 Opening a Recordset Based on a Table or Query
- 19 Opening a Recordset Based on a Table or Query with SQL command
- 20 Opening a Recordset Based on Criteria
- 21 Opening a Recordset Directly
- 22 Opening a Recordset with Inconsistent Updates
- 23 Options of Open Method: adCmdText - The provider evaluates the source as a command
- 24 Recordset.Open method
- 25 sets the locking type in the call to the Open method
- 26 The RecordCount Property Is Not Supported with a Forward-Only Recordset
- 27 Use dbOpenSnapshot when opening an recordset
adAsyncExecute - The source is executed asynchronously.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adAsyncExecute rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adAsyncFetchNonBlocking - The main thread never blocks when fetching.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adAsyncFetchNonBlocking rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adAsyncFetch - The initial quantity specified in the Initial Fetch Size property is fetched.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adAsyncFetch rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adCmdFile - The source is evaluated as a persisted recordset.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adCmdFile rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adCmdStoredProc - The provider evaluates the source as a stored procedure.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adCmdStoredProc rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adCmdTable - A SQL query is generated to return all rows from the table named in the source.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adCmdTable rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adCmdTableDirect - The provider returns all rows in the table named in the source.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adCmdTableDirect rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
adCmdUnknown - The type of command in the source is unknown.
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adCmdUnknown rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
Connection error handler
<source lang="vb">
Sub ConnectionErrorHandler()
On Error GoTo Except Dim Connection As ADODB.Connection Set Connection = CurrentProject.Connection Dim Recordset As New ADODB.Recordset Recordset.Open "badTable", Connection Exit Sub
Except:
Dim ADOError As ADODB.Error For Each ADOError In Connection.Errors MsgBox "ADO Error: " & ADOError.Description & " Native Error: " & _ ADOError.NativeError & " SQL State: " & _ ADOError.SQLState & "Source: " & _ ADOError.Source, vbCritical, "Error Number: " & ADOError.Number Next ADOError
End Sub
</source>
Creating a Recordset Using a Connection Object
<source lang="vb">
Sub CreateRecordset3()
Dim cnn As ADODB.Connection Dim rst1 As ADODB.Recordset Dim rst2 As ADODB.Recordset Set cnn = New ADODB.Connection Set rst1 = New ADODB.Recordset Set rst2 = New ADODB.Recordset Set cnn = CurrentProject.Connection rst1.ActiveConnection = cnn rst1.Open "Select * From Employees" rst2.ActiveConnection = cnn rst2.Open "Select * From Products" Debug.Print rst1.GetString Debug.Print rst2.GetString rst1.Close rst2.Close cnn.Close Set rst1 = Nothing Set rst2 = Nothing Set cnn = Nothing
End Sub
</source>
Creating a Recordset Using a Connection String
<source lang="vb">
Sub CreateRecordset1()
"Declare and instantiate the recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset "Open the recordset rst.Open "Select * From Employees", CurrentProject.Connection "Print its contents Debug.Print rst.GetString "Close and destroy the recordset rst.Close Set rst = Nothing
End Sub
</source>
Creating a Recordset Using the ActiveConnection Property
<source lang="vb">
Sub CreateRecordset2()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * From Employees" Debug.Print rst.GetString rst.Close Set rst = Nothing
End Sub
</source>
Get Recordset content by calling Recordset.GetString
<source lang="vb">
Sub CreateRecordset1()
"Declare and instantiate the recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset "Open the recordset rst.Open "Select * From Employees", CurrentProject.Connection "Print its contents Debug.Print rst.GetString "Close and destroy the recordset rst.Close Set rst = Nothing
End Sub
</source>
Opening a DAO Recordset Using a SQL SELECT Statement
<source lang="vb">
Sub recorder()
Dim myDatabase As dao.Database Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb") Dim myRecordset As dao.Recordset Set myRecordset = myDatabase.OpenRecordset(Name:="SELECT * FROM Customers WHERE Country="Germany"", Type:=dbOpenDynaset)
End Sub
</source>
Opening a DAO Recordset Using a Table
<source lang="vb">
Sub dao()
Dim myDatabase As dao.Database Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb") Dim myRecordset As dao.Recordset Set myRecordset = myDatabase.OpenRecordset(Name:="Customers", Type:=dbOpenTable)
End Sub
</source>
Opening a Recordset
<source lang="vb">
Sub OpenRst()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset With rst .Source = "Select * from Employees" .ActiveConnection = CurrentProject.Connection .Open Debug.Print rst.Fields.count .Close End With Set rst = Nothing
End Sub
</source>
Opening a Recordset Based on an SQL Statement
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set rst = conn.Execute("Select * from Employees") Debug.Print rst("LastName") & ", " & rst("FirstName") rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Opening a Recordset Based on a Table or Query
<source lang="vb">
Sub OpenRst_TableOrQuery()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Set conn = CurrentProject.Connection Set rst = New ADODB.Recordset rst.Open "Employees", conn Debug.Print "CursorType: " & rst.CursorType & vbCr & "LockType: " & rst.LockType & vbCr _ & "Cursor Location: " & rst.CursorLocation Do Until rst.EOF Debug.Print rst.Fields(1) rst.MoveNext Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Opening a Recordset Based on a Table or Query with SQL command
<source lang="vb">
Sub ConnectAndExec()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set rst = conn.Execute("Select * from Employees") Debug.Print rst.Source rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Opening a Recordset Based on Criteria
<source lang="vb">
Sub OpenRst_WithCriteria()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set rst = New ADODB.Recordset rst.Open "SELECT * FROM Employees WHERE ReportsTo is Null", _ conn, adOpenForwardOnly, adLockReadOnly Do While Not rst.EOF Debug.Print rst.Fields(1).Value rst.MoveNext Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Opening a Recordset Directly
<source lang="vb">
Sub OpenRst_Directly() Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset With rst .Source = "Select * From Employees" .ActiveConnection = CurrentProject.Connection .Open End With MsgBox rst.Fields(1) rst.Close Set rst = Nothing
End Sub
</source>
Opening a Recordset with Inconsistent Updates
<source lang="vb">
Sub InconsistentUpdates()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Properties("Jet OLEDB:Inconsistent") = True rst.Open Source:="Select * from Employees " & _ "INNER JOIN Projects " & _ "ON Employees.ClientID = Projects.ClientID", _ Options:=adCmdText rst("Projects.ClientID") = 1 rst.Update Debug.Print rst("Projects.ClientID") rst.Close Set rst = Nothing
End Sub
</source>
Options of Open Method: adCmdText - The provider evaluates the source as a command
<source lang="vb">
Sub OptionsParameter()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.Open "Select * from Employees", _ Options:=adCmdText rst("City") = "Village" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
Recordset.Open method
<source lang="vb">
Sub RecSetOpen()
Dim rst As ADODB.Recordset Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set rst = New ADODB.Recordset With rst .Open "Select * From Customers", _ strConnection, adOpenForwardOnly .Save CurrentProject.Path & "\MyResultset.dat" .Close End With Set rst = Nothing
End Sub
</source>
sets the locking type in the call to the Open method
<source lang="vb">
Private Sub Form_Open(Cancel As Integer)
Dim con As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strFrmNm As String Set myRecordset = New ADODB.Recordset Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\mydb.mdb;" myRecordset.Open "SELECT * FROM Employees", con, adOpenKeyset, adLockOptimistic Set Me.Recordset = myRecordset myRecordset.Close con.Close Set myRecordset = Nothing Set con = Nothing
End Sub
</source>
The RecordCount Property Is Not Supported with a Forward-Only Recordset
<source lang="vb">
Sub ForwardOnlyRecordset()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from Employees" Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Use dbOpenSnapshot when opening an recordset
<source lang="vb">
Sub exaRecordsets()
Dim db As Database Dim rsTable As Recordset Dim rsDyna As Recordset Dim rsSnap As Recordset Set db = CurrentDb Set rsTable = db.OpenRecordset("Employees") Debug.Print "TableCount: " & rsTable.RecordCount Set rsDyna = db.OpenRecordset("Employees", dbOpenDynaset) Debug.Print "DynaCount: " & rsDyna.RecordCount rsDyna.MoveLast Debug.Print "DynaCount: " & rsDyna.RecordCount Set rsSnap = db.OpenRecordset("Employees", dbOpenSnapshot) Debug.Print "SnapCount: " & rsSnap.RecordCount rsSnap.MoveLast Debug.Print "SnapCount: " & rsSnap.RecordCount rsTable.Close rsDyna.Close rsSnap.Close
End Sub
</source>