Update a MDB file

[Gigatech]

Hi, I have a MDB installed on my client computer. I added fields and made other structural changes on my original MDB. What is the best way to apply those changes to my client MDB without loss data?

 

[zevw]

Hi!

This the module I use when I make updates on tables

I create a new directory where I copy my newly modified tables i.e. \DBNEW

I open my newly created Database and paste the module.

1. I first run step 1 in my module and comment out the other steps.

2. I copy the same module to my old database and run first step 2 and comment out Step 1 & Step 3

3. After Step 2 is done I run step 3 commenting out Step 1 & Step 2.

Function UpdtTbls()
Dim AllTables As TableDef, tblname As String

    For Each AllTables In CurrentDb.TableDefs
        'Get the Table name
        tblname = AllTables.Name
        If Mid(tblname, 1, 4) <> "Msys" Then
            'Step 1: Delete all table from New Database after copied, Open all tables
            DoCmd.DeleteObject acTable, tblname
            'Step 2: Transfer all Table structures to new Database
            'DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\DBNew\[Name of your database].mdb", acTable, tblname, tblname, True
            'Step 3: Insert data into new table
            'DoCmd.SetWarnings False
                'DoCmd.RunSQL "INSERT INTO " & tblname & " IN 'C:\DBNew\[Name of your database].mdb' SELECT " & tblname & ".* FROM " & tblname & " IN 'C:\[Directory of the original data withe old database structure]\[Name of your old database].mdb';"
            'DoCmd.SetWarnings True
        End If
    Next AllTables

End Function

I hope this helps:

William

 

[Gigatech]

Thanks zevw. I have autoincremental fields, does the "Insert into ... select * ..." preserve the original values or when the records are inserted they obtain new values for the autoincremental fields?

Best regards.

 

[zevw]

Yes they do, and just to be on the safe side, Run Compact and repair before using the DB

 

[KornGeek]

Gigatech,

There have been several topics discussing the drawbacks of using autonumber fields. I strongly recommend doing away with these. MichaelRed wrote an excellent FAQ describing a better automatic numbering system. It's more work, but saves you a lot of potential trouble down the line.

In one database project I worked on, the customer had autonumber fields, and didn't want to change them out. I spend easily double the amount of time it would have taken to change them dealing with database corruption issues caused by them.

 

[zevw]

I agree with KornGeek that it causes major problems if you will update the DB while it is being used by other users. However my experience is, if you have a clean database without any records in them, and insert the records from you old database to the new one your next autonumber will be the one number higher then the last record. (which is the highest autonumber - since autonumbers are indexed)

Again when you do the migration, make sure no one should be using the DB only you, and then run a repair.

This is my experience