Hi Greg,
First, download the following VB6 project from Microsoft. It will give you all the code youâll ever need to migrate any DAO code to ADO code. (See below for some discussion regarding wheter or not you
want to migrate DAO code to ADO code.) Just paste the project code into an Access module and it'll run. If you don't have Visual Studio installed, let me know and I can port the code over to Access and send you an .mdb.
http://msdn.microsoft.com/library/de...l/daotoado.asp
Second, I wanted to elaborate on Brian's code a bit to point up two fundamental differences between the DAO and ADO object models. Here's Brian's code (which is perfectly correct):
Dim rstBrowsePTIs As ADODB.Recordset
Dim strRecSrc As String, strCriteria As String
strRecSrc = "tblBrowseByCode"
cmdSP.ActiveConnection = CurrentProject.Connection
cmdSP.CommandType = adCmdTable
cmdSP.CommandText = strRecSrc
Set rstBrowsePTIs = cmdSP.Execute
This code can be re-written as:
Dim cnn As ADODB.Connection
Dim rstBrowsePTIs As ADODB.Recordset
Dim strRecSrc As String, strCriteria As String
strRecSrc = "tblBrowseByCode"
Set cnn = CurrentProject.Connection
Set rstBrowsePTIs = New ADODB.Recordset
rstBrowsePTIs.Open strRecSrc, cnn, adOpenDynamic, adLockOptimistic
Debug.Print rstBrowsePTIs.GetString(adClipString, , ";")
[I tossed in the last line to show you how you can dump the contents of an ADO recordset to the debug window to see what you got back. I always do this during development]
' Your code
Dim dbsPTIEvaluator As DAO.Database
Dim rstBrowsePTIs As DAO.Recordset
Dim strRecSrc As String, strCriteria As String
strRecSrc = "tblBrowseByCode"
Set dbsPTIEvaluator = DBEngine.Workspaces(0).Databases(0)
Set rstBrowsePTIs = dbsPTIEvaluator.OpenRecordset(strRecSrc, DB_OPEN_DYNASET)
strCriteria = "[strBrowseName] = '" & Me.txtSavedSearch & "'"
Youâll notice that my syntax mirrors yours a bit more closely by explicitly declaring a connection object (though you donât have to explicitly instantiate a connection object before creating other objects on an ADO connection).
The Connection object is the first big difference between DAO and ADO. In ADO, the Connection object replaces DAOâs Database object as the top-level object in the ADO hierarchy; it defines a session for a user for a data source. This differs from DAO, in which the Workspace object defines the session for a user and the data source is defined by the Database object.
Also notice the line:
Set cnn = CurrentProject.Connection
which mirrors your:
Set dbsPTIEvaluator = DBEngine.Workspaces(0).Databases(0)
Couple of comments: With Access 97 forward, you should use Accessâs CurrentDb function instead of DBEngine.Workspaces(0).Databases(0). The latter returns a pointer to the current database; that is, it refers to the open copy of the current database. The CurrentDb function creates another instance of the current database, which lets you create more than one variable of type Database that refers to the current database (important in a multi-user environment.) So:
Set dbsPTIEvaluator = CurrentDb is the recommended DAO syntax.
In ADO, CurrentProject.Connection replaces CurrentDb, providing access the current database from VBA code.
The other fundamental difference is that ADO introduces a Command object to represent a SQL statement or stored procedure. The Command object is the ADO replacement for DAOâs QueryDef object, and lets you execute DDL and DML commands on a conncetion.
Regarding your question about whether or not to use DAO in a Jet database:
Have a look at the MDAC Roadmap site, and click on the âDeprecated MDAC Componenetsâ link.
http://msdn.microsoft.com/library/de...dacroadmap.asp
Youâll notice that
both DAO and Jet have been relegated to the dust bin of obsolete technologies. No new feature enhancements or service packs for either will ever see the light of day, and it is recommended that neither be used for new project development. So, does this mean that you should never use DAO or Jet again? Absolutely not! Iâm attaching the following article to help clarify DAOâs and ADO's relationship to Jet. Its from Access-
VB-SQL Advisor and I think Mary Chipman may(?) have written it. The operative sentences for me are:
Quote:
|
quote:Unless you've been living in a cave, you've likely been inundated with articles touting the virtues of ADO over DAO and urging you to convert all of your DAO code to ADO. My recommendation is: Don't.
|
and
Quote:
|
quote: DAO was written and conceived with the Jet database engine in mind. It provides superior performance when working with Access/Jet tables.
|
As the article points out, lots of folks (myself included) still use DAO
when working with Jet, and for some pretty legitimate reasons. Once you move beyond the Jet-centric universe, however, ADO (or ADO.Net) if unequivocally preferred over DAO.
The article:
Quote:
quote:Unless you've been living in a cave, you've likely been inundated with articles touting the virtues of ADO over DAO and urging you to convert all of your DAO code to ADO. My recommendation is: Don't. While it's true that ADO offers significant enhancements for working with data (especially server data), it doesn't necessarily apply if you're working solely with Access and Jet databases. Even if you're writing new applications using Jet 4.0/Access 2000, you still need DAO. Here's why.
DAO was written and conceived with the Jet database engine in mind. It provides superior performance when working with Access/Jet tables. If you need to work with certain Jet-specific features, ADO isn't even an option. Microsoft has stated at conferences and in marketing materials that they will achieve full-functional parity between DAO and ADO, but that hasn't happened yet.
For example, if you need to perform transactions that span multiple databases, you can use DAO to open a transaction using a Jet Workspace object, open the databases, and either commit or rollback the transaction. In ADO, there's no Workspace object, only a Connection object, which connects to one database at a time. So it would require some creative ADO coding on your part to simulate behavior that you get for free in DAO.
Locking is another weakness of ADO. Although an ADO Recordset has a LockType property, you can't use it to apply pessimistic locking on an Access table by using the ADO intrinsic constant adLockPessimistic if you're also using the Project.CurrentConnection syntax. The lock type is only applied to the current connection, rendering it useless for blocking other connections working with the same table. In DAO you can set the LockEdit property to dbPessimistic -- or do nothing, since dbPessimistic is the default -- and lock the data for all connections until your edit is completed. If you want to prevent users from reading or writing to a table, you can use the DAO Recordset object's constants, dbDenyWrite and dbDenyRead. There are no equivalents in ADO.
Jet Replication Objects (JRO), the Jet-specific extensions to ADO available in Jet 4.0, have also left a few things out. For example, you can't create a replica that prevents deletions. You can do this from the Access menus in Access 2000 or from DAO 4.0, thanks to an undocumented technique revealed by Michael Kaplan. Simply pass the value 4 to the DAO MakeReplica method to create a no-deletes replica. Other capabilities, such as being able to set the ReplicateProject property on a database, are also lacking in JRO. In fact, the entire Properties collection and the CreateProperty method are unavailable in ADO, JRO, or ADOX. DAO to the rescue again.
ADOX, the ADO extension for Microsoft Jet 4.0, is probably the most deficient of all when it comes to functional parity with DAO, most notably when dealing with security. For example, the ADOX method for creating a new user doesn't let you set a PID (personal identifier), only the user name and password for the new user. It generates a random PID on its own, which you can't document or retrieve after the user has been created. If you care about documenting security (and you should), you need to use either DAO or Jet SQL grammar to create new users.
ADOX lacks a property to check both a user's explicit and implicit (or inherited) permissions on an object. In DAO, the AllPermissions property does this -- there's no ADOX equivalent. With ADOX, you'd need to write code to iterate through all the groups a user belongs to, then check each group's permissions against the one you're looking for. It's a safe bet that this code will run more slowly than AllPermissions.
The most frustrating thing about ADOX is its inability to set or retrieve permissions on Access forms, reports, macros, and modules. If you've read the "Converting DAO to ADO" white paper on Microsoft's Web site, you may have noticed the ADOX procedure purporting to set permissions on Access objects. The code looks good, but it doesn't work. There's no fix for this -- the workaround is to use DAO. You'll also find it most convenient to use DAO for setting relationships, creating indexes, and setting/retrieving database properties.
I'm not suggesting you should never use ADO -- if you're working with external data sources, such as SQL Server, ADO is definitely preferred over DAO or ODBCDirect. Dan Haught has some great transitioning tips on page 52. There are also new twists in ADO recordsets that are fun to work with, and ADO command objects are the only way to use the new Jet SQL grammar. You can always take advantage of both ADO and DAO in the same project by setting references to both type libraries. But for Access-only applications, you're still going to need DAO to get the most out of the Jet engine.
|
Anyway, sorry to run on so long, but your question is a perennial one, and I wanted to pull my thoughts together on it. So there you have it.
HTH,
Bob
DAO vs. ADO
