Importing data from an old databse into a new one

[Mus' via AccessMonster.com]

Hi

I have an old database with approx. 50 tables of data. I also have a newer
version which is based upon the original but has a number of additional
fields in the various tables. In theory the old data will sit directly into
the new one as the original fields have not been altered. What is the best
way of doing this?

I have tried exporting the data into excel and then importing the data into
the new database but I get a couple of lost/deleted error messages (without
expecting everyfield in every table, it appears that data hasn't actually
been lost?)

Any suggestions (preferably not creating 50 append queries as I may need to
do this for a number of copies of this database) ?

 

[Joe Fallon]

Well I was going to suggest writing 50 append queries....

Actually - I still am.

Put the 50 queries in code if you want.
Write a module to call the 50 queries in the right sequence.
Test it.

Then include the module as part of the new mdb you send out.
Users import their "old data" by running your module.

Done.

 

[Brendan Reynolds]

You could at least partially automate the creation of the append queries.
Here's an example. This is based on the assumption that the database
contains linked tables with the same name as the old tables with a "1"
appended (which is what the linked table manager does when you create a link
to a table with the same name as an existing table). It also assumes that
there are no other tables in the database with names ending in "1". This is
quick-and-dirty example code. While writing it, it became apparent that it
would be possible to build the INSERT INTO and SELECT clauses in tandem,
during a single pass through the Fields collection, which would be more
efficient. However, I expect even the less efficient two-pass method below
will probably zip through most databases in milliseconds, so I leave it to
you to decide if it is worth the time and effort of optimising it.

Public Sub CreateAppendQueries()

Dim db As DAO.Database
Dim tdfSource As DAO.TableDef
Dim tdfTarget As DAO.TableDef
Dim strSQL As String
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef

Set db = CurrentDb
For Each tdfSource In db.TableDefs
If Right$(tdfSource.Name, 1) = "1" Then
Set tdfTarget = db.TableDefs(Left$(tdfSource.Name,
Len(tdfSource.Name) - 1))
strSQL = "INSERT INTO " & tdfTarget.Name & " ("
For Each fld In tdfTarget.Fields
strSQL = strSQL & fld.Name & ", "
Next fld
strSQL = Left$(strSQL, Len(strSQL) - 2)
strSQL = strSQL & ")" & Chr$(13) & Chr$(10) & "SELECT "
For Each fld In tdfTarget.Fields
strSQL = strSQL & fld.Name & ", "
Next fld
strSQL = Left$(strSQL, Len(strSQL) - 2)
strSQL = strSQL & Chr$(13) & Chr$(10) & "FROM " & tdfSource.Name
Set qdf = New DAO.QueryDef
With qdf
.Name = "qapp" & tdfTarget.Name
.SQL = strSQL
End With
db.QueryDefs.Append qdf
End If
Next tdfSource
Application.RefreshDatabaseWindow

End Sub

 

[Mus' via AccessMonster.com]

Thanks folks, I'll start checking these options out.

This is truly a great community. )