Problems opening Access 2.0 databases in Access 2002 on Windows 2000

C

Curt

I have to extract data from legacy Access 2.0 databases.
We were running the process on Windows NT4, but now we've
upgraded to Windows 2000. Due to the changes in the MDAC
libraries, I need to convert my Access 2.0 databases to
Access 2002 before I run my automated extract process on
them.

I developed a small VB application to automatically
convert the Access 2.0 database to Access 2002. I
developed the application in VB5 on Windows XP. It works
fine in this environment.

However, our deployment environment is Windows 2000.
When I run my process, it fails. So, I just tried to
double-click on the Access 2.0 file, and I receive a
couple errors from Access 2002:

1.) Cannot find the *.mdb file.

I'd like to post the exact message, but this particular
error is hard to recreate. However, I find this error
message to be odd since I double-clicked on the *.mdb
file, so Windows shouldn't be complaining about not being
able to find it.

2.) Microsoft Access failed to convert or enable your
code modules.
Your computer may be low on disk space or memory.

With this second error, which I always get, sometimes the
database will open anyway or it'll open just fine on the
second attempt.
I have verified that there's ample disk space and memory,
so I think this is another bogus error message.

The automated process uses the call:
Set acObj = CreateObject("Access.Application")
acObj.ConvertAccessProject oldDbPath, newDbPath, 10

It gets part way through the conversion, then dies. The
error log reads:

Error converting database: The Microsoft Jet database
engine could not find the object ''. Make sure the
object exists and that you spell its name and the path
name correctly.

I have tried with other Access 2.0 databases with the
same result.

The ODBC driver version we are using on Windows 2000 is
4.00.6200.00
The version we use on our WinXP development machines is
4.00.6019.00

I have limited control over the tools and dll versions
that are being used. If there is something wrong with
the versions, I can forward up to the SA to see if they
could change them.

If anyone has an idea what the problem may be, I'd
appreciate the suggestion.

Thank you,
Curt
 
C

Curt

Actually, there are errors accessing Access 2.0 databases
with the MDAC libs that come with Windows 2000. We've
had several databases die during out data extraction
because of incompatibilities with Memo datatypes mostly.
Are Microsoft rep said we needed to pre-convert the
databases to Access 2002 using a VB solution posted on
MSDN. I implemented this and it resolved all of our
issues on Windows XP.

Going through the Access 2002 application to convert our
databases is not an acceptable solution as this process
must be automated in a batch. We have a Java application
that performs the data extraction and we must call our VB
utility from that application.

Our automated tool is not defective as it runs perfectly
under Windows XP and uses the MSDN posted solution
precisely. Furthermore, if you read the original post,
we have problems just opening the Access 2.0 databases
through Access 2002. So, this is definitely not an issue
with the software we've developed but rather a system
issue.

Thank you for your response.
 
C

Chris Mills

Well, Access 2.0 knows nothing about MDAC. And it runs fine on Windows 2000
for me, just by copying the required files/shortcuts (there ARE no registry
settings).

So I think you're confused.

You CAN'T automate conversions. They need humanoid intervention. No-one has
ever successfully done automated conversions. If you claim to have, then you
are the first.

You are not supposed to open A2.0 databases in A2002. You are supposed to
convert them and fix them up.

Chris
 
D

david epsom dot com dot au

It looks like perhaps you think you are using ODBC
to connect to a Jet 2.x database, which you wish to
convert to Jet 4.x because you are having problems
with your ODBC connection. If so, you should use
DAO 3.6 (or perhaps ADOX) to convert your Jet 2.x
database to Jet 4.x

You will, of course need permission: if you are using
a password protected database, or one with user security,
please repost.

(david)
 
D

david epsom dot com dot au

Access uses the Jet Database Engine.
Access stores data in a Jet Database.
Access is a Rapid Application Development Tool
Access stores an Access Project in a Jet Database.
An Access project may contain Access forms, reports, macro's etc.

Jet converts Jet Databases from one Jet version to another.
Access uses Jet to convert a Jet database from one Jet version to another.

Access 2002 converts Access 2 projects stored in a
Jet 2.x database file to Access 2002 projects stored
in a Jet 4.0 database file. It uses Jet to read the
project from a Jet 2 file, and to write the reformed
project to a Jet 4 file.

You need to convert the Access project if you wish to open an Access 2
database in Access 2002.
If you only wish to use the data, you do not need to open the database in
Access.
If you only wish to use the data, you can link or query the data from
another program (Excel) or from a different database (Access 2002).

You may use the Jet Engine to convert the Jet file format,
without changing the Access Project inside the file.
To tell the Jet Engine to convert the Jet file format,
you may use the DAO or JRO com objects, controlled from
VB, VB.net, VBA, VBS, JS, C++, C#, or whatever, or
you may use the MS Compact and Repair utility.

It is not clear why you wish to convert the files, or the project stored
inside those files. Nothing you have posted so far indicates any need or
benefit. It may be that you should be asking a more general question.

(david)
 
C

Curt

Please see below for my response...
-----Original Message-----
You need to convert the Access project if you wish to open an Access 2
database in Access 2002.

Yes, that is what we are attempting to do. In the
original post I said:

"The automated process uses the call:
Set acObj = CreateObject("Access.Application")
acObj.ConvertAccessProject oldDbPath, newDbPath, 10"
If you only wish to use the data, you do not need to open the database in
Access.

We do not want to open it in Access. We want to use our
automated conversion. The only reason we were opening it
in Access was to see what if we could determine what was
wrong with our automated process. And attempting to open
the database in Access 2002 cause all sorts of
problems... prior to the point where it should ask if we
wish to convert or open the older database.
If you only wish to use the data, you can link or query the data from
another program (Excel) or from a different database
(Access 2002).

No, we need to access the data from our external
application. Using Excel or another Access 2002 database
would be inefficient and not suitable for batch
processing.
You may use the Jet Engine to convert the Jet file format,
without changing the Access Project inside the file.
To tell the Jet Engine to convert the Jet file format,
you may use the DAO or JRO com objects, controlled from
VB, VB.net, VBA, VBS, JS, C++, C#, or whatever, or
you may use the MS Compact and Repair utility.

If I am not mistaken, that is what we are doing in our
automated process per MS Knowledge Base article 304318.
It is not clear why you wish to convert the files, or the project stored
inside those files. Nothing you have posted so far indicates any need or
benefit. It may be that you should be asking a more
general question.

The why is kind of irrelevant here. We have a business
need to extract data from our legacy database and move it
into a production-grade database (Siebel/DB2 to be
precise). The need, obviously, is to extract data in a
batch process to place into the production-grade database.

Since we know that there's a report object that is
causing a problem, rather than try to patch the Windows
2000 server so that it reacts like our Windows XP
development platform does (on that platform, the error is
simply logged to zSysError and the conversion
successfully completes), it would probably be easier to
modify the VB application that converts the Access 2.0
database to remove the report first. Since I am not a VB
expert, I guess I'll need to research how this is
accomplished.

Thanks,
Curt
 
D

david epsom dot com dot au

Curt, If you wish to access your data from your
external application, you do not need to do an
Access conversion.
If I am not mistaken, that is what we are doing in our
automated process per MS Knowledge Base article 304318.

No, you are using an Access.Application object.
An Access.Application object attempts an ACCESS conversion.
If you wish to access your data from your external
application, you do not need to do an Access conversion.

If you wish to convert a JET 2 database to a JET 4
database for some unexplained reason (seldom necessary),
you can do so by using a DAO object, like this:
(VBS example)


-----------
Dim objEngine
dim strDB1
dim strDBtmp

strDB1 = "MyDB.mdb"
strDBtmp = "MyDB.tmp.mdb"

Set objEngine = wscript.CreateObject("DAO.DBEngine.36")

objEngine.CompactDatabase strDb1, strdbtmp,"",64

-----------
64 is the value for dao.dbVersion40

copy and past that text into a text file:
change the database name value in the text file:
rename the text file extension to .vbs:
double click on the .vbs file.
The new file ("MyDB.tmp.mdb") will be a JET 4.0 file.
This works on a PC that has Access 2K+ installed.

-----------
Alternatives: use a JRO object instead of a DAO
object: use VBA/VB/VC/whatever instead of VBS:
use the MS Compact & Repair application instead of
using automation.

(david)
 
C

Curt

David, see below...
-----Original Message-----
Curt, If you wish to access your data from your
external application, you do not need to do an
Access conversion.

Our external app is a Java/JDBC application. We can't
use JDBC reliably on a Jet 2.x database on Windows 2000.
We receive too many errors when selecting on Memo
fields. The Microsoft rep said our only choice is to
convert the legacy Access 2.0 database to Access 2002 and
then use JDBC to perform the queries.
Our choice of Java/JDBC is one we had no control over.
On a Windows platform, it's probably not the best
language to use against other MS technologies. But this
was the tool selection we were given.
No, you are using an Access.Application object.
An Access.Application object attempts an ACCESS conversion.
If you wish to access your data from your external
application, you do not need to do an Access conversion.

Forgive me, I'm not a MS developer so I'm not very up to
speed on this. I was under the impression that
Access.Application gave me a DAO object.
If you wish to convert a JET 2 database to a JET 4
database for some unexplained reason (seldom necessary),
you can do so by using a DAO object, like this:
(VBS example)


-----------
Dim objEngine
dim strDB1
dim strDBtmp

strDB1 = "MyDB.mdb"
strDBtmp = "MyDB.tmp.mdb"

Set objEngine = wscript.CreateObject("DAO.DBEngine.36")

objEngine.CompactDatabase strDb1, strdbtmp,"",64

-----------
64 is the value for dao.dbVersion40

copy and past that text into a text file:
change the database name value in the text file:
rename the text file extension to .vbs:
double click on the .vbs file.
The new file ("MyDB.tmp.mdb") will be a JET 4.0 file.
This works on a PC that has Access 2K+ installed.
-----------
Alternatives: use a JRO object instead of a DAO
object: use VBA/VB/VC/whatever instead of VBS:
use the MS Compact & Repair application instead of
using automation.

Thanks. We cannot use VBS, but we are allowed to use
VB5/6. I'll look into using a JRO object to do this.
Unfortunately, we can't use the MS Compact & Repair app
since we must run this as a batch process. If nothing
else, we will run the little conversion app I have on XP
and then move the convert databases over to our
application server for our external app to handle.

Thanks,
Curt
 
D

david epsom dot com dot au

Thanks. We cannot use VBS, but we are allowed to use
VB5/6. I'll look into using a JRO object to do this.

Whatever environment you are using for the
Access.Application object will handle a JRO object.

An installation of (?)VB(?) or Access will install
the JET licence, so even though you won't use the
Access.Application object, you may still need to have
Access (or Office?) installed on the workstation.
use JDBC reliably on a Jet 2.x database on Windows

JDBC uses JET 4, and JET 4 does have some problems with
JET 2 databases.
We receive too many errors when selecting on Memo
fields. The Microsoft rep said our only choice is to

Only JET 4 allows you to SORT on memo fields, but that
came with some problems: make sure you have applied SR6
or SR7.


(david)
-----------
Dim jro As jro.JetEngine
Set jro = New jro.JetEngine
jro.CompactDatabase _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDB1.mdb;, _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDB2.mdb;
------------
http://support.microsoft.com/defaul...port/kb/articles/Q230/5/01.ASP&NoWebContent=1
HOWTO: Compact Microsoft Access Database Through ADO
 
C

Curt

David, Thanks for the help. I'll take a look at those
links and I'll play with that code snippet.
I've been on with MS support and they also suggested
converting to 97 and then 2002 or trying to import each
table object into a fresh Jet 4 DB. I'm leaning towards
the second option of those two.

We were able to convert 2.0 to 2002 on NT4 and XP using
Access.Application.ConvertAccessProject and with
DAO.DBEngine.36.CompactDatabase. But still, even with
the latest Jet and MDAC on Win2000 we get the same errors
whether we are opening or converting the DB. Very weird.
I guess it's not worth it to continue to mix and match
library versions on Win2000 if a programmatic solution is
possible.

Curt
 
C

Curt

Odd, using CompactDatabase with a DAO or JRO object works
fine on NT4 and XP. It also appears to work on Win2K.
It creates a new database and doesn't issue any errors.
However, when you try to open it in the Access
application, nothing happens. If you double click on the
mdb, Access comes up, but doesn't attempt to open it.

I think we will need to pursue the option of importing
each table individually into a new Jet 4.x database.
Seems like the Access conversions work on the tables but
only die on that report which contains no source.

Thanks again for the help,
Curt
 
D

david epsom dot com dot au

However, when you try to open it in the Access
application, nothing happens. If you double click on

If you need to open them in Access, you must do
an Access conversion....
.... Tools | Database | Database conversion ...
In which case you will be back where you started.
I think we will need to pursue the option of importing
each table individually into a new Jet 4.x database.

File | Get External Data | Import |
<select file>
<tables> | <select all>
<Options> | <Relationships>

(david)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top