Copy current record to another table 1

[legos]

Hi everyone, this is probably a simple problem, but i can't figure out how to slove it.
I have a VB module that creates a new database with a table that has the same structure as my current database. I now want to copy the current record from my current database into the new database and new table.
Here is the code i used to make the new database and table

Set rst1 = db.OpenRecordset("Century_Inventory")
rst1.MoveFirst
Set dbsNew = wrkDefault.CreateDatabase(apiDB, _
dbLangGeneral, dbEncrypt)
dbsNew.Close
DoCmd.TransferDatabase acExport, "Microsoft Access", _
apiDB, acTable, "Century_Inventory", api, -1

now all i want to do is copy the current record into this table. I know i could edit this table and list all of the fields manually to copy it, but i want to do something that is more dynamic and copy and paste the entire record.

Durible Outer Casing to Prevent Fall-Apart

 

[legos]

After searching through the forums i've come up with this

DoCmd.RunSQL "INSERT INTO [another table] SELECT * FROM [Century_Inventory] WHERE [FileName] = Fname"

Is it possible to reference two tables contained in two different access projects with this SQL code?

Durible Outer Casing to Prevent Fall-Apart

 

[GKChesterton]

Can you not set up the new remote table as a linked table in your starting database?

[purple]If we knew what it was we were doing, it would not be called
[blue]research[/blue] database development, would it? [tab]-- Albert Einstein[/purple]​

 

[Golom]

Sure

DoCmd.RunSQL _
"INSERT INTO [red][;Database=C:\xxx\A.mdb].[/red][another table] " & _
"SELECT * FROM [red][;Database=C:\yyy\B.mdb].[/red][Century_Inventory] " & _
"WHERE [FileName] = '" & Fname & "'"

 

[legos]

Excellent!
It is a little clunky, but it will do until i can find a better way to go about copying the data. Here is the final form.

Set rst1 = db.OpenRecordset("Century_Inventory")
rst1.MoveFirst
Set dbsNew = wrkDefault.CreateDatabase(apiDB, _
dbLangGeneral, dbEncrypt)
dbsNew.Close
DoCmd.TransferDatabase acExport, "Microsoft Access", _
apiDB, acTable, "Century_Inventory", api, -1
SQL = "INSERT INTO [;Database=" & apiDB & "].[" & api & "] SELECT * FROM [Century_Inventory] WHERE [Filename] = '" & file & "'"
DoCmd.RunSQL SQL

Durible Outer Casing to Prevent Fall-Apart