Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Transfer of data

Posted on 2000-04-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3

Accepted Solution

JCE earned 800 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,
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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