Avatar of bill201
bill201
 asked on

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

hi

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
Nick67

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.
bill201

ASKER
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
Nick67

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

bill201

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

Right,

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.
bill201

ASKER


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)
Nick67

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
http://en.allexperts.com/q/Using-MS-Access-1440/2009/10/Import-Table-Password-Protected.htm
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.
bill201

ASKER
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.
fblack61
ASKER CERTIFIED SOLUTION
Nick67

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
or
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