Automate Saving Access 2003 to Access 2000 and Access 97

[Cheryl]

I would like to write something to automate saving an access database that is
in 2003 backwards to 2000 and 97. It doesn't matter if it is a bat file, vb
program, macro or whatever.

 

[John Nurick]

Hi Cheryl,

If the databases just contain data, see DAO.DBEngine.CompactDatabase.

 

[Rick Brandt]

I would like to write something to automate saving an access database
that is in 2003 backwards to 2000 and 97. It doesn't matter if it is
a bat file, vb program, macro or whatever.

You do realize that converting "backwards" only changes the file format and does
nothing to ensure that the new file will actually function right?

If you use any features that are not supported in the older version then that
area will not work after the conversion.

It is much better to develop in the oldest version you need to support and then
convert "up" as that will (almost) always work without incident.

 

[Cheryl]

The reason we convert backwards is to provide public data to other entities
that do not have the latest and greatest Microsoft Software as we do. I work
for a government agency and we always have to provide data in a "lower"
format. What I'd like to do is automate the job so one of our guys doesn't
have to manually do it every time as he does now.

 

[Cheryl]

John,

Thanks for the suggestion. Unfortunately, it is not "compact and repair"
that I want to do. I've solved that one by checking "compact on close" so the
database stays compacted for me. I really need to save it in the file formats
that can be opened by older versions of Access. I've tried
Access.AcCommand.acCmdConvertDatabase, but you have to use SENDKEYS with it
and it is totally unreliable.

Any other thoughts?
--Cheryl

 

[Rick Brandt]

John,

Thanks for the suggestion. Unfortunately, it is not "compact and
repair" that I want to do. I've solved that one by checking "compact
on close" so the database stays compacted for me. I really need to
save it in the file formats that can be opened by older versions of
Access. I've tried Access.AcCommand.acCmdConvertDatabase, but you
have to use SENDKEYS with it and it is totally unreliable.

Any other thoughts?

What John was suggesting is that compacting via code also allows you to
change the file format at the same time.

 

[Cheryl]

Sorry, I didn't originally see that as an option. Now I have the code and I'm
getting this error:

Cannot perform this operation; features in this version are not available in
databases with older formats.

Dim sOldPath As String = "c:\temp\v2000.mdb"
Dim sNewPath As String = "c:\temp\v97.mdb"
Dim db As DAO.Database
Dim de As New DAO.DBEngineClass
db = de.OpenDatabase(sOldPath)
MessageBox.Show(db.Version)
'This shows 4.0
db.Close()
de.CompactDatabase(sOldPath, sNewPath, dbLangGeneral, dbVersion30,
dbLangGeneral)
'This is the line where I get the error.

I'm referencing DAO 3.6. If I reference DAO 3.5, I never get past the open
of the v2000 database.

 

[Cheryl]

So I've completely given up. This is the approach I finally took:

1. Manually create the databases in the various versions one time.
2. using an oledb command, delete * from destination table
3. Create a datareader and select * from sourcetable on 1st db
4. Create an insert command with each piece of data
5. insert into destination table.

This seems like a crazy way to do it, but it is working.

John & Rick, thanks for your help.

 

[Guest]

From within Access, with a database loaded,

Application.SysCmd(605, "C:\Database97.mdb")

If you are not familiar with scripting, look on your PC
for examples of .VBS files to learn how to write VBS
scripts - it is similar to Access VBA.

Even unscripted, converting between versions can be
a bit flaky - let us know how you go.

(david)

 

[Cheryl]

David,

That worked going from 2000 to 97! Now, the question of the day: how do you
go from 2003 to 2000? And finally, where do you find out more about syscmd? I
can't find much on the web or at Microsoft, it appears to be a big secret.

Well, we're half way there. Thank you so much.
-- Cheryl

 

[Guest]

These are undocumented commands. Many years ago, people found
them by looking at the code for the Access wizards (back when
Microsoft "dog fooded" Access applications), and then by
looking at the entry points in MSaccess.exe. There probably
is a command for going from 2003 to 2000, but I don't know
what it is.

You can google "Syscmd 605" to see what other people have
said about undocumented syscmds.

(david)