Copy table to different database


Jake F

I need to move a table to a history database each day and I used the
docmd.copyobject but it's creating a linked table in the history db. I
didn't find anything in the command that was missing or specified a linked
table, but maybe you all can see something I missed or know of a better way.
I need it to copy/paste the table so i can clear the old table for the next

DoCmd.CopyObject DestinationDB, "tblAttendanceHistory_" & Format(Date,
"yymmdd"), acTable, "tblSupv_EE"

Jake F

It isn't giving me error, just creating a linked table in the history
database instead of a separate table. I think it was because the original
table was linked to the back end but I figured a work around for it. Thanks

Dale Fye

Not sure why you are archiving data daily, or are you really just creating a

You could use the Transferdatabase method:

docmd.TransferDatabase acExport, "Microsoft Access", _
"C:\SomeFolder\SomeOtherDB.mdb", _
acTable, "YourTableName",

Or you could create a MakeTable query and designate the recipient History db:

SELECT YourTableName.*
INTO YourTableName IN 'C:\SomeFolder\SomeOtherDB.mdb'
FROM YourTableName

Or, you could append the data to an existing table in that external db using
an append query:

INSERT INTO YourTableName (Field1, Field2, ....)
IN 'C:\SomeFolder\SomeOtherDB.mdb'
SELECT Field1, Field2, ...
FROM YourTableName
WHERE [DateModified] = #9/21/2009#

Jeff Boyce

Like Dale, I'm curious why you feel the need to 'move a table to a history
database', let alone "daily"...

What business need are you satisfying by doing this?

Are you aware that moving data from one place to another is usually
unnecessary in a well-normalized relational database (but very common in a
spreadsheet-based application)?

If you'll provide more specific description about how your data is
structured, and what business need you are trying to solve, folks here may
be able to offer alternative approaches.


Jeff Boyce
Microsoft Office/Access MVP

Jake F

I need to keep the daily history of attendance of 1000 people. So having a
different table for each of those and then having users make changes to the
days table I figured would slow it down if I didn't kick the past tables to a
different database.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question