Examples of Converting DAO Code

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Microsoft Access includes version 4.0 of the Microsoft Jet database engine and the DAO 3.6 object library. All previous version of DAO are no longer supported in Microsoft Access 2000. The following examples demonstrate how to convert code constructs in Microsoft Access databases created with earlier versions of the Jet database engine and DAO object libraries

Note   This information is provided for reference only. Versions of DAO code prior to 3.6 are no longer supported in Access 2000. It is suggested that you use the ADO methods of accessing data in your current database for all new applications and future version compatibility.

Setting a Reference to the current database

Version Example
1.x 
Dim dbs As Database
Set dbs = CurrentDb
2.x 
Dim dbs As Database
Set dbs = DBEngine.Workspaces(0).Databases(0)
or 
Dim dbs As Database
Set dbs = DBEngine(0)(0)
7.0 or Later 
Dim dbs As DAO.Database
Set dbs = CurrentDb
9.0

(Recommended)

 
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Note   In Microsoft Access, the current database is the default database in the collection, so you can still use the DBEngine(0)(0) syntax to return a object variable pointing to the current database. However, when using DAO the CurrentDb method is the preferred means of returning this object variable, because it enables you to create more than one Database object variable that points to the current database.

Opening a Database and Setting a Reference

Version Example
1.x 
Set dbs = OpenDatabase("Contacts.mdb")
2.x and later 
Set dbs = DBEngine.Workspaces(0).OpenDatabase("Contacts.mdb")
or 
Set dbs = Workspaces(0).OpenDatabase("Contacts.mdb")
or 
Set dbs = OpenDatabase("Contacts.mdb")
9.x (Recommended) 
cnn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Contact.mdb

Opening a Table-Type Recordset

Version Example
1.x 
Dim tbl As Table
Set tbl = dbs.OpenTable("Orders")
2.x 
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Orders", DB_OPEN_TABLE)
or 
Dim rst As Recordset
Set rst = dbs!Orders.OpenRecordset
7.0 or later 
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset("Orders", dbOpenTable)
or 
Dim rst As DAO.Recordset
Set rst = dbs!Orders.OpenRecordset
9.0 (Recommended) 
Dim rst As New ADODB.Recordset
rst.Open Orders,cnn,adOpenKeyset,adLockOptimistic,adCmdTableDirect

Opening a Table-Type Recordset Exclusively

Version Example
1.x 
Dim tbl As Table
Set tbl = dbs.OpenTable("Orders", DB_DENY_READ)
2.x 
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Orders", DB_OPEN_TABLE, DB_DENY_READ)
or 
Dim rst As Recordset
Set rst = dbs!Orders.OpenRecordset(DB_OPEN_TABLE, DB_DENY_READ)
7.0 or later 
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset("Orders", dbOpenTable, dbDenyRead)
or 
Dim rst As DAO.Recordset
Set rst = dbs!Orders.OpenRecordset(dbOpenTable, dbDenyRead)
9.0 (Recommended) 
S/A (Not available via ADO)

Opening a Dynaset-Type Recordset

Version Example
1.x 
Dim dyn As Dynaset
Set dyn = dbs.CreateDynaset("Orders")
2.x 
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Orders", DB_OPEN_DYNASET)
or 
Set rst = dbs!Orders.OpenRecordset(dbOpenDynaset)
7.0 or later 
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
or 
Set rst = dbs!Orders.OpenRecordset(dbOpenDynaset)
9.0 (Recommended) 
Dim rst As New ADODB.Recordset
rst.Open Orders, cnn, adOpenKeyset, adLockOptimistic

Opening a Snapshot-Type Recordset

Version Example
1.x 
Dim snp As Snapshot
Set snp = dbs.CreateSnapshot("Orders")
2.x 
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Orders", DB_OPEN_SNAPSHOT)
or 
Set rst = dbs!Orders.OpenRecordset(DB_OPEN_SNAPSHOT)
7.0 or later 
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Orders", dbOpenSnapshot)
or 
Set rst = dbs!Orders.OpenRecordset(dbOpenSnapshot)
9.0 (Recommended) 
Dim rst As New ADODB.Recordset
rst.Open Orders, cnn, adOpenStatic, adLockReadOnly

Listing Fields

Version Example
1.x 
' Given open Table object tbl.
Dim snp As Snapshot
Set snp = tbl.ListFields()
While Not snp.EOF
   Debug.Print snp!Name, snp!Type
   snp.MoveNext
Wend
snp.Close
2.x 
' Given open TableDef object tdf.
Dim intI As Integer, fld As Field
For intI = 0 To tdf.Fields.Count - 1
   Set fld = tdf.Fields(intI)
   Debug.Print fld.Name, fld.Type
Next intI
7.0 or later 
' Given open TableDef object tdf.
Dim fld As Field
For Each fld in tdf.Fields
   Debug.Print fld.Name, fld.Type
Next fld
9.0 (Recommended) 
 Given open Table object tbl
Dim col as ADOX.column
For Each col in tbl.Columns
   Debug.Print col.Name, col.Type
Next col

Listing Tables in a Database

Version Example
1.x 
' Given open Database object dbs.
Dim snp As Snapshot
Set snp = dbs.ListTables()
While Not snp.EOF
   Debug.Print snp!Name
   snp.MoveNext
Wend snp.Close
2.x 
' Given open Database object dbs.
Dim intJ As Integer, tdf As TableDef
For intJ = 0 To dbs.TableDefs.Count - 1
   Set tdf = dbs.TableDefs(intJ)
   Debug.Print tdf.Name
Next intJ
7.0 or later 
' Given open Database object dbs.
Dim tdf As TableDef
For Each tdf in dbs.TableDefs
   Debug.Print tdf.Name
Next tdf
9.0 (Recommended) 
 Given open Catalog object cat
Dim tbl As ADOX.Table
For Each tbl in cat.Tables
   Debug.Print tbl.Name
Next tbl

Listing a Table's Indexes

Version Example
1.x 
' Given open Table object tbl.
Dim snp As Snapshot
Set snp = tbl.ListIndexes()
While Not snp.EOF
   Debug.Print snp!Name
   snp.MoveNext
Wend
snp.Close
2.x 
' Given open TableDef object tdf.
Dim intI As Integer, idx As Index
For intI = 0 To tdf.Indexes.Count - 1
   Set idx = tdf.Indexes(intI)
   Debug.Print idx.Name
Next intI
7.0 or later 
' Given open TableDef object tdf.
Dim idx As DAO.Index
For Each idx in tdf.Indexes
   Debug.Print idx.Name
Next idx
9.0

(Recommended)

 
' Given open Table object tbl.
Dim idx As ADOX.Index
For Each idx in tbl.Indexes
   Debug.Print idx.Name
Next idx

Listing a Query's Parameters

Version Example
1.x 
' Given open QueryDef object qdf.
Dim snp As Snapshot
Set snp = qdf.ListParameters()
While Not snp.EOF
   Debug.Print snp!Name
   snp.MoveNext
Wend
snp.Close
2.x 
' Given open QueryDef object qdf.
Dim intI As Integer, prm As Parameter
For intI = 0 to qdf.Parameters.Count - 1
   Set prm = qdf.Parameters(i)
   Debug.Print prm.Name
Next intI
7.0 or later 
' Given open QueryDef object qdf.
Dim prm As Parameter
For Each prm in qdf.Parameters
   Debug.Print prm.Name
Next prm
9.0 (Recommended) 
' Given open Procedure object prc.
Dim prm As ADODB.Parameter
For Each prm in prc.Parameters
   Debug.Print prm.Name
Next prm

Setting Query Parameters Dynamically

Version Example
1.x 
' Given open Database dbs.
Dim qdf As QueryDef, dyn As Dynaset
Set qdf = dbs.OpenQueryDef("OrdersQuery")
qdf.ParameterName = "[Start date]"
Set dyn = qdf.CreateDynaset()
2.x 
' Given open QueryDef object qdf.
Dim intI As Integer, prm As Parameter, rst As Recordset
For intI = 0 To qdf.Parameters.Count - 1
   Set prm = qdf.Parameters(intI)
   prm.Value = InputBox("Value for " & prm.Name)
Next intI
Set rst = qdf.OpenRecordset
7.0 or later 
' Given open QueryDef object qdf.
Dim prm As Parameter, rst As Recordset
For Each prm in qdf.Parameters
   prm.Value = InputBox("Value for " & prm.Name)
Next prm
Set rst = qdf.OpenRecordset
9.0 (Recommended) 
' Given open Procedure object prc.
Dim prm As ADODB.Parameter, rst As ADODB.Recordset
For Each prm in prc.Command.Parameters
   prm.Value = InputBox("Value for " & prm.Name)
Next prm
rst.Open prc.Command,,adOpenKeyset, adLockOptimistic

Running a Pass-Through Query

Version Example
1.x 
' Given open Database object dbs.
Dim lngRows As Long
' Run stored procedure on server.
lngRows = dbs.ExecuteSQL("EXECUTE SP_StoredProc")
or 
Dim snp As Snapshot
Set snp = dbs.CreateSnapshot("EXECUTE SP_StoredProc", DB_SQLPassThrough)
2.x and later 
' Given open Database object dbs.
Dim qdf As QueryDef, rst As Recordset
Set qdf = dbs.CreateQueryDef("ODBCQuery")
qdf.Connect = "ODBC;" & "DSN=MyServer;" _
   & "UID=sa;" & "PWD=hithere;" & "DATABASE=pubs"
qdf.SQL = "EXECUTE SP_StoredProc"
qdf.ReturnsRecords = True
.
.
.
Set rst = qdf.OpenRecordset
9.0 and later 
' Given open Catalog Object with Activeconnection set, object cat.
Dim rst As Recordset, cmdPassthru As New ADODB.Command
Set cmdPassthru.ActiveConnection = Currentproject.connection
cmdPassthru("Jet OLEDB:Passthru Query conection string")= _
   "ODBC;DSN=MyServer;UID=sa;PWD=hithere;DATABASE=pubs"
cmdPassthru.CommandText = "EXECUTE SP_StoredProc"
cat.Procedures.Append "ODBCQuery", cmdPassthru
Set rst = cmd.Execute

Creating a Temporary Query

Version Example
1.x 
' Given open Database object dbs.
Dim qdf As QueryDef, strTemp As String
strTemp = "TMP:" & User() & Time()
qdf = dbs.CreateQueryDef(strTemp)
qdf.SQL = "UPDATE Table1 SET Table1.Field1 = Field1 * 1.1;"
.
.
.
qdf.Execute
qdf.Close
.
.
dbs.DeleteQueryDef(strTemp)
2.x and later 
qdf = dbs.CreateQueryDef("")
qdf.Execute
qdf.Close
9.0 and later 
cmd.CommandText = "UPDATE Table1 SET Table1.Field1 = Field1 * 1.1;" cmd.Execute
set cmd = Nothing