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
i want to copy a structure from one table to another database file how can i do it
- there are several solution in EE on the same question. you can look into the following links:
https://www.experts-exchange.com/questions/20552999/Copy-table-structure-not-data-through-VBA.html?sfQueryTermInfo=1+10+30+copi+structur+tabl+vba
https://www.experts-exchange.com/questions/24740778/Copying-a-table-structure-via-VBA.html?sfQueryTermInfo=1+10+30+copi+structur+tabl+vba
https://www.experts-exchange.com/questions/24724655/VBA-code-to-copy-table-structure-and-data-from-one-access-database-to-another.html?sfQueryTermInfo=1+10+30+copi+structur+tabl+vba
https://www.experts-exchange.com/questions/20552999/Copy-table-structure-not-data-through-VBA.html?sfQueryTermInfo=1+10+30+copi+structur+tabl+vba
https://www.experts-exchange.com/questions/24740778/Copying-a-table-structure-via-VBA.html?sfQueryTermInfo=1+10+30+copi+structur+tabl+vba
https://www.experts-exchange.com/questions/24724655/VBA-code-to-copy-table-structure-and-data-from-one-access-database-to-another.html?sfQueryTermInfo=1+10+30+copi+structur+tabl+vba
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
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
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!"
Simply adapt it to copy the structure of one table: DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject.Path & "\test2.mdb", acTable, "query1", "query1", true
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
That's why the second snippet does only that
DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject
Explanation of the syntax:
DoCmd.TransferDatabase acExport -- export it
"Microsoft Access" - what format?
Application.CurrentProject
acTable--object to be exported
query1 -- name of the source object
query1 -- name to be gioven in the target
true -- structure only
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open the destination database.
Open the Database Window in the Table tab.
Right click in the Database Window, select Paste and then Structure Only.