VBA/Excel/Access/Word/Access/Recordset Open

Материал из VB Эксперт
Перейти к: навигация, поиск

Содержание

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>