OpenCurrentDatabase or OpenDatabase - different versions of Access

[CPutnam]

Here's the situation:

I have an application that I've converted to Access 2002. I've tested it
pretty thoroughly and it's ready to be distributed to users. BUT the users
already have the Access 97 application and they have a lot of custom reports
and queries that need to be exported and then imported into the Access 2002
application.

The original application creator included an Upgrade routine that the users
have often used successfully when we have needed to distribute bug fixes,
additional standard reports & queries, etc. I'm trying to use this routine
to transfer all the custom information from the Access 97 app to the
converted App.

Basically, the routine opens an initialized converted database (i.e. one
without any custom objects), then opens the Access97 database, exports the
custom objects into a temporary database, and then imports them into the
converted database which then becomes the LIVE database. Everything works
fine, mostly, except for this line:

appToolkit.OpenCurrentDatabase mvarSourceFile

(AppToolkit is defined in the declarations as: Dim appToolkit As New
Access.Application
mvarSourceFile is the location of the old application .mdb file (remember
that this file is an Access97 database).)

Apparently the .mdb file for an Access97 database can't be opened from an
Access 2002 database. BUT I have found that if I first convert the Access97
database to an Access 2002 database (ignoring the few errors I get because
of different error codes) then this line doesn't cause any problems and the
upgrade goes through okay.

If at all possible, I don't want to make the users (there are about 90 of
them spread throughout the US) have to upgrade their databases as a separate
step (this application happens to have a Front End, a BackEnd, and an extra
little database that would all need to be converted) -- Partly because
people get really worried when they see error messages even if you tell them
to ignore them and also because extra steps always leave room for user error
and more problem.

So I'm trying to figure out how to get this upgrade to just open the
Access97 database and do what needs to be done. It's okay with me if it
opens the database and does the conversion to Access 2002 on the fly if it
needs to (I suppose that the users will still get the error messages but
that doesn't seem to affect the conversion). I have read other postings
about closing the application I'm coming from when I open the other .mdb
file but I don't think that's an option because, as I explained above, I
have to import the custom objects into it. And I certainly couldn't close
the converted database before opening the Access97 database which is where
the problem lies.

I haven't been able to find anything about how to structure
OpenCurrentDatabase or OpenDatabase in order to tell the routine what
exactly it is opening and give instructions on how to do it. Does anything
like that exist?

Thanks in advance for any ideas from anyone. Carol.

 

[John Nurick]

Hi Carol,

appToolkit.OpenCurrentDatabase mvarSourceFile

(AppToolkit is defined in the declarations as: Dim appToolkit As New
Access.Application
mvarSourceFile is the location of the old application .mdb file (remember
that this file is an Access97 database).)

I don't know what the problem is but it's probably not in the statement
above. AFAIK Access 2002 can automate an Access 97 mdb. Certainly Access
2003, using either of the techniques in the demo procedure below, and
probably some others. What actual error messages are you getting?

Sub OpenDBTest()
Dim appA As Access.Application
Dim T As DAO.TableDef

Set appA = GetObject("C:\TEMP\Test.97.mdb")
Debug.Print "DB opened with GetObject is " _
& appA.CurrentProject.Name
For Each T In appA.CurrentDb.TableDefs
If Left(T.Name, 4) <> "MSYS" Then
Debug.Print T.Name
End If
Next
Debug.Print vbCrLf
appA.CloseCurrentDatabase
appA.Quit
Set appA = Nothing

Dim appB As New Access.Application

appB.OpenCurrentDatabase "C:\Temp\Test.97.mdb"
Debug.Print "DB opened with OpenCurrentDatabase is " _
& appB.CurrentProject.Name
For Each T In appB.CurrentDb.TableDefs
If Left(T.Name, 4) <> "MSYS" Then
Debug.Print T.Name
End If
Next
Debug.Print vbCrLf
appB.CloseCurrentDatabase
appB.Quit
Set appB = Nothing

End Sub

 

[CPutnam]

Thanks for the reply, John.

The error message I'm getting is just a generic message that is in the code
"Unable to upgrade your Toolkit. Please contact Technical Support."
(Toolkit is the name of the application. There is an error trap that
displays some of the errors but this one doesn't display any number code or
further explanation.

And I'm sorry but I don't understand how the code could be at fault if it
works when I have an converted Access .mdb file with the same name and same
path as the Access 97 .mdb file (which doesn't work).

I'm in the middle of setting this up again and will test the demo's that you
provided. In the meanwhile, if you (or anyone else) have any other ideas,
please let me know.

Thanks in advance. Carol.

 

[John Nurick]

Hi Carol,

Comments inline.

Thanks for the reply, John.

The error message I'm getting is just a generic message that is in the code
"Unable to upgrade your Toolkit. Please contact Technical Support."
(Toolkit is the name of the application. There is an error trap that
displays some of the errors but this one doesn't display any number code or
further explanation.

If it's a generic message that's in your code, put a breakpoint in the
relevant error handler and inspect Err.Number and Err.Description.

And I'm sorry but I don't understand how the code could be at fault if it
works when I have an converted Access .mdb file with the same name and same
path as the Access 97 .mdb file (which doesn't work).

I'm in the middle of setting this up again and will test the demo's that you
provided. In the meanwhile, if you (or anyone else) have any other ideas,
please let me know.

Thanks in advance. Carol.

 

[CPutnam]

The error I am getting is 7876. In the error code list, it says that
indicates:

There isn't enough temporary disk space to complete the
operation.@Free disk space, and then try the operation again.
For more information on freeing temporary disk space, search the
Microsoft Windows Help index for 'disk space, freeing'.
For information on compacting a database to free disk space, click
Help.@@2@209009@3


I've got 3.1 gigs free space and the application .mdb I'm attempting to open
is about 17 meg. Could this error be located to the size of my swap file?
(I'm running Windows XP and I'm pretty sure that I haven't changed the
default (but I can't find where it's set...and I've looked and looked).

Thanks for any more ideas. Carol.

Hi Carol,

Comments inline.

Thanks for the reply, John.

The error message I'm getting is just a generic message that is in the code
"Unable to upgrade your Toolkit. Please contact Technical Support."
(Toolkit is the name of the application. There is an error trap that
displays some of the errors but this one doesn't display any number code or
further explanation.

If it's a generic message that's in your code, put a breakpoint in the
relevant error handler and inspect Err.Number and Err.Description.

 

[John Nurick]

Umm. The first thing I'd suspect is a problem with this particular mdb.
If you create a new Access 97 mdb file containing one or two simple
tables, can your code open it or do you get the 7876 error?

If your code works on a new simple Access 97 mdb, make a copy of the
problematic one, open it in Access 97 and compact it. If the compacted
file works OK, well and good. Otherwise, I'd assume the mdb file is
corrupt and (working with a copy) try to repair it (see Tony Toews's
corruption FAQ at http://www.granite.ab.ca/access/corruptmdbs.htm).

Good luck, and please post back here to say how you get on.

The error I am getting is 7876. In the error code list, it says that
indicates:

There isn't enough temporary disk space to complete the
operation.@Free disk space, and then try the operation again.
For more information on freeing temporary disk space, search the
Microsoft Windows Help index for 'disk space, freeing'.
For information on compacting a database to free disk space, click
Help.@@2@209009@3


I've got 3.1 gigs free space and the application .mdb I'm attempting to open
is about 17 meg. Could this error be located to the size of my swap file?
(I'm running Windows XP and I'm pretty sure that I haven't changed the
default (but I can't find where it's set...and I've looked and looked).

Thanks for any more ideas. Carol.

 

[CPutnam]

Well, more information to add to the mix:

I was able to open the problematic database using the code that you
originally sent, John, a module in a simple database with nothing in it but
the code. But I'm still not able to open it as part of the upgrade process.
So could it somehow be a memory problem?

Also, I may have copied the error message incorrectly. I think the one I'm
looking for is 7866. The explanation for this one is: 'Microsoft Access
can't open the database because it is missing, or opened exclusively by
another user

But the database is local on my computer only and it isn't being used by any
other program (I've tried the upgrade right after rebooting the computer) so
I don't know what else to look at.

Question: The names of the two databases are the same although they are in
different subdirectories, with very different paths. Would that affect
this? (It never did while we were doing upgrades of the Access97 version.)

Sorry for the mixup of the error message. And thanks for any other ideas.
Carol.

Umm. The first thing I'd suspect is a problem with this particular mdb.
If you create a new Access 97 mdb file containing one or two simple
tables, can your code open it or do you get the 7876 error?

If your code works on a new simple Access 97 mdb, make a copy of the
problematic one, open it in Access 97 and compact it. If the compacted
file works OK, well and good. Otherwise, I'd assume the mdb file is
corrupt and (working with a copy) try to repair it (see Tony Toews's
corruption FAQ at http://www.granite.ab.ca/access/corruptmdbs.htm).

Good luck, and please post back here to say how you get on.

 

[John Nurick]

For starters I'd change the name of one of the databases. That will
exclude
(a) the possibility that Access has problems handling two databases with
the same name (I don't think it does, although this is a traditional
limitation Excel's)
(b) the possibility that your code is trying to open the other database
with the same name rather than the one you think

If changing the name makes it work, then investigate (a) and (b).
Otherwise, trace the execution of your code from the very beginning to
make certain that some other procedure has not accessed the database
first and failed to release it.

Well, more information to add to the mix:

I was able to open the problematic database using the code that you
originally sent, John, a module in a simple database with nothing in it but
the code. But I'm still not able to open it as part of the upgrade process.
So could it somehow be a memory problem?

Also, I may have copied the error message incorrectly. I think the one I'm
looking for is 7866. The explanation for this one is: 'Microsoft Access
can't open the database because it is missing, or opened exclusively by
another user

But the database is local on my computer only and it isn't being used by any
other program (I've tried the upgrade right after rebooting the computer) so
I don't know what else to look at.

Question: The names of the two databases are the same although they are in
different subdirectories, with very different paths. Would that affect
this? (It never did while we were doing upgrades of the Access97 version.)

Sorry for the mixup of the error message. And thanks for any other ideas.
Carol.