DAO to ADO

[Luciano]

I tried the new ADODB.Recordset of VBA in Access2007 to open tables, but
Access2007 don't understand this syntax. Maybe I have to Add a Reference?

 

[Tony Toews [MVP]]

I tried the new ADODB.Recordset of VBA in Access2007 to open tables, but
Access2007 don't understand this syntax. Maybe I have to Add a Reference?

Correct. You need to add the ADO reference.

But if you're already using DAO why think about using ADO?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Luciano]

because I have some problems to split my tables, I think DAO is the reason?

 

[Douglas J. Steele]

Very doubtful.

DAO is the native approach for communicating with Access files, ADO isn't.

What's the problem you're having splitting your database?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Luciano]

I wrote in the past some sub-routine (inDAO) in a Global Module something like:

Option Compare Database
Option Explicit
Public ws As Workspace
Public db As Database
Public tbInitialisatie As Recordset

Public Sub OpenInitialisatie()
On Error GoTo ErrorOpenInitialisatie
Set ws = DBEngine.Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)
Set tbInitialisatie = db.OpenRecordset("tblInitialisatie", DB_OPEN_TABLE)
MsgBox “DAO OKâ€
ExitError:
Exit Sub
ErrorOpenInitialisatie:
MsgBox "ErrorOpenInitialisatie DAO"
Resume ExitError
End Sub

Whan I split my tables, and I call this routine in a form, I get the message
"ErrorOpenInitialisatie DAO"

Yesterday I wrote the code (in ADO):

Option Compare Database
Option Explicit
Public tbInitialisatie As New ADODB.Recordset

Public Sub OpenInitialisatie()
tbInitialisatie.Open "tblInitialisatie", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
MsgBox “ADO OKâ€
ExitError:
Exit Sub
ErrorOpenInitialisatie:
MsgBox "ErrorOpenInitialisatie ADO"
Resume ExitError
End Sub

and I don't get the Erro-message anymore, but I get the 'good' message “ADO
OKâ€

 

[Douglas J. Steele]

If you have references set for both ADO and DAO, you'll find that you'll
need to "disambiguate" certain declarations, because objects with the same
names exist in the 2 models.

Try using

Public tbInitialisatie As DAO.Recordset

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Luciano]

In my first sub-routine, I did not set references for ADO, and yet I have the
Error-message.
In my splitted database (see my second sub-routine that replaces my first
sub-routine), I did set the ADO reference and then I don't have the
Error-message.
This means (I think) that there are no same names in my declarations.
In my case, I can only split my tables in ADO-mode and not in DAO-mode.

 

[aaron.kempf]

I'm not so sure that I agree with that.

DAO might be the _OLD_ way to communicate with _JET_ files.
But 'Access Files' do not explicitly refer to MDB. MDB is the
deprecrated format; it has not had any improvements since 1995.

DAO is too buggy for real world usage-- move to ADO.

-Aaron

 

[Brendan Reynolds]

The problem may be that your DAO code is attempting to open a table-type
recordset, and you can't do that with linked tables. Try dbOpenDynaset
instead of DB_OPEN_TABLE.

 

[Tony Toews [MVP]]

DAO might be the _OLD_ way to communicate with _JET_ files.
But 'Access Files' do not explicitly refer to MDB. MDB is the
deprecrated format; it has not had any improvements since 1995.

DAO is too buggy for real world usage-- move to ADO.

Aaron is completely wrong.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[AccessVandal via AccessMonster.com]

As Douglas said, you need to both references.

Access by default is ADO (A2K and A2003 I believe.) You didn't say what
version or the SP.

To use DAO, you need to use the references like...

Public ws As DAO.Workspace
Public db As DAO.Database
Public tbInitialisatie As DAO.Recordset

 

[David W. Fenton]

m:

DAO might be the _OLD_ way to communicate with _JET_ files.
But 'Access Files' do not explicitly refer to MDB.

What, then, would they refer to? ACCDB? If so, then, yes, DAO is the
preferred interface, and it's the new update of DAO that shipped
with A2K7. DAO is alive and well, and is still the preferred
interface for all Jet data (ACCDB is still Jet data).

I don't understand your insistence on posting such obviously
erroneous information. Do you place no value whatsoever on your
credibility?

Or are you just batshit crazy?

 

[Stanley L.]

I converted a 2000 app to 2007. I have mostly DAO with a little ADO. ADO
seemed more useable for connections and linking to a BE datafile. I found
that 2007 is more sensitive to stating whether, for example, it is a DAO or
ADO recordset being used. It was very worthwhile searching the entire app
code and being specific whether you are currentl using DAO or ADO. For
example, 'dim rst as recordset' became 'dim rst as DAO.recordset' or 'dim rst
as ADO.recordset' as the case may be. I would expect that this applies to all
the duplicate words such as 'property' etc. as previously mentioned. HTH

 

[David W. Fenton]

I converted a 2000 app to 2007. I have mostly DAO with a little
ADO. ADO seemed more useable for connections and linking to a BE
datafile.

If you're using linked tables, ADO is pretty much useless, except if
you have server-side code that you need to run, such as certain kind
of SQL Server stored procedures.

I found
that 2007 is more sensitive to stating whether, for example, it is
a DAO or ADO recordset being used. It was very worthwhile
searching the entire app code and being specific whether you are
currentl using DAO or ADO.

While I have never created a single app that utilizes ADO with early
binding (well, except for a mini-app that utilizes the ADO
UserRoster to find who is logged in to a database), I fully specify
all my uses of DAO objects, just in case somewhere along the line an
ADO reference should get added (by me or some future developer
taking over from me).

For
example, 'dim rst as recordset' became 'dim rst as DAO.recordset'
or 'dim rst as ADO.recordset' as the case may be. I would expect
that this applies to all the duplicate words such as 'property'
etc. as previously mentioned.

I think it's wise to do it for even the things that do not overlap,
such as DAO.Database, a data type that doesn't exist in ADO. The
reason is I think the programmer ought to be aware of what libraries
are providing the functionality being used.