#$>ATAbsolutePage, PageCount, and PageSize Properties Example
This example uses the AbsolutePage, PageCount, and PageSize properties to display names and hire dates from the Employee table five records at a time.
Public Sub AbsolutePageX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer
' Open a recordset using a client cursor
' for the employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable
' Display names and hire dates, five records
' at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = ""
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & " " & _
rstEmployees!lname & " " & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close
End Sub
#$>ATAbsolutePosition and CursorLocation Properties Example
This example demonstrates how the AbsolutePosition property can track the progress of a loop that enumerates all the records of a Recordset. It uses the CursorLocation property to enable the AbsolutePosition property by setting the cursor to a client cursor.
Public Sub AbsolutePositionX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
' Open a recordset for the Employee table
' using a client cursor.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable
' Enumerate Recordset.
Do While Not rstEmployees.EOF
' Display current record information.
strMessage = "Employee: " & rstEmployees!lName & vbCr & _
"(record " & rstEmployees.AbsolutePosition & _
" of " & rstEmployees.RecordCount & ")"
If MsgBox(strMessage, vbOKCancel) = vbCancel _
Then Exit Do
rstEmployees.MoveNext
Loop
rstEmployees.Close
End Sub
#$>ATActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example
This example uses the ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction properties to execute a stored procedure.
Public Sub ActiveConnectionX()
Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String
' Define a command object for a stored procedure.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
' Define the stored procedure's input parameter.
intRoyalty = Trim(InputBox( _
"Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
' Create a recordset by executing the command.
Set rstByRoyalty = cmdByRoyalty.Execute()
' Open the Authors table to get author names for display.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", strCnn, , , adCmdTable
' Print current data in the recordset, adding
' author names from Authors table.
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop
rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub
#$>ATActualSize and DefinedSize Properties Example
This example uses the ActualSize and DefinedSize properties to display the defined size and actual size of a field.
Public Sub ActualSizeX()
Dim rstStores As ADODB.Recordset
Dim strCnn As String
' Open a recordset for the Stores table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstStores = New ADODB.Recordset
rstStores.Open "stores", strCnn, , , adCmdTable
' Loop through the recordset displaying the contents
' of the stor_name field, the field's defined size,
' and its actual size.
rstStores.MoveFirst
Do Until rstStores.EOF
MsgBox "Store name: " & rstStores!stor_name & _
vbCr & "Defined size: " & _
rstStores!stor_name.DefinedSize & _
vbCr & "Actual size: " & _
rstStores!stor_name.ActualSize & vbCr
rstStores.MoveNext
Loop
rstStores.Close
End Sub
#$>ATAttributes and Name Properties Example
This examples displays the value of the Attributes property for Connection, Field, and Property objects. It uses the Name property to display the name of each Field and Property object.
Public Sub AttributesX
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim proLoop As ADODB.Property
Dim strCnn As String
' Open connection and recordset.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", cnn1, , , adCmdTable
' Display the attributes of the connection.
Debug.Print "Connection attributes = " & _
cnn1.Attributes
' Display the attributes of the Employee table's
' fields.
Debug.Print "Field attributes:"
For Each fldLoop In rstEmployees.Fields
Debug.Print " " & fldLoop.Name & " = " & _
fldLoop.Attributes
Next fldLoop
' Display the attributes of the Employee table's
' properties.
Debug.Print "Property attributes:"
For Each proLoop In rstEmployees.Properties
Debug.Print " " & proLoop.Name & " = " & _
proLoop.Attributes
Next proLoop
rstEmployees.Close
cnn1.Close
End Sub
#$>ATBOF, EOF, and Bookmark Properties Example
This example uses the BOF and EOF properties to display a message if a user tries to move past the first or last record of a Recordset. It uses the Bookmark property to let the user flag a record in a Recordset and return to it later.
Public Sub BOFX()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intCommand As Integer
Dim varBookmark As Variant
' Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
' Use client cursor to enable AbsolutePosition property.
rstPublishers.CursorLocation = adUseClient
rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
"ORDER BY pub_name", strCnn, , , adCmdText
rstPublishers.MoveFirst
Do While True
' Display information about current record
' and get user input.
strMessage = "Publisher: " & rstPublishers!pub_name & _
vbCr & "(record " & rstPublishers.AbsolutePosition & _
" of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
"Enter command:" & vbCr & _
"[1 - next / 2 - previous /" & vbCr & _
"3 - set bookmark / 4 - go to bookmark]"
intCommand = Val(InputBox(strMessage))
Select Case intCommand
' Move forward or backward, trapping for BOF
' or EOF.
Case 1
rstPublishers.MoveNext
If rstPublishers.EOF Then
MsgBox "Moving past the last record." & _
vbCr & "Try again."
rstPublishers.MoveLast
End If
Case 2
rstPublishers.MovePrevious
If rstPublishers.BOF Then
MsgBox "Moving past the first record." & _
vbCr & "Try again."
rstPublishers.MoveFirst
End If
' Store the bookmark of the current record.
Case 3
varBookmark = rstPublishers.Bookmark
' Go to the record indicated by the stored
' bookmark.
Case 4
If IsEmpty(varBookmark) Then
MsgBox "No Bookmark set!"
Else
rstPublishers.Bookmark = varBookmark
End If
Case Else
Exit Do
End Select
Loop
rstPublishers.Close
End Sub
#$>ATCacheSize Property Example
This example uses the CacheSize property to show the difference in performance for an operation performed with and without a 30-record cache.
Public Sub CacheSizeX()
Dim rstRoySched As ADODB.Recordset
Dim strCnn As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNoCache As Single
Dim sngCache As Single
Dim intLoop As Integer
Dim strTemp As String
' Open the RoySched table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstRoySched = New ADODB.Recordset
rstRoySched.Open "roysched", strCnn, , , adCmdTable
' Enumerate the Recordset object twice and record
' the elapsed time.
sngStart = Timer
For intLoop = 1 To 2
rstRoySched.MoveFirst
Do While Not rstRoySched.EOF
' Execute a simple operation for the
' performance test.
strTemp = rstRoySched!title_id
rstRoySched.MoveNext
Loop
Next intLoop
sngEnd = Timer
sngNoCache = sngEnd - sngStart
' Cache records in groups of 30 records.
rstRoySched.MoveFirst
rstRoySched.CacheSize = 30
sngStart = Timer
' Enumerate the Recordset object twice and record
' the elapsed time.
For intLoop = 1 To 2
rstRoySched.MoveFirst
Do While Not rstRoySched.EOF
' Execute a simple operation for the
' performance test.
strTemp = rstRoySched!title_id
rstRoySched.MoveNext
Loop
Next intLoop
sngEnd = Timer
sngCache = sngEnd - sngStart
' Display performance results.
MsgBox "Caching Performance Results:" & vbCr & _
" No cache: " & Format(sngNoCache, _
"##0.000") & " seconds" & vbCr & _
" 30-record cache: " & Format(sngCache, _
"##0.000") & " seconds"
rstRoySched.Close
End Sub
#$>ATConnectionString, ConnectionTimeout, and State Properties Example
This example demonstrates different ways of using the ConnectionString property to open a Connection object. It also uses the ConnectionTimeout property to set a connection timeout period, and the State property to check the state of the connections. The GetState function is required for this procedure to run.
Public Sub ConnectionStringX()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim cnn3 As ADODB.Connection
Dim cnn4 As ADODB.Connection
' Open a connection without using a Data Source Name (DSN).
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd;database=pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open
' Open a connection using a DSN and ODBC tags.
Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
cnn2.Open
' Open a connection using a DSN and OLE DB tags.
Set cnn3 = New ADODB.Connection
cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
cnn3.Open
' Open a connection using a DSN and individual
' arguments instead of a connection string.
Set cnn4 = New ADODB.Connection
cnn4.Open "Pubs", "sa", "pwd"
' Display the state of the connections.
MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
"cnn2 state: " & GetState(cnn1.State) & vbCr & _
"cnn3 state: " & GetState(cnn1.State) & vbCr & _
"cnn4 state: " & GetState(cnn1.State)
cnn4.Close
cnn3.Close
cnn2.Close
cnn1.Close
End Sub
Public Function GetState(intState As Integer) As String
Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select
End Function
This example demonstrates the Count property with two collections in the Employee database. The property obtains the number of objects in each collection, and sets the upper limit for loops that enumerate these collections. Another way to enumerate these collections without using the Count property would be to use For Each...Next statements.
Public Sub CountX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim intloop As Integer
' Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
' Print information about Fields collection.
Debug.Print rstEmployees.Fields.Count & _
" Fields in Employee"
For intloop = 0 To rstEmployees.Fields.Count - 1
Debug.Print " " & rstEmployees.Fields(intloop).Name
Next intloop
' Print information about Properties collection.
Debug.Print rstEmployees.Properties.Count & _
" Properties in Employee"
For intloop = 0 To rstEmployees.Properties.Count - 1
Debug.Print " " & rstEmployees.Properties(intloop).Name
Next intloop
rstEmployees.Close
End Sub
#$>ATCursorType, LockType, and EditMode Properties Example
This example demonstrates setting the CursorType and LockType properties before opening a Recordset. It also shows the value of the EditMode property under various conditions. The EditModeOutput function is required for this procedure to run.
Public Sub EditModeX()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
' Open recordset with data from Employee table.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
Set rstEmployees.ActiveConnection = cnn1
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockBatchOptimistic
rstEmployees.Open "employee", , , , adCmdTable
' Show the EditMode property under different editing
' states.
rstEmployees.AddNew
rstEmployees!emp_id = "T-T55555M"
rstEmployees!fname = "temp_fname"
rstEmployees!lname = "temp_lname"
EditModeOutput "After AddNew:", rstEmployees.EditMode
rstEmployees.UpdateBatch
EditModeOutput "After UpdateBatch:", rstEmployees.EditMode
rstEmployees!fname = "test"
EditModeOutput "After Edit:", rstEmployees.EditMode
rstEmployees.Close
' Delete new record because this is a demonstration.
cnn1.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"
End Sub
Public Function EditModeOutput(strTemp As String, _
intEditMode As Integer)
' Print report based on the value of the EditMode
' property.
Debug.Print strTemp
Debug.Print " EditMode = ";
Select Case intEditMode
Case adEditNone
Debug.Print "adEditNone"
Case adEditInProgress
Debug.Print "adEditInProgress"
Case adEditAdd
Debug.Print "adEditAdd"
End Select
End Function
#$>ATDescription, HelpContext, HelpFile, NativeError, Number, Source, and SQLState Properties Example
This example triggers an error, traps it, and displays the Description, HelpContext, HelpFile, NativeError, Number, Source, and SQLState properties of the resulting Error object.
Public Sub DescriptionX()
Dim cnn1 As ADODB.Connection
Dim errLoop As ADODB.Error
Dim strError As String
On Error GoTo ErrorHandler
' Intentionally trigger an error.
Set cnn1 = New ADODB.Connection
cnn1.Open "nothing"
Exit Sub
ErrorHandler:
' Enumerate Errors collection and display
' properties of each Error object.
For Each errLoop In cnn1.Errors
strError = "Error #" & errLoop.Number & vbCr & _
" " & errLoop.Description & vbCr & _
" (Source: " & errLoop.Source & ")" & vbCr & _
" (SQL State: " & errLoop.SQLState & ")" & vbCr & _
" (NativeError: " & errLoop.NativeError & ")" & vbCr
If errLoop.HelpFile = "" Then
strError = strError & _
" No Help file available" & _
vbCr & vbCr
Else
strError = strError & _
" (HelpFile: " & errLoop.HelpFile & ")" & vbCr & _
" (HelpContext: " & errLoop.HelpContext & ")" & _
vbCr & vbCr
End If
Debug.Print strError
Next
Resume Next
End Sub
#$>ATFilter and RecordCount Properties Example
This example uses the Filter property to open a new Recordset based on a specified condition applied to an existing Recordset. It uses the RecordCount property to show the number of records in the two Recordsets. The FilterField function is required for this procedure to run.
Public Sub FilterX()
Dim rstPublishers As ADODB.Recordset
Dim rstPublishersCountry As ADODB.Recordset
Dim strCnn As String
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String
' Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable
' Populate the Recordset.
intPublisherCount = rstPublishers.RecordCount
' Get user input.
strCountry = Trim(InputBox( _
"Enter a country to filter on:"))
If strCountry <> "" Then
' Open a filtered Recordset object.
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry)
If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
' Print number of records for the original
' Recordset object and the filtered Recordset
' object.
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishersCountry.RecordCount
MsgBox strMessage
End If
rstPublishersCountry.Close
End If
End Sub
Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
' Set a filter on the specified Recordset object and then
' open a new Recordset object.
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function
Note When you know the data you want to select, it's usually more efficient to open a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain records from a particular country.
Public Sub FilterX2()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
' Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "SELECT * FROM publishers " & _
"WHERE Country = 'USA'", strCnn, , , adCmdText
' Print current data in recordset.
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & _
rstPublishers!country
rstPublishers.MoveNext
Loop
rstPublishers.Close
End Sub
#$>ATIsolationLevel and Mode Properties Example
This example uses the Mode property to open an exclusive connection, and the IsolationLevel property to open a transaction that is conducted in isolation of other transactions.
Public Sub IsolationLevelX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
' Assign connection string to variable.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
' Open connection and titles table.
Set cnn1 = New ADODB.Connection
cnn1.Mode = adModeShareExclusive
cnn1.IsolationLevel = adXactIsolated
cnn1.Open strCnn
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenDynamic
rstTitles.LockType = adLockPessimistic
rstTitles.Open "titles", cnn1, , , adCmdTable
cnn1.BeginTrans
' Display connection mode.
If cnn1.Mode = adModeShareExclusive Then
MsgBox "Connection mode is exclusive."
Else
MsgBox "Connection mode is not exclusive."
End If
' Display isolation level.
If cnn1.IsolationLevel = adXactIsolated Then
MsgBox "Transaction is isolated."
Else
MsgBox "Transaction is not isolated."
End If
' Change the type of psychology titles.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
rstTitles!Type = "self_help"
rstTitles.Update
End If
rstTitles.MoveNext
Loop
' Print current data in recordset.
rstTitles.Requery
Do While Not rstTitles.EOF
Debug.Print rstTitles!Title & " - " & rstTitles!Type
rstTitles.MoveNext
Loop
' Restore original data.
cnn1.RollbackTrans
rstTitles.Close
cnn1.Close
End Sub
#$>ATMarshalOptions Property Example
This example uses the MarshalOptions property to specify what rows are sent back to the server — All Rows or only Modified Rows.
Public Sub MarshalOptionsX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
Dim strMarshalAll As String
Dim strMarshalModified As String
' Open recordset with names from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn, , , adCmdText
' Store original data.
strOldFirst = rstEmployees!fname
strOldLast = rstEmployees!lname
' Change data in edit buffer.
rstEmployees!fname = "Linda"
rstEmployees!lname = "Kobara"
' Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"
strMarshalAll = "Would you like to send all the rows " & _
"in the recordset back to the server?"
strMarshalModified = "Would you like to send only " & _
"modified rows back to the server?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
If MsgBox(strMarshalAll, vbYesNo) = vbYes Then
rstEmployees.MarshalOptions = adMarshalAll
rstEmployees.Update
ElseIf MsgBox(strMarshalModified, vbYesNo) = vbYes Then
rstEmployees.MarshalOptions = adMarshalModifiedOnly
rstEmployees.Update
End If
End If
' Show the resulting data.
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
rstEmployees!lname
' Restore original data because this is a demonstration.
If Not (strOldFirst = rstEmployees!fname And _
strOldLast = rstEmployees!lname) Then
rstEmployees!fname = strOldFirst
rstEmployees!lname = strOldLast
rstEmployees.Update
End If
rstEmployees.Close
End Sub
#$>ATMaxRecords Property Example
This example uses the MaxRecords property to open a Recordset containing the ten most expensive titles in the Titles table.
Public Sub MaxRecordsX()
Dim rstTemp As ADODB.Recordset
Dim strCnn As String
' Open recordset containing the 10 most expensive
' titles in the Titles table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstTemp = New ADODB.Recordset
rstTemp.MaxRecords = 10
rstTemp.Open "SELECT Title, Price FROM Titles " & _
"ORDER BY Price DESC", strCnn, , , adCmdText
' Display the contents of the recordset.
Debug.Print "Top Ten Titles by Price:"
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & " - " & rstTemp!Price
rstTemp.MoveNext
Loop
rstTemp.Close
End Sub
#$>ATNumericScale and Precision Properties Example
This example uses the NumericScale and Precision properties to display the numeric scale and precision of fields in the Discounts table of the Pubs database.
Public Sub NumericScaleX()
Dim rstDiscounts As ADODB.Recordset
Dim fldTemp As ADODB.Field
Dim strCnn As String
' Open recordset.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstDiscounts = New ADODB.Recordset
rstDiscounts.Open "discounts", strCnn, , , adCmdTable
' Display numeric scale and precision of
' numeric and small integer fields.
For Each fldTemp In rstDiscounts.Fields
If fldTemp.Type = adNumeric _
Or fldTemp.Type = adSmallInt Then
MsgBox "Field: " & fldTemp.Name & vbCr & _
"Numeric scale: " & _
fldTemp.NumericScale & vbCr & _
"Precision: " & fldTemp.Precision
End If
Next fldTemp
rstDiscounts.Close
End Sub
#$>ATOriginalValue and UnderlyingValue Properties Example
This example demonstrates the OriginalValue and UnderlyingValue properties by displaying a message if a record's underlying data has changed during a Recordset batch update.
Public Sub OriginalValueX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim fldType As ADODB.Field
Dim strCnn As String
' Open connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
' Open recordset for batch update.
Set rstTitles = New ADODB.Recordset
Set rstTitles.ActiveConnection = cnn1
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles"
' Set field object variable for Type field.
Set fldType = rstTitles!Type
' Change the type of psychology titles.
Do Until rstTitles.EOF
If Trim(fldType) = "psychology" Then
fldType = "self_help"
End If
rstTitles.MoveNext
Loop
' Similate a change by another user by updating
' data using a command string.
cnn1.Execute "UPDATE titles SET type = 'sociology' " & _
"WHERE type = 'psychology'"
'Check for changes.
rstTitles.MoveFirst
Do Until rstTitles.EOF
If fldType.OriginalValue <> _
fldType.UnderlyingValue Then
MsgBox "Data has changed!" & vbCr & vbCr & _
" Title ID: " & rstTitles!title_id & vbCr & _
" Current value: " & fldType & vbCr & _
" Original value: " & _
fldType.OriginalValue & vbCr & _
" Underlying value: " & _
fldType.UnderlyingValue & vbCr
End If
rstTitles.MoveNext
Loop
' Cancel the update because this is a demonstration.
rstTitles.CancelBatch
rstTitles.Close
' Restore original values.
cnn1.Execute "UPDATE titles SET type = 'psychology' " & _
"WHERE type = 'sociology'"
cnn1.Close
End Sub
#$>ATPrepared Property Example
This example demonstrates the Prepared property by opening two Command objects — one prepared and one not prepared.
Public Sub PreparedX()
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim cmd2 As ADODB.Command
Dim strCnn As String
Dim strCmd As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNotPrepared As Single
Dim sngPrepared As Single
Dim intLoop As Integer
' Open a connection.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
' Create two command objects for the same
' command -- one prepared and one not prepared.
strCmd = "SELECT title, type FROM titles ORDER BY type"
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = cnn1
cmd1.CommandText = strCmd
Set cmd2 = New ADODB.Command
Set cmd2.ActiveConnection = cnn1
cmd2.CommandText = strCmd
cmd2.Prepared = True
' Set a timer, then execute the unprepared
' command 20 times.
sngStart = Timer
For intLoop = 1 To 20
cmd1.Execute
Next intLoop
sngEnd = Timer
sngNotPrepared = sngEnd - sngStart
' Reset the timer, then execute the prepared
' command 20 times.
sngStart = Timer
For intLoop = 1 To 20
cmd2.Execute
Next intLoop
sngEnd = Timer
sngPrepared = sngEnd - sngStart
' Display performance results.
MsgBox "Performance Results:" & vbCr & _
" Not Prepared: " & Format(sngNotPrepared, _
"##0.000") & " seconds" & vbCr & _
" Prepared: " & Format(sngPrepared, _
"##0.000") & " seconds"
cnn1.Close
End Sub
#$>ATProvider and DefaultDatabase Properties Example
This example demonstrates the Provider property by opening two Connection objects using different providers. It also uses the DefaultDatabase property to set the default database for the Microsoft ODBC Provider.
Public Sub ProviderX()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
' Open a connection using the Microsoft ODBC provider.
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd"
cnn1.Open strCnn
cnn1.DefaultDatabase = "pubs"
' Display the provider.
MsgBox "Cnn1 provider: " & cnn1.Provider
' Open a connection using the Microsoft Jet provider.
Set cnn2 = New ADODB.Connection
cnn2.Provider = "Microsoft.Jet.OLEDB.3.51"
cnn2.Open "C:\Samples\northwind.mdb", "admin", ""
' Display the provider.
MsgBox "Cnn2 provider: " & cnn2.Provider
cnn1.Close
cnn2.Close
End Sub
This example demonstrates the Source property by opening three Recordset objects based on different data sources.
Public Sub SourceX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim rstPublishers As ADODB.Recordset
Dim rstTitlesPublishers As ADODB.Recordset
Dim cmdSQL As ADODB.Command
Dim strCnn As String
Dim strSQL As String
' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
' Open a recordset based on a command object.
Set cmdSQL = New ADODB.Command
Set cmdSQL.ActiveConnection = cnn1
cmdSQL.CommandText = "Select title, type, pubdate " & _
"FROM titles ORDER BY title"
Set rstTitles = cmdSQL.Execute()
' Open a recordset based on a table.
Set rstPublishers = New ADODB.Recordset
rstPublishers.Open "publishers", strCnn, , , adCmdTable
' Open a recordset based on an SQL string.
Set rstTitlesPublishers = New ADODB.Recordset
strSQL = "SELECT title_ID AS TitleID, title AS Title, " & _
"publishers.pub_id AS PubID, pub_name AS PubName " & _
"FROM publishers INNER JOIN titles " & _
"ON publishers.pub_id = titles.pub_id " & _
"ORDER BY Title"
rstTitlesPublishers.Open strSQL, strCnn, , , adCmdText
' Use the Source property to display the source of each recordset.
MsgBox "rstTitles source: " & vbCr & _
rstTitles.Source & vbCr & vbCr & _
"rstPublishers source: " & vbCr & _
rstPublishers.Source & vbCr & vbCr & _
"rstTitlesPublishers source: " & vbCr & _
rstTitlesPublishers.Source
rstTitles.Close
rstPublishers.Close
rstTitlesPublishers.Close
cnn1.Close
End Sub
This example uses the Status property to display which records have been modified in a batch operation before a batch update has occurred.
Public Sub StatusX()
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
' Open recordset for batch update.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles", strCnn, , , adCmdTable
' Change the type of psychology titles.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
rstTitles!Type = "self_help"
End If
rstTitles.MoveNext
Loop
' Display Title ID and status.
rstTitles.MoveFirst
Do Until rstTitles.EOF
If rstTitles.Status = adRecModified Then
Debug.Print rstTitles!title_id & " - Modified"
Else
Debug.Print rstTitles!title_id
End If
rstTitles.MoveNext
Loop
' Cancel the update because this is a demonstration.
rstTitles.CancelBatch
rstTitles.Close
End Sub
This example demonstrates the Type property by displaying the name of the constant corresponding to the value of the Type property of all the Field objects in the Employee table. The FieldType function is required for this procedure to run.
Public Sub TypeX()
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim strCnn As String
' Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
Debug.Print "Fields in Employee Table:" & vbCr
' Enumerate Fields collection of Employees table.
For Each fldLoop In rstEmployees.Fields
Debug.Print " Name: " & fldLoop.Name & vbCr & _
" Type: " & FieldType(fldLoop.Type) & vbCr
Next fldLoop
End Sub
Public Function FieldType(intType As Integer) As String
Select Case intType
Case adChar
FieldType = "adChar"
Case adVarChar
FieldType = "adVarChar"
Case adSmallInt
FieldType = "adSmallInt"
Case adUnsignedTinyInt
FieldType = "adUnsignedTinyInt"
Case adDBTimeStamp
FieldType = "adDBTimeStamp"
End Select
End Function
This example demonstrates the Value property with Field and Property objects by displaying field and property values for the Employees table.
Public Sub ValueX()
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim prpLoop As ADODB.Property
Dim strCnn As String
' Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
Debug.Print "Field values in rstEmployees"
' Enumerate the Fields collection of the Employees
' table.
For Each fldLoop In rstEmployees.Fields
' Because Value is the default property of a
' Field object, the use of the actual keyword
' here is optional.
Debug.Print " " & fldLoop.Name & " = " & fldLoop.Value
Next fldLoop
Debug.Print "Property values in rstEmployees"
' Enumerate the Properties collection of the
' Recordset object.
For Each prpLoop In rstEmployees.Properties
' Because Value is the default property of a
' Property object, the use of the actual keyword
' here is optional.
Debug.Print " " & prpLoop.Name & " = " & prpLoop.Value
Next prpLoop
rstEmployees.Close
End Sub
This example uses the Version property of a Connection object to display the current ADO version. It also uses several dynamic properties to show the current DBMS name and version, OLE DB version, provider name and version, driver name and version, and driver ODBC version.
Public Sub VersionX()
Dim cnn1 As ADODB.Connection
' Open connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
strVersionInfo = "ADO Version: " & cnn1.Version & vbCr & _
"DBMS Name: " & cnn1.Properties("DBMS Name") & vbCr & _
"DBMS Version: " & cnn1.Properties("DBMS Version") & vbCr & _
"OLE DB Version: " & cnn1.Properties("OLE DB Version") & vbCr & _
"Provider Name: " & cnn1.Properties("Provider Name") & vbCr & _
"Provider Version: " & cnn1.Properties("Provider Version") & vbCr & _
"Driver Name: " & cnn1.Properties("Driver Name") & vbCr & _
"Driver Version: " & cnn1.Properties("Driver Version") & vbCr & _
"Driver ODBC Version: " & cnn1.Properties("Driver ODBC Version")
MsgBox strVersionInfo
cnn1.Close
End Sub
# adproAbsolutePageX
$ AbsolutePage, PageCount, and PageSize Properties Example (ADO)
> example
A adproAbsolutePageX;adproPageCountX;adproPageSizeX;
# adproAbsolutePositionX
$ AbsolutePosition and CursorLocation Properties Example (ADO)
> example
A adproAbsolutePositionX;adproCursorLocationX;
T 53127
# adproActiveConnectionX
$ ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example (ADO)
> Example
A adproActiveConnectionX;adproCommandTextX;adproCommandTimeoutX;adproCommandTypeX;adproSizeX;adproDirectionX;
# adproActualSizeX
$ ActualSize and DefinedSize Properties Example (ADO)
> Example
A adproActualSizeX;adproDefinedSizeX;
# adproAttributesX
$ Attributes and Name Properties Example (ADO)
> Example
A adproAttributesX;adproNameX;
# adproBOFX
$ BOF, EOF, and Bookmark Properties Example (ADO)
> example
A adproBOFX;adproEOFX;adproBookmarkX;
T 53010
# adproCacheSizeX
$ CacheSize Property Example (ADO)
> Example
A adproCacheSizeX
# adproConnectionStringX
$ ConnectionString, ConnectionTimeout, and State Properties Example (ADO)
> Example
A adproConnectionStringX;adproConnectionTimeoutX;adproStateX;
# adproCountX
$ Count Property Example (ADO)
> example
A adproCountX;
T 53326
# adproCursorTypeX
$ CursorType, LockType, and EditMode Properties Example (ADO)
> example
A adproCursorTypeX;adproLockTypeX;adproEditModeX;
T 53128
# adproDescriptionX
$ Description, HelpContext, HelpFile, NativeError, Number, Source, and SQLState Properties Example (ADO)
> Example
A adproDescriptionX;adproHelpContextX;adproHelpFileX;adproNativeErrorX;adproNumberX;adproSourceX;adproSQLStateX;
# adproFilterX
$ Filter and RecordCount Properties Example (ADO)
> example
A adproFilterX;adproRecordCountX;
T 52999
# adproIsolationLevelX
$ IsolationLevel and Mode Properties Example (ADO)
> Example
A adproIsolationLevelX;adproModeX;
# adproMarshalOptionsX
$ MarshalOptions Property Example (ADO)
> Example
A adproMarshalOptionsX;
# adproMaxRecordsX
$ MaxRecords Property Example (ADO)
> Example
A adproMaxRecordsX;
# adproNumericScaleX
$ NumericScale and Precision Properties Example (ADO)
> Example
A adproNumericScaleX;adproPrecisionX;
# adproOriginalValueX
$ OriginalValue and UnderlyingValue Properties Example (ADO)
> Example
A adproOriginalValueX;adproUnderlyingValueX;
# adproPreparedX
$ Prepared Property Example (ADO)
> Example
A adproPreparedX;
# adproProviderX
$ Provider and DefaultDatabase Properties Example (ADO)
> Example
A adproProviderX;adproDefaultDatabaseX;
# adproSourceX
$ Source Property Example (ADO)
> example
A adproSourceX;
T 53008
# adproStatusX
$ Status Property Example (ADO)
> Example
A adproStatusX;
# adproTypeX
T 55770
$ Type Property Example (ADO)
> Example
A adproTypeX;
# adproValueX
T 55547
$ Value Property Example (ADO)
> Example
A adproValueX;
# adproVersionX
$ Version Property Example (ADO)
> Example
A adproVersionX;