Exporting Data to Another MS Access Database

[Guest]

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
*

 

[Joseph Meehan]

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?

May I make a suggestion? There may be a better way of doing what you
want.

I am guessing that DB2 is not really a "backup database" but based on
your comments "Once old data from DB1 is transferred to DB2, the old data
from DB1 will be deleted." but rather a archive file.

You did not indicate how much data you are storing, but I suggest than
an archive and backup routine might be better than what you are now doing.

Current versions of Access (and some older versions) will hold up to 2
gigabyte of data. That usually translates into millions of records.

I suggest using one database with all the data, old and new as your
working database. Add one field called archive and make it a yes/no field
default value no. Then when you want to archive the data, simply change
that field to yes. Use a query field to filter out all the yes data and
base all your existing queries, forms reports etc. on that query. You could
filter each form report etc. if you would prefer. You could then even add
some reports or queries to view only archive data or all data as you wish.

Then on a regular schedule copy that working database to a new location
making it your backup database.

That will give you additional security in the event of that event that
damages one of the databases and a more convenient access all your data.

 

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

 

[Guest]

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.

 

[Guest]

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
*