Update Table

[kjharve]

Hi,

I am running two databases. The first database contains a table which I would like to feed two different tables in the second database (the structures are not the same.)

In order to extract the data from the feed table I have linked to it. Then, I have used ADO to query the linked table and stored the data I need in a recordset.

Now, the tables I am looking to write to will have this update performed monthly. Some records will stay the same, some records will change, some records will be deleted and new records will be added. So, the best way (I think) to make sure the data is correct is to clear the data from the destination table and write the whole table again from the recordset.

I am creating a temporary table in case the upload of data goes wrong and then deleting all records from the destination table.

The recordset will be exactly how I want it to appear in the table. What I want to know is how can I easily dump all the data from the recordset into the destination table?

Am I asking the right question here or is my approach wrong?

Any suggestions folks?

 

[mdmilner]

Actually, yes this might be one of the slowest approaches to doing this. I'm guessing you are going to use the recordset to .Append records one by one.

What you need are two different append queries. The concept you're missing is, you can use append queries to append data in remote databases, all you need is the syntax to reference the path to the destination database.

INSERT INTO tblDest (fld1, fld2, fld3)
SELECT afld1, afld2, afld3 FROM tblSource

Just adapt the standard syntax above by putting a fully qualified path ahead of the tablename followed by a dot.

ie.

[c:\folder1\folder2\database.mdb].tableName

This works in either direction - all you need is the correct path.
My recommendation is when using network shares to use the \\sharename\folder1\folder1 syntax.

Mike

 

[kjharve]

I actually solved it in the way you suggest before you posted your reply. However, I was still using the linked table - I didn't realise I could reference a table from an external database in a query.

When I read your post I was actually looking for a way to create and remove linked tables using code (and wasn't liking the solutions.) So you have kind of answered my question for me!

Thanks for that!

 

[kjharve]

Erm, hi!

Any idea how I enter the password to the source database using the query?

 

[kjharve]

Don't worry, done it!

[c:\folder1\folder2\database.mdb; pwd=<password>].tableName