Solved

Transfer of data

Posted on 2000-04-30
12
300 Views
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.
0
Comment
Question by:jwilk8284
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 1

Accepted Solution

by:
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
0
 
LVL 2

Expert Comment

by:repstein
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.

Richard
0
 
LVL 7

Expert Comment

by:Dedushka
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,
Dedushka
0
 
LVL 54

Expert Comment

by:nico5038
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!

Nico
0
 
LVL 7

Expert Comment

by:Dedushka
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.
Dedushka
0
 
LVL 9

Expert Comment

by:BrianWren
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.Execute

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

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

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

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...

Brian
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:JCE
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.
JCE
0
 

Author Comment

by:jwilk8284
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.

0
 
LVL 1

Expert Comment

by:JCE
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
Err_Handler:
   MsgBox Err.Description, 48, "Felmeddelande nr " & Err.Number
   Resume Next
   Exit Sub
Start:

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!
rsDef.MoveFirst
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
rsDef.MoveNext
Loop

rsDef.Close

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
Next
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
JCE
0
 
LVL 1

Expert Comment

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

Author Comment

by:jwilk8284
ID: 2788171
JCE,

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.  

0
 

Author Comment

by:jwilk8284
ID: 2788175
Thank you for responding.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now