Opening & saving a database in '07 as an '03

[Jey]

I want to work from home... but I have Office '07 there and '03 at work. Can
I open an '03 Access database in '07 but force it to remain in a naitive '03
format... including all object library references etc. etc.?

Just to see what would happen, I tried opening my database in '07, modified
some VBA code, and saved it. It saved it as a .mdb (without me having to tell
it to...). Now when I try to open it in '03, I get all kinds of error
messages and things just not working right. It changed some object library
references to newer versions, I get error messages like "missing dll" and
"invalid syntax" even on functions that I didn't change. When I try to open
the database window (which I don't want opening at startup) it just hangs.

I've heard that holding the shift key while opening a database can bypass
certain things... can someone clarify exactly what that does, and will it
help me here?

Thanks for any advice you can give,
Jey

 

[Tom Wickerath]

Hi Jey,

Holding down the shift key while the database is opening will cause any
startup options to be ignored, as long as the bypass key has not been
previously disabled. This includes bypassing an Autoexec macro and/or a form
set as the startup form.

I suspect that you have one or more references that are currently marked as
MISSING, in the copy that you edited in A2007 and attempted to re-open in
A2003. Here are two excellent articles on the subject of missing references:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Jey]

Hi Tom,

Yes, in the version that I edited at home and now want to open in A2003, the
Excel 12.0 library is marked 'Missing'. In the copy of the database that I
didn't take home, I have the Excel 11.0 library checked. (I have functions
that export data to excel)

One of the articles said to uncheck the missing library, get out of Access,
then go back in and re-check it. I tried, but it won't let me uncheck the
'missing' Excel 12.0 library!

Editing things in the database (tables etc.) doesn't seem to make it change
that library, just when I open the VBA editor... so it seems to work fine in
A2007 using the Excel 11.0 library. Do the 11.0 and 12.0 libraries have the
same name & location?

I think what it comes down to is can I edit the VBA code in A2007 without it
automatically 'upgrading' to the Excel 12.0 library? I don't want to have to
go in and mess around with libraries every time I want to give the database
to someone using A2003!!

Thanks,
Jey

 

[Tom Wickerath]

Hi Jey,

One of the articles said to uncheck the missing library, get out of Access,
then go back in and re-check it. I tried, but it won't let me uncheck the
'missing' Excel 12.0 library!

This is unusual. I have seen in the past where there is sometimes a
reference to the Microsoft Forms Library (version 2 [?]) that I've never been
able to remove manually. The solution is to create a brand new database, and
import all objects into it. I have explicit instructions in the lower half of
page 3 of a Word document that I call "Access Links". You are welcome to
download a zipped copy from this location:

http://www.accessmvp.com/TWickerath/index.htm

Editing things in the database (tables etc.) doesn't seem to make it change
that library, just when I open the VBA editor... so it seems to work fine in
A2007 using the Excel 11.0 library.

You know, I'm honestly not sure if all of the references are fixed when
first opening the database, or on-demand (ie. the first time a VBA function
is called). In any case, if you ever use a common function in a query, such
as Date(), Left, Right, InStr, etc., you would be making a call to VBA, in
which case your references would be "fixed" for you.

Do the 11.0 and 12.0 libraries have the same name & location?

I believe they have the same name. The location is likely not the same.

I think what it comes down to is can I edit the VBA code in A2007 without it
automatically 'upgrading' to the Excel 12.0 library?

I wouldn't be willing to bet any money on this.

I don't want to have to
go in and mess around with libraries every time I want to give the database
to someone using A2003!!

This is the exact reason that using late binding is the preferred option.
Declaring variables as Objects is using Late Binding. Here are two examples:

Early Binding (requires a checked reference to the Excel {version} Object
Library)
Dim xlApp As Excel.Application

Late Binding (does not require a checked reference)
Dim xlApp As Object

It is easier to develop code using early binding, because you get the
benefit of Intellisense. However, you get a more robust application by
converting your code, once you have it working as desired, to late bound code
because your code is not tied to a version specific object library.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Jey]

Thanks!

That sounds like exactly what I needed to know. I'll try to remove all early
binding from my code and hopefully that will fix it. Will it just be the
Excel reference that I want to eliminate from the reference list? It's the
only one that I checked myself. There were 5 others already checked:
-VB for Applications
-Microsoft Access 11.0 object library
-OLE automation
-Microsoft DAO 3.6 object library
-Microsoft ActiveX Data Objects 2.1 library

Jey

Hi Jey,

One of the articles said to uncheck the missing library, get out of Access,
then go back in and re-check it. I tried, but it won't let me uncheck the
'missing' Excel 12.0 library!

This is unusual. I have seen in the past where there is sometimes a
reference to the Microsoft Forms Library (version 2 [?]) that I've never been
able to remove manually. The solution is to create a brand new database, and
import all objects into it. I have explicit instructions in the lower half of
page 3 of a Word document that I call "Access Links". You are welcome to
download a zipped copy from this location:

http://www.accessmvp.com/TWickerath/index.htm

Editing things in the database (tables etc.) doesn't seem to make it change
that library, just when I open the VBA editor... so it seems to work fine in
A2007 using the Excel 11.0 library.

You know, I'm honestly not sure if all of the references are fixed when
first opening the database, or on-demand (ie. the first time a VBA function
is called). In any case, if you ever use a common function in a query, such
as Date(), Left, Right, InStr, etc., you would be making a call to VBA, in
which case your references would be "fixed" for you.

Do the 11.0 and 12.0 libraries have the same name & location?

I believe they have the same name. The location is likely not the same.

I think what it comes down to is can I edit the VBA code in A2007 without it
automatically 'upgrading' to the Excel 12.0 library?

I wouldn't be willing to bet any money on this.

I don't want to have to
go in and mess around with libraries every time I want to give the database
to someone using A2003!!

This is the exact reason that using late binding is the preferred option.
Declaring variables as Objects is using Late Binding. Here are two examples:

Early Binding (requires a checked reference to the Excel {version} Object
Library)
Dim xlApp As Excel.Application

Late Binding (does not require a checked reference)
Dim xlApp As Object

It is easier to develop code using early binding, because you get the
benefit of Intellisense. However, you get a more robust application by
converting your code, once you have it working as desired, to late bound code
because your code is not tied to a version specific object library.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,

Yes, in the version that I edited at home and now want to open in A2003, the
Excel 12.0 library is marked 'Missing'. In the copy of the database that I
didn't take home, I have the Excel 11.0 library checked. (I have functions
that export data to excel)

One of the articles said to uncheck the missing library, get out of Access,
then go back in and re-check it. I tried, but it won't let me uncheck the
'missing' Excel 12.0 library!

Editing things in the database (tables etc.) doesn't seem to make it change
that library, just when I open the VBA editor... so it seems to work fine in
A2007 using the Excel 11.0 library. Do the 11.0 and 12.0 libraries have the
same name & location?

I think what it comes down to is can I edit the VBA code in A2007 without it
automatically 'upgrading' to the Excel 12.0 library? I don't want to have to
go in and mess around with libraries every time I want to give the database
to someone using A2003!!

Thanks,
Jey

 

[Tom Wickerath]

Hi Jey,

The first two references, Visual Basic for Applications and the Microsoft
Access {version} object library, are required of all databases. Access will
not allow you to remove these two references, even if you try. The next three
references you listed, OLE automation, Microsoft DAO 3.6 object library and
Microsoft ActiveX Data Objects 2.1 library are set by default for all new
databases created using Access 2003.

My feeling is that it is best to starve the references listing. If you don't
need a reference, then uncheck it. This way, you don't consume RAM memory
loading libraries that won't be used, and, more importantly, you help
minimize the chances of encountering a MISSING reference error in the future,
either by you or by someone else who is using an application that you
developed. Here are two excellent articles on the subject of references:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

So, how do you know if a reference is needed or not? The easiest way to
determine this is to uncheck one reference at a time, click on the OK button
to dismiss the Add References dialog box, and then attempt to compile your
code:

Debug | Compile {ProjectName}

where {ProjectName} is the name of your VBA project. Before removing any
references, make a backup copy of your database. You might even want to
capture an image that lists the checked references (one can also run code to
print this data out). Also, before removing any references, try compiling
your code using the command shown above, just to verify that you do not have
any pre-existing compile problems. You will know if your code compiles okay
if you do not receive an error. Also, if you immediatly attempt to compile
again, before making any change to code, you should see that the option is
"greyed out" (unavailable). This is a good sign. Once you have code that
compiles okay, then try removing the references one at a time, recompiling
each time. If the code compiles okay, then you did not need the reference
that you just removed. My guess is that you can remove the OLE Automation and
the ADO (Microsoft ActiveX Data Objects 2.1 library) references without a
problem. It is very common to find DAO code, so you may very well need that
one, but you can test it in the same way to verify.

I forgot to mention in my previous reply that your best chance of success is
to use the lowest version of Access for all of your development work. For
example, Access 2007 includes a new feature called embedded macros. The
various wizards have been re-written to use this feature. So, if you use the
wizards in A2007, and you then convert back to the A2000 or 2002/2003 file
format, you may very well find command buttons or other functionality that
simply doesn't work in the earlier version of Access.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________