database variable type error won't go away?

[1DMF]

I keep getting a user defined type error with

    Dim db As Database

But I have referenced the MS DAO 3.6 object library, so why am I still getting this error?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

I fixed it by explicitly declaring it as DAO.Database

What i don't undertstand is in other applications, I haven't had to prefix it with DAO?

Why would some apps work without it, yet this new one won't?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

Although that seems strange, you should ALWAYS fully qualify any class that has a competing class name. If not expect these types of errors.

Here is a couple of reasons why. Assume I want a DAO recordset. Assume I have 2 references listed in the below order.

Microsoft ActiveX data objects x
dao 3.6


If I then do the following
dim rs as recordset
set rs = me.recordset
The above throws an error.
The below does not
dim rs as dao.recordset
set rs = me.recordset

The reason is that by not fully qualifying "recordset" it picks the first one listed in the references, and assumes you wanted an ADODB recordset. Since a form returns a DAO recordset you get an error trying to assign a dao recordset to an adodb.recordset.

If you would go back and reorder the references the original code would work.

This used to be a huge issue in A2k. In Access 97 the default reference was DAO. In A2k they changed it to ADODB as a default reference. I think in 2003 they went back to forcing you to select.

 

[PHV]

MajP, which library apart from DAO will expose a Database object ?

 

[MajP]

I do not think I said that any other library exposes a database object. I do not think that was my point, and I stated that the observed behavior was abnormal. However, when I am working with the two competing libraries I fully qualify and therefore have no issues.
dim rs as dao.recordset
dim rs2 as adodb.recordset
dim flds as dao.fields
dim flds2 as adodb.fields
dim fld as dao.field
dim fld2 as adodb.field
And thus out of practice
dim db as dao.database

If you disagree with this practice, then state so. However, I am sure you have seen numerous thread where people have run into trouble by not qualifying the dao or ado objects.
However, do not waste your time quizzing me on Access VBA when you know the answer. I think I have proven I know just a little bit in this area.

 

[1DMF]

PHV I have found that the MS Office ACCess Data Object conflicts with DAO 3.6

But it didn't seem to matter which order I have my references, in my old db it doesn't need the prefix in the new one it does.

The only difference is the old one is converted from 2003 -> 2007 and this was a new one from scratch which is the only difference between the two?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PHV]

But it didn't seem to matter which order I have my references
You shouldn't have DAO and Access Data Objects both ticked.
Use either one or the other, but not both.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

You can't PHV that's what i was just saying, Access moans about a conflict , so i don't (can't) have them both ticked!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

Actually you can, and you probably already do. The DAO model is now contained in (2007)
Microsoft Office 12.0 Access Database Engine Object (ACE)

So this goes back to the default idea. DAO loads by default. You can however add a reference to ADO. The name conflict is probably coming from trying to load a DAO library. That is because you already have the Access Database Engine Object library loaded by default.

The bottom two references are the default ACE references that provides DAO, and the ADO library below it.

 

[1DMF]

Well mine is actually '14' (Access 2010) and i have unticked it, so i can use DAO 3.6

these are the ones I have ticked in the app that doesn't moan at not using the DAO prefix

VB For Applications
MS Access 14 Object library
OLE Automation
DAO 3.6 Object library

Yet having only those selected in the new app won't compile without DAO prefix ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

You should keep
Microsoft Office 14.0 Access Database Engine Object (ACE)
selected since this has the most current version of DAO:

You basically loaded an older library.
So if you only have ACE 14.0 (no other DAO, ADO references) loaded, you should not have to fully qualify. If you still do, in order for it to compile, then I would consider creating a new database and import all your objects into it. There may be some corruption.