Link to home
Start Free TrialLog in
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
Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag of Canada image

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

Avatar of bill201

ASKER

i don't want to copy all tables i want to copy only one table.
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
Avatar of 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)
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.
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial