Database Conversion (Access 97 > Access 2000)

Topic

New Reply

We’ve got 2500 access databases on our network that we need to convert to Access 2000.

We’re going to write a program that automatically converts all of these databases, is there any way to convert without using the command line switch? I don’t wan’t to use the switch if possible as if the database is already in 2000 format you will get a pop up message informing you.

Alternatively, is there an extra /s switch to specify silent running…?

Thanks

Reply | Quote

Replies

Unfortunately, I’m afraid you have some challenges ahead of you. I don’t believe there is any way of using the command line to make the process silent, so the only option I know of would be to test the version of the database after you’ve opened it, perhaps in VB rather than VBA. Windows Script Host might be of some value, but it depends on how well your 2500 databases are organized and stored. One solution we’ve used is to simply train the users to do the conversion. But if you have any VBA or old Access Basic lying around, you will have to intervene to get the database to work – conversion doesn’t deal with all the issuse, just the common ones. Just out of curiosity, how did you end up with 2500 databases – does each employee have one of their own or something?

Reply | Quote

Recommend try the Access Application object ConvertAccessProject method. Example, using Replace function to append “2K” to name of the 97 db:

Public Sub TestConvert97To2K(ByRef strOldFileName As String)

Dim strNewFileName As String
strNewFileName = Replace(strOldFileName, “.mdb”, “2K.mdb”, , , vbTextCompare)
‘ strOldFileName = “C:AccessPassword.mdb”

Application.ConvertAccessProject _
SourceFilename:=strOldFileName, _
DestinationFilename:=strNewFileName, _
DestinationFileFormat:=acFileFormatAccess2000

‘ Test msg:
MsgBox “Access 97 database ” & strOldFileName & ” has been converted to ” & _
“Access 2000 format. New file: ” & strNewFileName, vbInformation, “CONVERTED”

End Sub

This works w/o problem in Acc 2002. Not sure when this method was introduced but may be available in A2K?. The acFileFormat enum defines which versions of Access file may be converted to, as noted in “Help” file:

DestinationFileFormat – Required – AcFileFormat. The Access version of the converted file.

AcFileFormat can be one of these AcFileFormat constants:

acFileFormatAccess2
acFileFormatAccess2000
acFileFormatAccess2002
acFileFormatAccess95
acFileFormatAccess97

You’d want to add error handling, an error will occur if destination file already exists. As noted by Wendell, if these db’s have Access objects (forms, reports, modules, etc) , not just tables, you have your work cut out for you verifying that no conversion errors occurred….

HTH

Reply | Quote

ConvertAccessProject came in with A2002.

Reply | Quote

Thanks people, the ConvertAccessProject would’ve been perfect but I’ll just have to stick with the command line for now.

Wendell – We’re quite a large company (1000+ employees) dealing with lots of sales information. We converted to the MS Office suite from Lotus & as soon as Access was available, people were using databases rather than Lotus 123 for all their data projects! It’s a bit of a mess to be honest….they take up so much disk space & we really need to review all of them. That’s a tedious job though & one I may have to delegate….!!!

Reply | Quote

I don’t know if you’ve resolved this issue, by chance (following link in a recent thread on unrelated subject) came across this link you may want to check out:

Trigeminal Software, Inc. (TSI) – Free Utilities – TSI Conversion

Among other cool stuff, this site has a free utility called TSI Conversion that you can use to convert an Access db to current version. The download zip file contains 3 versions of the utility (an .MDE file) numbered 08, 09, 10 for Access 97, 2000, or 2002 respectively. In a quick test, the convert09.mde utility successfully converted an A97 db to A2K format. The .MDE exposes one public function, ConvertDatabase. The syntax is simple. Example:

ConvertDatabase “C:AccessPassword97.mdb”, “C:AccessPassword2000.mdb”

Note there was NO annoying dialog box informing you file was converted – the db was converted w/o fanfare. Recommend check this out to see if you can use it for your thankless conversion project.

HTH

Reply | Quote

Was toying with my own conversion program but this looks like exactly what I need. I’ll test this today & let you know how I get on, many thanks for finding….

Reply | Quote

Have tried with a few databases and seems very good…

My only moan is that it only returns TRUE or FALSE, in the case of FALSE I would want to know why it failed (security etc). But it’s going to save time whatever so it’s all good…!

Thanks

Reply | Quote