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

[Danielwy]

There may be a bit of confusion due to me not being very advanced with programming and not having the best understanding of Access.



I will try to better explain the problem and give a background:

There is read/write access to both databases.

1. The problem is that in database 1, which is a front end, has all the tables linked to the back end.
2. I want to export table1 to database 2, but when i export it, it exports as a linked table. So now the back end is linked to the front end, and the front end is linked to this new table in database 2. So if a change is made to the table in database 2, that change is reflected in both the front end table and back end table since they are linked.
3. What I want is the table in database 2 to be a local table so that when a change is made to it, it stays within that database and doesnt change the values in the front and back end.

(The table in the Front and back end is a template table, so i want the template inserted into the new database and that is it)

[orange]

This is confusing.
Typically in a split database, all tables are in the BE.
The frontend contains Links to tables; and all queries , forms....

Why can't you have all your tables in the BE. What exactly are you trying to accomplish (or avoid)?

Can you describe what you are trying to do using database1 and database2. A front end is only a front end if it is linked to a BE.

[Danielwy]

Thank you both for the inputs. They will be quite helpful.

[Danielwy]

Yes all of the tables are in the back end and linked to the front end. That is what I was trying to say. But database 2 is a completely different database.

So what I should have said is that when I export a table to database 2 using the front end, then that table in database 2 is now linked to the table in the backend. I DO NOT want that. I want database 2 to take the values in the table from the backend, but not have it linked to the backend. So when the values in the database 2 table are changed, they dont change the values stored in the backend.

Sorry for the confusion

[orange]

Sorry daniel, but it isn't getting any clearer for me.

I see your set up as

FE.mdb------------------------->BE.mdb

Database2.mdb

where FE.mdb is linked to BE.mdb and BE.mdb has all the physical tables. And database2.mdb is a separate animal --NOTHING to Do with FE and BE.

have i got it so far?

If so , then I think you'd

Sign on to BE.mdb
and run a proc like this

Sub testsend()
DoCmd.CopyObject "full path to database2.mdb", "NewTablename", acTable, "ExistingTableName"
End Sub

Or another thought

Signon to Database2
and use the import wizard to import YourDesiredTable
from BE.mdb and name the Table whatever you want in Database2.mdb

[Danielwy]

Yes you are perfectly right about what I was trying to say. Does the docmd only work for objects in the current database? Or can I use it in the front end to copy an object in the back end and then paste it into database 2?

[Danielwy]

Or perhaps something like

DoCmd.TransferDatabase acExport(from backend database), "Microsoft Access", LFilename, acTable, "Template_ALL", "ALL", True

[orange]

Have you ever used the Import wizard to import data/things from one database to another?

I think this is the easiest manual method.

Let's say you opened database2,
then using the import wizard
select the database you want to import From (BE.mdb)
then select the table(s) you want to import
and follow the wizard instructions.

I think it will keep the names, but you can rename them separately.

[apr pillai]

You can do this with a simple Append Query using the IN Clause. See the example given below (Your original query, given at the begining of this thread, modified):

INSERT INTO Table2 IN 'C:\New folder\Database1.accdb'
SELECT Table1.*
FROM Table1;

OR

1. Start creating a new Query and place Table1 in the Query Design.
2. Change the Query Type to Append. A dialog box will open up.
3. Select Another Database option.
4. Use the Browse... Command Button and locate the target database and select it.
5. Now select Table2, of target database, from the drop-down box of Table Name control.
6. Click OK.
7. Save the Query and run it to append the data from current database into the target database.

You don't need to link table2 to the current database.

A Query design image, with the external file reference, is attached for info.

[Danielwy]

Perfect thanks everyone.

[finsmith]

followed this thread since I also need to append or add items from one table to another.
Append works great. And then if need be data can be fulfilled in later on. for me it was like drawing no, drawing item, and then name. I know it sounds funny but it works. Since Its a query, it will work out well for our needs.