Whether to use DAO or ADO is sometimes a tricky question. By simply changing the value of a compile-time constant,
USEDAO, you can selectively choose to use either Object Library within you code. Here is the procedure to follow:
1. Set References to both the DAO and ADO Object Libraries. In my specific case, this would be:
- Microsoft DAO 3.6 Object Library
-
Microsoft ActiveX Data Objects 2.1 Object Library
2. Declare your compile-time constant within the General Declarations Section of your Form's Code Module:
- #Const USEDAO = True 'will use DAO, for now
3. Sample demonstration code:
- Dim strSQL As String
-
-
strSQL = "SELECT * FROM Employees ORDER BY [LastName]"
-
-
#If USEDAO Then '#Const USEDAO = True
-
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
-
Set MyDB = CurrentDb()
-
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
-
-
Do While Not MyRS.EOF
-
'Example of Recordset processing
-
Debug.Print MyRS![FirstName] & " " & MyRS![LastName]
-
MyRS.MoveNext
-
Loop
-
#Else '#Const USEDAO = False, USE ADO
-
Dim MyRS As ADODB.Recordset
-
Set MyRS = New ADODB.Recordset
-
-
Set MyRS.ActiveConnection = CurrentProject.Connection
-
With MyRS
-
.CursorType = adOpenStatic
-
.LockType = adLockReadOnly
-
.Open strSQL
-
End With
-
-
'You can also use the single line syntax
-
'MyRS.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly, adCmdText
-
-
Do While Not MyRS.EOF
-
'Example of Recordset processing
-
Debug.Print MyRS![FirstName] & " " & MyRS![LastName]
-
MyRS.MoveNext
-
Loop
-
#End If
4. If you wanted to use ADO for this exercise, change the compile-time constant to False:
- #Const USEDAO = False 'will use ADO now
