Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 5th, 2003, 07:31 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default DAO vs. ADO

I've heard many people talk about how DAO is out and ADO is in, yet in all the posts I've read, I see people use DAO all the time (that I can tell)... So much so, I've still haven't got a handle of what "real" ADO code looks like. Below is a snippet of code I have in a form. Can someone translate this into ADO? Thanks.

Code:
'(Assuming the correct library is referenced.)

Dim strRecSrc As String, strCriteria As String
Dim dbsPTIEvaluator As Database
Dim rstBrowsePTIs As Recordset

strRecSrc = "tblBrowseByCode"
Set dbsPTIEvaluator = DBEngine.Workspaces(0).Databases(0)
Set rstBrowsePTIs = dbsPTIEvaluator.OpenRecordset(strRecSrc, DB_OPEN_DYNASET)
strCriteria = "[strBrowseName] = '" & Me.txtSavedSearch & "'"

rstBrowsePTIs.FindFirst strCriteria
If rstBrowsePTIs.NoMatch Then
   rstBrowsePTIs.AddNew
Else
   rstBrowsePTIs.Edit
End If
 
rstBrowsePTIs("fDefault") = Me.chkDefault
rstBrowsePTIs("strBrowseName") = Me.txtSavedSearch
rstBrowsePTIs("bytBrowseNo") = Me.txtBrowseNo
rstBrowsePTIs.Update
rstBrowsePTIs.Close

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
Reply With Quote
  #2 (permalink)  
Old September 5th, 2003, 08:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Greg

As you can see, it's not that different:

Code:
Dim strRecSrc As String, strCriteria As String
Dim rstBrowsePTIs As ADODB.Recordset

strRecSrc = "tblBrowseByCode"
cmdSP.ActiveConnection = CurrentProject.Connection
cmdSP.CommandType = adCmdTable
cmdSP.CommandText = strRecSrc

Set rstBrowsePTIs = cmdSP.Execute

strCriteria = "[strBrowseName] = '" & Me.txtSavedSearch & "'"

If Not rstBrowsePTIs.BOF And Not rstBrowsePTIs.EOF Then
    rstBrowsePTIs.MoveFirst
    rstBrowsePTIs.Find strCriteria,,adSearchForward
    If not rstBrowsePTIs.EOF Then
        rstBrowsePTIs.AddNew
    End If
 
    rstBrowsePTIs("fDefault") = Me.chkDefault
    rstBrowsePTIs("strBrowseName") = Me.txtSavedSearch
    rstBrowsePTIs("bytBrowseNo") = Me.txtBrowseNo
    rstBrowsePTIs.Update
    rstBrowsePTIs.Close
End IF

Brian Skelton
Braxis Computer Services Ltd.
Reply With Quote
  #3 (permalink)  
Old September 5th, 2003, 01:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old September 5th, 2003, 01:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Oops, gave you the wrong URL for the VB6 project. It's here:

http://msdn.microsoft.com/library/de...oadoupdate.asp

Reply With Quote
  #5 (permalink)  
Old September 5th, 2003, 01:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Greg,

Just to make sure I was clear enough about this:

Brian is using a command object here (which doesn't exist in DAO) to populate the recorset:

cmdSP.ActiveConnection = CurrentProject.Connection
cmdSP.CommandType = adCmdTable
cmdSP.CommandText = strRecSrc

Set rstBrowsePTIs = cmdSP.Execute

You are using the the OpenRecorset method of your Databse object:

Set rstBrowsePTIs = dbsPTIEvaluator.OpenRecordset(strRecSrc, DB_OPEN_DYNASET)

And I'm using the recordset object's Open method:

rstBrowsePTIs.Open strRecSrc, cnn, adOpenDynamic, adLockOptimistic

Hence the variations. Anyway, enough o' that.

Bob






Reply With Quote
  #6 (permalink)  
Old February 10th, 2005, 12:17 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey..Bob.

Well, I am converting a project developed with DAO to ADO,
I just read your comment about that VB project, i also check
URL but didnt find out that project. So please check and give
me new URL..

I am stuck middle of something and it will really help me.

Thanx in Advance./..

Reply With Quote
  #7 (permalink)  
Old February 10th, 2005, 11:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hello,

Unfortunately, the module I mentioned appears to have been archived with the following comment: "Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist."

I could e-mail you the module 83K but not through the Wrox site (no attachments allowed).

Bob



Reply With Quote
  #8 (permalink)  
Old January 11th, 2006, 06:22 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi!

Could you email me the code, I would really appreciate it!

Best regards,
ocliff

Reply With Quote
  #9 (permalink)  
Old January 11th, 2006, 06:23 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

... my email is at the bottom of this page: www.avectris.se

thx!

Reply With Quote
  #10 (permalink)  
Old January 11th, 2006, 04:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Manfred,

I sent a .zip file of the VB6 project. Let me know if you have any problems opening it. The module you are interested in primarily is MigratingDAOtoADO.bas. This is just a VB standard module that you can open in the Access VBE editor as well.

Best,

Bob


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO or DAO knowledge76 Access VBA 3 October 12th, 2015 04:26 PM
ADO - DAO compatability petermat Access VBA 6 January 10th, 2006 06:06 PM
Dao to Ado vrtviral Access VBA 5 February 19th, 2005 11:13 AM
ADO vs DAO perrymans BOOK: Expert One-on-One Access Application Development 0 October 24th, 2004 11:36 PM
DAO / ADO? merguvan Access VBA 8 January 18th, 2004 07:39 AM



All times are GMT -4. The time now is 11:14 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.