в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:52Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/Recordset Open - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия