Is it possible upgrade old MS access database with script?

Question

I have this old MS Acceess db full of useful data, but newer version of software requires newer version of db (newer as differences in db design and some fixes). With Sql Server or there is no trouble at all, I would write a script a I would have a upgraded db. With Access I have trouble when I try to write script myself and when I use tools for generating scripts.

Please help.

Answer 1

Why don't you connect to your ole access database with another tool to extract the data? Excel can do the job, and I guess many other (more or less) free tools can transfer all this data to your favorite server.

Answer 2

Compare'Em This utility will create the necessary VBA (or DDL if you pay him the $10) to generate the differences between two backend MDBs. While it has it's quirks it does a reasonably good job. I use it all the time for my own "shrink wrap" applications.

I keep a version number in a specific field in a table and execute the appropriate code when they first link to the BE MDB to update the BE MDB to the new file structure. Each version update is in it's own subroutine. I use the following to update the version number to the next version number.

' Update VersionNumberData on table
strSQL = "UPDATE zDataVersionNumberApp IN '" & strDatabasePathandName & "' SET zDataVersionNumber  = " & _
    CurrentVersionNumber
CurrentDb.Execute strSQL, dbFailOnError

Of course after running the code part way through to create the new tables, fields, indexes and relationships I then run various update/insert queries to clean up the data. Then continue running the generated code to delete the old fields/tables. The syntax for the update/insert queries using the IN clause is a bit complex so I"m including an example. This example involves moving a typed in service techs name to another table so the system can then use a combo box for the service tech, assign the tech hourly rates and so forth.

' Append records to ServiceRecords table
strSQL = "INSERT INTO ServiceTech ( stServiceTech ) " & _
    " IN '" & strDatabasePathandName & "' " & _
    "SELECT srServicePerson FROM ServiceRecords " & _
    " IN '" & strDatabasePathandName & "' " & _
    "GROUP BY srServicePerson " & _
    "HAVING (srServicePerson Is Not Null);"
CurrentDb.Execute strSQL, dbFailOnError