Copy table to different database

  • Thread starter Thread starter Jake F
  • Start date Start date
J

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

DoCmd.CopyObject DestinationDB, "tblAttendanceHistory_" & Format(Date,
"yymmdd"), acTable, "tblSupv_EE"
 
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
though.
 
Not sure why you are archiving data daily, or are you really just creating a
backup?

You could use the Transferdatabase method:

docmd.TransferDatabase acExport, "Microsoft Access", _
"C:\SomeFolder\SomeOtherDB.mdb", _
acTable, "YourTableName",
"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#
 
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.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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.
 
Back
Top