Automate "Get External Data" (1 Viewer)

[Sum Guy]

I have been asked to create an Access database at work for a confidential function. I'll call it DB1

This database will be placed on a server with the necessary security.

All of the necessary data is already entered each day into another Access database which is on the same server. I''ll call this one DB2

What I need to do is inport one table from DB2 into DB1 each day, then run an Appnd Query into my DB2 table, then delete the imported table.

I can do all of this manually, but I won't be there every day (vacation, sick etc.) As a result I require an automated procedure to do this.

I really have no idea where to start. All suggestions to bring this about will be greatly appreciated.

 

[pbaldy]

If the data already exists in the other db, why not just link to that table? Why copy the data every day?

 

[Sum Guy]

The data in DB1 is unique to each day. It's stored in it's own database which has a default name which is that days date.

Example, when I go to work tomorrow, I have to import the data in a table in a database named 2008.01.08
On Friday, the data to be imported will be in a table in a database named 2008.01.09 etc.

 

[pbaldy]

Bleh. Well, you could use

DoCmd.TransferDatabase

to import the table. You can build a string using the Format and Date functions to get the correct file. I'd import it so it always had the same name in the destination db, so I could just fire off a saved append query when the import was done.

Once you get the whole thing automated, make it so the process starts when you open the db, and the last thing that happens is the db closes itself. Then start the db from Scheduled Tasks as appropriate.

 

[Sum Guy]

Bleh. Well, you could use

DoCmd.TransferDatabase

to import the table. You can build a string using the Format and Date functions to get the correct file. I'd import it so it always had the same name in the destination db, so I could just fire off a saved append query when the import was done.

Once you get the whole thing automated, make it so the process starts when you open the db, and the last thing that happens is the db closes itself. Then start the db from Scheduled Tasks as appropriate.

Thanks Paul, I will give it a try.
Yes, it's kind of a back assed way of doing things.