Insert data from one table into another table in a different database

[Danielwy]

How can I grab the records from the current database in Table1 and insert them into table2 in a different database.

So something like:

INSERT INTO Database2.table2.* SELECT database1.table1.*

Any help will be appreciated

[alansidman]

You could use the import wizard. Depending on which version of Access you are using to determine a starting point. In 2003 and earlier. File Get External Data. In 2007, its on the External Data Tab of the Ribbon.

[Danielwy]

Well for one I want to do it through code, which I know is possible. But the problem is that the table1 is a linked table and table2 cant be a linked table. It needs to be a new table of its own so it values can change without table1's values changing. Unless it is possible to export a table and somehow break the link one it is in the new database?

[Danielwy]

once*, not one

[Stingaway]

You can link to separate databases through the same front end. Select the data from table 1, append/insert it to table 2 - just as you would if they were in the same database.

When you say table 2 can't be a linked table - is there a system limitation or a business process / security reason why it can't be a linked table?

[Danielwy]

It is a business process why they cant be linked.

[Danielwy]

also how would the SQL statement go to select from current database and append to a seperate database, I feel you would have to reference the database that it is getting inserted into

[Stingaway]

Well if you can't link the database, you may be out of luck attempting to do it 'through code', as you still have to connect (i.e. link) to the database through the code in order to insert the records...

[Stingaway]

also how would the SQL statement go to select from current database and append to a seperate database, I feel you would have to reference the database that it is getting inserted into

When you link a database through ODBC, it is generally given a name like dbo_Customer or dbo_Employee.

Behind the scenes, dbo_Customer links to database1 through the ODBC link. dbo_Employee links to database2 through the ODBC link.

In your query, you would do an INSERT INTO dbo_Employee and your select statement would be a Select * from dbo_Customer

I do this all the time with multiple database linked through one Access front end... it makes life VERY manageable data wise...

[Danielwy]

Thank you for the info. I have been thinking of away around this problem and I may have found a solution, let me know if you think it is a bad way to do it.

All done through code:
export the table to the new database, copy and paste this table so that it creates a local table and then delete the original table that was exported

[Stingaway]

Let me see if I get this straight.

1.) In Database 1, you do a file export, or a macro to export the table to Database 2.
2.) You open Database 2, copy the table and paste it, then delete the original table here or
3.) You go back into Database 1 and delete the table that you just exported.

Is that correct?

[Danielwy]

Step 2 is correct. It is deleted in database 2.

[Danielwy]

I need to keep the table in database 1 so that it can be distributed to other databases. Database 1 is acting as a front end to distribute the table to many other databases.

[Stingaway]

So you have a table in database 2 that you want to replace with the newly imported table.

Based on your business processes it sounds as if you are already automating as much as possible. The only recommendations I would make (both in terms of meddling and in terms of automation):

1.) If you have access rights to both databases, it makes one curious as to why your business process disallows access rights at least in read only form to ODBC into Database 1.

2.) If you are copying the table into a Database 2 and then replacing an entire table in database 2, you might consider a macro to do the steps you are doing manually anyway. The macro would 1.) Rename the original table 2.) Rename the new table with the original name of the original table and then 3.) Delete the renamed original table.

[orange]

You can use this technique from Database1

expression.CopyObject(DestinationDatabase, NewName, SourceObjectType, SourceObjectName)


Sub testsend()
DoCmd.CopyObject "D:\A2K\Anotherdb.mdb", "Names", acTable, "TestNames"
End Sub


Good luck with your project