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

x
?
Solved

Transfer of data

Posted on 2000-04-30
12
Medium Priority
?
308 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
[X]
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
12 Comments
 
LVL 1

Accepted Solution

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

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

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