Programmatically convert Access database

[Ron Hinds]

I want to convert a database from Access 97 format to Access 2002/2003
format. The CompactDatabase function has an option to convert, but
(according to the help file) won't convert to greater than the A97 format.
Any suggestions?

 

[aaron.kempf]

microsoft has a new tool for automating this type of thing; i believe--
check out www.microsoft.com/office/access it should be really close to
the first page.

 

[M.L. Sco Scofield]

I think what Aaron is referring to is the "Access 2003 Conversion Toolkit"
which you can get at
http://www.microsoft.com/downloads/...76-5D89-450A-B977-980A9841111E&displaylang=en.

Actually, it is more for analyzing your databases for potential conversion
issues than actually converting them.

Also, take a look at the conversion whitepaper available at
http://support.microsoft.com/kb/237313/EN-US/. (The issue for converting to
Access 2002 and 2003 are basically the same as converting to Access 2000.)

For programmatically converting, as long as you are in Access
2000/2002/2003, there is a new constant for CompactDatabase, dbVersion40,
that the help file doesn't mention. (You can find it using the object
browser in a VBA window.)

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Paul Overway]

You might see errors if you use CompactDatabase to convert a database. See
error #2573 at

http://www.granite.ab.ca/access/corruption/symptoms.htm

There is also a good chance that you'll corrupt the database...IFRC, thats
what happened when I tried it once. I don't think there is a safe way to
programmatically convert databases.

 

[aaron.kempf]

well the safest way is to import all the objects into a new MDB right??
that kinda stuff is easily programmable in Access using VBA.

i mean seriously-- what do you want it to do; rewrite DAO into ADO?



-aaron

 

[Ron Hinds]

I'm only talking about converting the BE databases at client sites, so they
will be data-only DBs. The FE I have already converted. Based on my testing,
it seems there is a significant performance boost by converting the BE DBs
to Access 2003 format, hence my original question. I guess I should have
made that clearer ;-)

 

[Ron Hinds]

Does the dbVersion40 constant convert to A2000 or A2002/3 format? When I
manually converted my BE DBs for testing purposes it was a two-step
process - first from A97 to A2000 then to A2002/3. I saw a significant
performance improvement by converting the BE DBs to A2002/3 format.

 

[Tony Toews]

You might see errors if you use CompactDatabase to convert a database. See
error #2573 at

http://www.granite.ab.ca/access/corruption/symptoms.htm

There is also a good chance that you'll corrupt the database...IFRC, thats
what happened when I tried it once. I don't think there is a safe way to
programmatically convert databases.

FWIW I was programmatically converting a 25 Mb A97 MDB to A2000 every
day or two for a year or more. Once I had a problem.

(I was also making MDEs in both A97 and A2000 as well as copying to
the MDEs to the file share on the server so the Auto FE Updater would
copy them down. The whole process would take about four or five
minutes so I'd go have a washroom break and grab a coffee while this
was happening.)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm