Avatar of bill201
 asked on

how can i copy a table from a database to another database with vba


i want to copy a structure from one table to another database file how can i do it
Microsoft AccessDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon
Jacques Bourgeois (James Burger)

Right click on the original table in the Database Window and select Copy.
Open the destination database.
Open the Database Window in the Table tab.
Right click in the Database Window, select Paste and then Structure Only.

i wil explain my question

i have a database with the name: Test1.mdb
in this database i have a table with the name "query1"
i have a second db with the name test2.mdb
i want with vba to copy the query 1 from test1.mdb to test2.mdb
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

This will copy every table from one db to another
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Not tdf.Name Like "Msys*" Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject.Path & "\test.mdb", acTable, tdf.Name, tdf.Name, False
    End If
Next tdf

MsgBox "Done!"

Open in new window

Simply adapt it to copy the structure of one table:
DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject.Path & "\test2.mdb", acTable, "query1", "query1", true

Open in new window


i don't want to copy all tables i want to copy only one table.


That's why the second snippet does only that
DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject.Path & "\test2.mdb", acTable, "query1", "query1", true
Explanation of the syntax:
DoCmd.TransferDatabase acExport -- export it
"Microsoft Access" - what format?
Application.CurrentProject.Path & "\test2.mdb" -- string representing the full apth to the export target
acTable--object to be exported
query1 -- name of the source object
query1 -- name to be gioven in the target
true -- structure only
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.


i don't want to export i want import
in other words i'm in test 2 and i want to import query1 from test1

and by the way i will be happy if you will explain me where to put the "pwd=" (it's a password protected file)

That's easy and difficult--or unknown to me anyway--at the same time.
Change acExport to acImport to alter the direction--that's the simple part
That the file is password protected is the hard part.  Look here
What that poster does, is use OpenDatabase to create a Database object for the password-protected file.
He then creates link tables to that file, copies those link files as local tables and deletes the link files.
That's a lot more complex.

the file test1 that i want import is not in the same path from test2 it's in this path c:\user\docuemnts
Your help has saved me hundreds of hours of internet surfing.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question