VBA Reference Libraries-Access 2003 vs 2007

[djf]

I'm in the process of converting from Access 2003 to Access 2007. When I
converted my 2003 db to 2007 the VBA code would not run. I changed the
Reference libraries in 2007 to match what I had in 2003 and then the code
worked. I'm looking for some clarification on what the difference is between
my 2003 set up and the default set up for 2007. What is the difference in the
libraries?

All of the references were the same in 2003 and 2007 except for the following.

2003
Microsoft DAO 3.6 Object Library

2007
Microsoft Office 12.0 Access database engine Object Library
Microsoft Visual Basic for Applications Extensibility 5.3

I deleted the two references for 2007 shown above and put in the DAO 3.6 in
their place and the code works.

 

[Tom van Stiphout]

On Fri, 4 Jun 2010 07:00:19 -0700, djf <[email protected]>
wrote:

"...would not run" is not very descriptive. Can you be more specific?
Is this perhaps a Trusted Location issue?

I don't know how "Microsoft Visual Basic for Applications
Extensibility 5.3" got in there, but it is not a standard library and
not needed for most applications. I'm 99% sure your app will still
compile (Code window > Debug > Compile) if you uncheck it.
"Microsoft Office 12.0 Access database engine Object Library" includes
DAO (which you can see from the Object Browser when checking and
unchecking this library). My DAO code works just fine with it and I
don't have to check DAO 3.6 which is from the 2003 timeframe.

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

If you're still using the MDB file format, use Microsoft DAO 3.6 Object
Library


If you use the new ACCDB file format in Access 2007, you need Microsoft
Office 12.0 Access database engine Object Library

 

[David W. Fenton]

If you're still using the MDB file format, use Microsoft DAO 3.6
Object Library

If you use the new ACCDB file format in Access 2007, you need
Microsoft Office 12.0 Access database engine Object Library

I don't think this is correct. Isn't the DAO reference pointed to
the A2007 version of DAO when you run the MDB in A2007?

OK, I just tested, and I'm wrong. But I'm actually surprised since
the ADO reference is for the database engine you're using, and if
you're using A2007, you're using ACE, right?

Or is all interaction with MDBs handled by Jet 4, and not by the
ACE?

I don't have a machine with A2007 and no earlier version of Access
installed to test, but I'd be interested to see what the results are
in opening an MDB from an earlier version with the DAO 3.6
reference.

I just tested creating an MDB in A2007, and it uses DAO 3.6. I can
manually replace the DAO 3.6 reference with the new one, but it
doesn't do it by default.

Hmm. A lot of my assumptions about the ACE may be wrong...

 

[Douglas J. Steele]

OK, I just tested, and I'm wrong. But I'm actually surprised since
the ADO reference is for the database engine you're using, and if
you're using A2007, you're using ACE, right?

Or is all interaction with MDBs handled by Jet 4, and not by the
ACE?

I believe that all interactions with MDBs is handled by Jet 4, not ACE.
Thinking about it, that's probably how I would have done it to ensure
consistency with older versions.

 

[Douglas J. Steele]

I believe that all interactions with MDBs is handled by Jet 4, not ACE.

I also believe my grammar is getting attrocious now that I've finished
writing the book. <g>

 

[David W. Fenton]

I believe that all interactions with MDBs is handled by Jet 4, not
ACE. Thinking about it, that's probably how I would have done it
to ensure consistency with older versions.

It's not how they did it with any previous version of Jet, but it
does allow the abandonment of ULS in the easiest possible way.

I have to change my usual spiel on replication support, though, as
I've assumed the ACE supported it, just not for ACCDB. In light of
this, it seems likely to me that it isn't a file format issue, but
baked into the ACE.