Transfering Records to another database in access!

[DracusWolf]

Hey,

I'm new to Access and VB and have come to a wall.
I have a Database with three tables, each with identical columns. I need to transfer records once they become out-of-date to the appropriate table. It would save quite a bit of time of it could be done each time the database is run.


Is there a way around this as i am out of ideas!!!!

Thanks!

 

[Ashank4vba]

You could use a query to select out-of-date records and then
use functions like transferdatabase to export the query to the other database.

DoCmd.TransferDatabase acExport, , databasename, acQuery, source, destination, ,

 

[DracusWolf]

Thanks a million.

That works fine, with only one thing.

How do u get it to run one a month??

Is it required to write a VB program to run the macro??

Again,
Thanks for your help!

 

[alvechurchdata]

You could use a query to select out-of-date records and then use functions like transferdatabase to export the query to the other database.

I'd suggest a pair of queries. An Append query to select and copy the old records across and a Delete query to delete the old records.

How do u get it to run one a month??

Does it have to be once a month? If you put the code in your startup form then it would transfer the out of date records each time you started up. You'd be trading a small delay when you copy a few records at each startup for a much longer delay once a month.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[drkhelmt]

As for rnning once a month, if you have a form (i.e. a main menu) that opens when the database is opened, you could use the onOpen event to trigger a function that would run, then test the day with an If statement and run the sql automatically.

dim sqlst as string
If day = 1 Then 'tests if it is the first of the month
   sqlst = "INSERT INTO ..." 'move the records
   DoCmd.RunSQL (sqlst)
   sqlst = "DELETE ..." 'delete the records from the table
   DoCmd.RunSQL (sqlst)
End If

Hope this helps, good luck!

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?