Transfer of data

Posted on 2000-04-30
Last Modified: 2008-03-10
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.
Question by:jwilk8284
  • 4
  • 3
  • 2
  • +3

Accepted Solution

JCE earned 200 total points
ID: 2763598
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

Expert Comment

ID: 2763667
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.


Expert Comment

ID: 2763760
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,
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 54

Expert Comment

ID: 2763783
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!


Expert Comment

ID: 2763787
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.

Expert Comment

ID: 2764394
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...


Expert Comment

ID: 2764527
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.

Author Comment

ID: 2773018
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.


Expert Comment

ID: 2775081
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

Expert Comment

ID: 2786549
Hello jwilk8284
What's happen could you use the code or have you found another solution.

Author Comment

ID: 2788171

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.  


Author Comment

ID: 2788175
Thank you for responding.

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question