Exporting Data to Another MS Access Database

[Kelvin Leong]

Hi,

I have 2 databases of the same structure, namely DB1 and DB2. DB1 is the
database that is frequently used and has the latest information, while DB2 is
a backup database, where old data from DB1 is transferred from. Once old data
from DB1 is transferred to DB2, the old data from DB1 will be deleted.

I tried using a macro to perform the exporting of the old data to DB2 and
deleting it from DB1. However, I can't seem to get it to work.

Is there a better strategy to this or is macro the best way for a beginner?

 

[strive4peace]

Hi Kelvin,

You do not need a macro

Open DB2

link to the tables in DB1

from the menu in the database window --> File, Get External data, Link
Tables...

navigate to DB1

select all the tables in DB1 that have data you wish to transfer and
click OK

since the tables probably have the same name, the linked tables will
have a '1' on the end (and also an arrow before them). Now both
versions of tables will be available in your working database and you
can use Append Queries to transfer the data

for better understanding, download and read this:

Access Basics
http://allenbrowne.com/tips.html
Tips for Casual Users
Access Basics: free tutorial - Word document by Crystal (Access MVP)



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

moving related records
---


Hi Kelvin,

as a sidenote,

when you append, you will want to first append the main records

in your original database, make a new field in the main table:

NewID, long integer, defaultValue = null
WHERE NewID is the name of the real automber ID field in the working
database if there is not a conflict, otherwise leave at NewID

and in the real table, if you have an autonumberID in the old table,
make a field to hold it:

OrigID, long integer, defaultValue = null

after you append the old records to the working table, use Update
queries to fill in NewID and OrigID, if applicable

do this each time you create records in the working database for a table
where there will be related records

Also, since you will modify structure, you will need to open the old
database to do this since you cannot change structure in linked tables

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Kelvin Leong]

Hi Strive4Peace,

Thanks for the advise. I'll try and see whether I can get it to work. I'll
let you know the outcome. Again, thanks.

 

[Kelvin Leong]

Hi Joseph,

Thanks for the information on the access limitations. At the moment, my
database reaches to 150 MB. I guess there is still room, but I guess you're
kinda right, it is some sort of an archive database.

 

[strive4peace]

Hi Kelvin,

you're welcome hope the big conversion goes smoothly!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*