Access 2007 SCM over multiple databases

Question

I'm looking for a way to implement SCM over multiple Access 07 databases. We just need source control for the forms/code.

Essentially we have 100+ databases that are structured the same, use the same code/modules, but each contains data for just one client. When we make a code change we currently have to manually go through each file to make the change.

Has anyone implemented source control for something similar(God help you if so) or have any ideas?

PS - I realize there is lots of DailyWTFery in here, this is a legacy product I've been assigned to do some emergency maintenance on before we rewrite to .NET/MSSQL, but I think there's enough work to warrant putting this in place if it's possible.

Answer 1

You can find out more how to do SCM in this question; it does involve exporting and importing the code and forms using some undocumented (or poorly documented) commands. I also recall issues with checksums or version numbers happening when you used that method.

However, you could solve a lot of this problem by separating the the data and the application sides of the DB into separate files and then adding table links to the data DB from the application DB. Then you would have just one Application DB, and oodles of Client data DBs.

Switching to a different client is as simple as re-linking to the other DB.

You can do that manually or code it with a structure something like this:

Dim myTable As TableDef
Dim db As Database
dim strDBPath as string

strDBPath = ";DATABASE=C:\workbench\MyNewDB.mdb"

Set db = CurrentDb
db.TableDefs.Refresh
For Each myTable In db.TableDefs
    If Len(myTable.Connect) > 0 Then
        myTable.Connect = strDBPath 
        myTable.RefreshLink
    End If
Next

'' Note: This whips through every table in your database and re-links it to the new
''       DB. ODBC links will have a different value for strDBPath - I'll leave that as
''       and exercise for the reader