Opening or Importing Access 97 mdb file

[stepone]

Hi,

We regularly receive Access '97 format mdb files from one of our customers. We have to process the data in these files, and to do this we open them on an old pc, running Access 2003, convert them to 2003, and then open them in Access 2016.

We want to retire this old pc but we are unable to open these files with our current version of Access (2016). Is there any way of importing or otherwise viewing the data in an Access 97 mdb file without using Access 2003? I have googled quite a bit without success. Hoping that some of the experts on here can come up with something !

Thanks,
StepOne

 

[Ranman256]

the old format db and be read by any newer Access version.
you dont have to convert. You can , but not needed.

 

[stepone]

the old format db and be read by any newer Access version.
you dont have to convert. You can , but not needed.

Thanks, I don't want to convert - just open. If I try to open this file using Access 2016 I get a message "Cannot open a database created with a previous version of your application." Can you explain how I can open it?

Thanks,
StepOne

 

[CJ_London]

Backwards compatibility for Access 97 databases ceased with Access 2013 - see this link

https://support.office.com/en-us/ar...ess-2013-BC006FC3-5B48-499E-8C7D-9A2DFEF68E2F

You can no longer open an Access 97 database in Access 2013. You'll need to upgrade the file. To do this, open it in Access 2010 or Access 2007, save it in the .accdb file format, and then open it in Access 2013.
This change also affects databases that are linked to Access 97 databases.


So you will have to carry on doing what you are doing - or persuade your client to upgrade to 2003 or later. Perhaps give them your copy of 2003?

 

[Ranman256]

ALL access apps can open older versions. This error makes no sense unless it was not a full install maybe.

 

[stepone]

ALL access apps can open older versions. This error makes no sense unless it was not a full install maybe.

That's not true Ranman. The latest Access apps will NOT open Access 97 files. See the link in the post above, or just google it - plenty of people with the same problem.

Cheers,
StepOne

 

[stepone]

Backwards compatibility for Access 97 databases ceased with Access 2013

So you will have to carry on doing what you are doing - or persuade your client to upgrade to 2003 or later. Perhaps give them your copy of 2003?

Unfortunately the client is a global multi-national. The file in question is produced from one of their billing systems, which serves many 10s of millions of customers. I do not know why it is produced in Access format, as the system in question must run on mainframes, and the application must be written in something more traditional.

I had hoped that someone, somewhere might have written an app to allow data to be extracted from these files. The annoying thing is that the pc in question is on its last legs. If it dies then we are in trouble.

Can I ask a follow up - is is possible to install Access 2003 on my Windows 10 pc to run alongside Access 2016?

Thanks,
StepOne

 

[Gasman]

Could you perhaps use Excel to get at the data in the mdb and then use Access 2016 to get from the Excel sheet?

Roundabout I know, but is it an option?

 

[CJ_London]

is is possible to install Access 2003 on my Windows 10 pc to run alongside Access 2016?

yes and no - yes you can install both, and no, you cannot run them both at the same time - you will effectively be uninstalling/reinstalling each version which will be tiresome.

Gasman's suggestion is worthwhile following up - just be aware of the differences between 64bit office and 32bit office if you have installed 64bit excel.

I presume the .mdb only contains tables.

You could also contact the billing system supplier about an 'upgrade', if they are exporting '97 mdb's they must have come across the problem sometime over the last 3 or 4 years and will be experiencing more and more issues as time goes on. Don't know when but there will be a point where backward compatibility will cease for 2000 and 2003 as well.

Other alternative is you bin your 2016 access and revert to 2010 32bit. (not 2013)

Or buy a separate shiny new pc and install 2003 or 2010 - if they are a multinational client, the contract surely has to be big enough to support a spend of a few 100 £/$.

 

[Gasman]

I would hazard a guess that the initial data comes from the mainframe as a comma delimited file in the first place.?
You could perhaps cut out the middle man and request the csv file if that is the case.?

Further to CJ_London's ideas, you could try the other version in a VM ?
That way nothing would get affected with the other install?

 

[HiTechCoach]

It is true that JET3.x (Access 97 format) support was dropped with Access 2013/2016.

What about a free tool does not require a separate PC or VM or Access 2000/2002/2003 license?

There is a way to convert the JET3 DB to a JET4 mdb that will be compatible with 2013 without installing Access 2003 or any version of Access. And works side-by-side with 2000/2002/2003 and 2007/2010/2013/2016

Use this free tool: Jet compact utility (Click Here)

HINT: You can call it via a command-line to convert the file format from within your 2013 app! Or use a script to mass convert them.

Hint 2: For anyone still using a .MDB - This utility will run on a Windows Machine without Access installed! Great for compacting and repairing a JET3 or JET4 database on a server.

 

[Gasman]

Boyd,

Not had time to try it out, but it does say in the doc

Jetcomp.exe is a freely distributable utility, but requires that you have one of the following products installed:

Microsoft Office 97
Microsoft Office 2000
Microsoft Access 97
Microsoft Access 2000
Microsoft Visual Basic® 5.0
Microsoft Visual Basic 6.0
A Microsoft Office Developer Edition 97 run-time application that includes the run-time version of
Microsoft Access 97
A Microsoft Office Developer 2000 run-time application that includes the run-time version of
Microsoft Access 2000
A Microsoft Visual Basic 5.0 run-time application that includes Microsoft Jet database engine 3.5
A Microsoft Visual Basic 6.0 run-time application that includes Microsoft Jet database engine 3.5 or 4.0

 

[HiTechCoach]

Boyd,

Not had time to try it out, but it does say in the doc

Jetcomp.exe is a freely distributable utility, but requires that you have one of the following products installed:

Microsoft Office 97
Microsoft Office 2000
Microsoft Access 97
Microsoft Access 2000
Microsoft Visual Basic® 5.0
Microsoft Visual Basic 6.0
A Microsoft Office Developer Edition 97 run-time application that includes the run-time version of
Microsoft Access 97
A Microsoft Office Developer 2000 run-time application that includes the run-time version of
Microsoft Access 2000
A Microsoft Visual Basic 5.0 run-time application that includes Microsoft Jet database engine 3.5
A Microsoft Visual Basic 6.0 run-time application that includes Microsoft Jet database engine 3.5 or 4.0

You would be using a JET3.5/4 database with one of the listed applications.
That is why you would have a .MDB that might need compacted and/or repaired

In reality, all the JET Comp utility requires to run is the JET4 DLLs to be installed.

My guess is that Microsoft gave you a list of applications that the new would have installed the JET DLLs.

I never compact a database over the wire. I only compact an Access database on a local hard drive.

How I discover the Jet Compact Utility only required the JET4 DLLs.
One day in fall of 2000 when I needed to compact some JET4 databases on the local hard drive of a Windows 2000 Server used as a file server.

I installed the JET Comp 4 utility. Ran it and compacted several databases. I went to open Access 2000 to test the database and discover that there was not a single Office app installed on the server. There was actually no other user software installed. Nothing that would open an Access database. I looked in the system folder and sure enough the JET4 DLLs were there. The next time I did an install of XP I check and sure enough. The JET4 DLLs were there.

I later discovered that at JET4 DLLs became part of the Windows OS installed for at least Windows 2000 and XP. They are used internally by Windows200/XP! There were several issues with the new JET4 database engine when it was first released. There were eight (SP8) updates before it was really stable. The JET4 updates were part of the Windows Update Service and not part of Office updates.

Since the year 2000 on Windows 2000/XP and later, I have been running the JET Comp Utility without any of the above the listed software installed.

 

[stepone]

Hi HiTechCoach,

This looks like exactly what I want, but I can't quite get it to work.

The original database is password protected. When I use the utility to convert the database, it prompts me for the password, then creates a new database.

So far, so good. But when I try to open the new database, it prompts me for the password again. I enter the password, but Access then comes up with a dialog box "Database Enhancement. This database was created using a previous version of Microsoft Access. ... Access will upgrade your database...Proceed?". I click on Yes, and it comes up with "You are trying to convert a password protected database. Remove the password and try again." I click OK, and it leaves me with a blank Access window.

If I click on 'No' then Access crashes "Microsoft Access has stopped working" and all I can do is close it.

So it's asking me to remove the password before it can complete the conversion, but I can't do that without the old version of Access.

Any suggestions?
Thanks,
StepOne

 

[stepone]

Update! Although Access crashed when I clicked on 'No' , when I opened the new database up again all the tables are there - so the data is accessible! I just need to play around with this a bit more and see if I can get it working a bit more smoothly, and hopefully this will let us do what we want.

Thanks again!
StepOne

 

[HiTechCoach]

Update! Although Access crashed when I clicked on 'No' , when I opened the new database up again all the tables are there - so the data is accessible! I just need to play around with this a bit more and see if I can get it working a bit more smoothly, and hopefully this will let us do what we want.

Thanks again!
StepOne

You're welcome. Glad to hear you are making progress.

Password protect databases cause lots of issues.

Sounds like you were able to use the Jet Comp utility to convert the MDB from Jet 3.5 (Access 97) to Jet4 (Access 200) format.

From Access 2013/2016 I would not try to open the converted database. I would create linked tables from an accdb to the mdb.