Import data

[RevJeff]

Hello all,

I am trying to append data from a table in one database to a table in another database using vba.

Can someone point me in the right direction.

Thanks!

 

[CJ_London]

bit more info required - are both tables linked into the same db?
why do you want to use vba (it can be done in sql)

 

[AC5FF]

I agree with CJ. Appending data - even between db's - with SQL is as simple as it can be.

 

[RevJeff]

No, the tables are linked at all. Just two different tables. I just want to be able to append the data from one to the other with the click of a button. SQL is fine. I'm just not sure how.

 

[AC5FF]

Here is one I just wrote yesterday. Takes info from a tmpIMPORT table to my OutFileLog table. I qualify what I want to import (WHERE statement) and update the reportdate field to today.

INSERT INTO OutFileLog ( AccountNumber, Runtime, [Avg], ReportDate )
SELECT tmpImport.AccountNumber, tmpImport.Runtime, tmpImport.Avg, Date() AS ReportDate
FROM tmpImport
WHERE (((tmpImport.Runtime)>0) AND ((tmpImport.Avg)>0));

Easiest way I've found to do this, is just write a query in Design View to give you the data you want. Then change the query type to APPEND QUERY. At that point Access will ask for the table to append to and will show in design view what fields to append each

 

[RevJeff]

Will that work for tables in two different databases?

 

[AC5FF]

Kind of but I think it is written a bit differently.
I normally will just link the table, then I can use it without actually storing it twice.

 

[RevJeff]

Yeah, I would normally do that as well, but it is a front end/back end setup and the front end people are out in the field and it runs real slow, so I need them to update before heading out.

 

[AC5FF]

I could swear I've read how to do that, but never personally done so.
If I can find the site I read it on I'll post a link for you.

 

[JHB]

..
I am trying to append data from a table in one database to a table in another database using vba.

Something like below:

    CurrentDb.Execute ("INSERT INTO YourExternTableName ( FieldName1, FieldName2, FieldName.. ) IN 'YourDriveAndPath\YourDatabase.mdb or accdb' " _
  & "SELECT FieldName1, FieldName2, FieldName.. FROM YourTableName")