Transfer of data

How do you either (a) transfer a newly revised table structure into an existing database, or (b) transfer existing data into a new table structure?

 I have an existing database that has tables filled with data.  It is linked to another database which has the queries, forms, reports, macros and modules.  I use this database on a daily basis, constantly updating and filling it with new data.  I want to make some changes to the tables structures, so I plan to import just the table structures to a new database and make the changes there, so as to not disturb my daily flow of work.  There will be added fields, mostly.  Some existing fields will have minor changes - like defaults and validation rules (less restrictive, so existing data will still fit in the field).  What I can't see is how to then either transfer the newly revised table structure into the existing database, or transfer the existing data into the new table structures.
Who is Participating?
JCEConnect With a Mentor Commented:
You can do either way, both has it's owns considirations.
If you chose to (a) you have to manually or by code the cahnges. In that moment nobody shall work in the database.
If you chose (b) you have to define append-querys for all your tables or even here by code like dbs.execute "INSERT ... etc"
Even in this solution you have to stop work in the database.
If you need help in the matter of code for any of the choice, tell us.
Good Luck
Here is what I would do:

Assume the database that you want to change is called a.mdb

1. Save a.mdb as b.mdb
2. Make all your changes in b.mdb
3. Clear all data from the changed tables in b.mdb
4. STOP the a.mdb application and make sure no-one starts it until you are finished with the update
5. Write an append query to append all the data in the corresponding a.mdb tables to the the new b.mdb tables
6. Copy the tables in the a.mdb databse to the a.mdb database. They will be named Copy of TableX, Copy of TableY, etc. so you can go back if something gets messed up
7. Export the new b.mdb tables to the a.mdb database; this will replace the original tables
8. Restart the application

You will have to shut down your application briefly to do the update, as JCE mentioned.

Hi jwilk8284.
1. Copy your a.mdb back-end database to b.mdb.
2. Make all changes (add new fields, change existing fields) in this new back-end b.mdb without deleting data.
3. Open your front-end database and relink all tables from a.mdb to b.mdb using "Link Manager". Test your application with new back-end DB.
4. If something wrong your can return back to old structure (front-end <-> a.mdb back-end). Just relink back.

Good luck,
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Problem is the continuation of adding data and the change of tables.
The best way is to copy a.mbd to b.mdb and after finishing the changes to b.mdb to empty the tables and use import queries to fill the tables, thus having them checked (perhaps even converted) against the new defined rules/relations.
The b.mdb is then the newest to work with and a.mdb can be saved as old-version!

In addition to my previous comment.
If you  make changes often your should think about using replication, i.e. making "master" level db and replicas. After any changes replicas sinchronized.
This is standard MS approach and you can find info about replication in online help.
When I upgraded a previously-distributed app, and had to make table mods, what i did was this.  

I opened the back-end, (data db), from the db with the forms and such, (front-end db).

     dim d as database
     Set d = DBEngine(0).OpenDatabase("<pathToBackEnd><BackEndDb.mdb>"

Then i created an anonymous querydef, which i could use to manipulate the objects in the back-end db (BEDB).

     Dim q  As QueryDef
     Set q = d.CreateQueryDef("")

(Creating an anonymous queryDef prevents it from being added to the BEDBs QueryDefs collection, but it is still useful for running SQL in the BEDB.)  Then i used this query to run SQL in the BEDB.

     q.sql = _
            "ALTER TABLE <tbl> " & _
            "ADD COLUMN holdtxt TEXT"

     q.SQL = _
            "UPDATE <tbl> " & _
            "SET holdtxt = txt"

This transfers the data into a holding field.  Then delete the old field.

     q.SQL = _
            "ALTER TABLE <tbl> " & _
            "DROP COLUMN txt"

Now you can create a new txt field

     Dim t as Tabledef
     Set t = d.Tabledefs(<tbl>)
     Dim f as field

     Set field = T.CreateField("txt", dbText, 150)
     f.DefaultValue = "DefaultTxt"
     f.AllowZeroLength = False
     t.fields.append f

Now you can use the same code that was used to move the data to the holding field to move it to the new field.  Then you can use the DROP COLUMN code above to delete the holding field.

Just one more way to do this...

Hello again.
I found it. In the past I solved a problem like yours in this way.
During my work in a copy of the database I put all my changes in a table. The table holds the name of the table i changed,the new fieldname, the defenitions of the field and the lenght if it was a textfield.
When it was time to implement the changes i did something like Brian said but I get the new definitions from my "change-table".
The work in the office stopped in only 5 minutes!!!
If you want to see this code, tell me and I show you.
jwilk8284Author Commented:
JCE - thank you for responding first, and for the idea on the append queries, also will you please post the code to which you were referring?

repstein - good input, it is clear and understandable.

Dedushka - I am considering replication but am hesitating because the autonumbering then becomes random.  I would like to see the numbers increment in a predictable sequence.

BrianWren - thank you for the detailed response.

Hello again.
It's a while since I wrote this code and I think som of can be done better, but it works.

'The code exist in a form and I start it by pressing a button!
Private Sub STARTA_Click()
Dim ws As Workspace, db As Database, tblDef As TableDef, MinFil As String, Fld As Field, strFld As String
Dim rsDef As Recordset, Matrix(10, 4) As String, RecNr As Integer, I As Integer
Dim Title As String, Msg As String, Svar As Integer
On Error GoTo Err_Handler
GoTo Start
   MsgBox Err.Description, 48, "Felmeddelande nr " & Err.Number
   Resume Next
   Exit Sub

Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb

Set rsDef = db.OpenRecordset("TabellDef")
RecNr = 1

'I don't remember why I did this with an array.
'But it works! Tabell, Namn, Typ, Längd is the field you need in the table!
Do Until rsDef.EOF
   Matrix(RecNr, 1) = rsDef![Tabell]   'Tablename ex. tblKlass
   Matrix(RecNr, 2) = rsDef![Namn]     'Fieldname ex. KlassID
   Matrix(RecNr, 3) = rsDef![Typ]      'Type of field ex dbText, dbLong etc
   Matrix(RecNr, 4) = rsDef![Längd]    'Lenght of the field, if text otherwize 0
Debug.Print Matrix(RecNr, 1), Matrix(RecNr, 2), Matrix(RecNr, 3), Matrix(RecNr, 4)
RecNr = RecNr + 1


MinFil = Me!Path 'Or for exampel C:\Your.mdb
Set db = ws.OpenDatabase(MinFil)
For I = 1 To RecNr - 1
   Set tblDef = db.TableDefs(Matrix(I, 1))
   Set Fld = tblDef.CreateField(Matrix(I, 2))
'This is because dbText for example is a constant (value 10)
'You need a case for every type, press F1 on one of them and you can see all types!
   Select Case Matrix(I, 3)
      Case "dbText"
         Fld.Type = dbText
      Case "dbDouble"
         Fld.Type = dbDouble
      Case "dbLong"
         Fld.Type = dbLong
   End Select
   If Matrix(I, 4) <> 0 Then
      Fld.Size = Matrix(I, 4)
   End If
   tblDef.Fields.Append Fld
Set db = Nothing
   Title = "Meddelande"
   Msg = "Körningen är klar!"
   Svar = MsgBox(Msg, 16, Title)

End Sub

Hope you find it useful and if you have questions, let me know!
Good Luck
Hello jwilk8284
What's happen could you use the code or have you found another solution.
jwilk8284Author Commented:

I was away for a few days.  I have not tried the code yet but will go ahead and close out this question so you will have the points.  

jwilk8284Author Commented:
Thank you for responding.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.