copy table from ms access database to another

Posted on 2008-10-11
Last Modified: 2013-11-29
I need to copy the table structure with the data in it, from database1 to database2.
Is it possible to do so.
Question by:Mohammad Alsolaiman
  • 3
  • 2
  • 2
  • +4
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 30 total points
ID: 22694141
Yes.  Right click the table you want to copy, choose export, and follow the wizard to copy it to another file.

Assisted Solution

sstone55423 earned 30 total points
ID: 22694148
Choose the table, right click EXPORT | Access Database |  Give it the name of the other database, and it will put it in there, structure and data.
LVL 26

Assisted Solution

dannywareham earned 30 total points
ID: 22694194
You can also just open a new database and drag and drop the table across, or click CTRL+C to copy, then CTRL+V to paste into your newdb.

So many choices
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

by:Mohammad Alsolaiman
ID: 22694254
i need to do it by VBA code

Assisted Solution

vacnet earned 30 total points
ID: 22694273
1. Open the DB you want the table in.
2. Click on File>Import.
3. Youll then see the Import window where you need to choose the DB that has the table you need. Click Import
4. An Import Objects dialogue box will come up, make sure you are on the Tables Tab. Select/highlight the Table you want to Import.
5. Select OK.
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Access MVP) earned 260 total points
ID: 22694306
You can use the TransferDatabase command in VBA.  From the Help File:

The TransferDatabase method carries out the TransferDatabase action in Visual Basic.

expression.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)
expression    Required. An expression that returns one of the objects in the Applies To list.

TransferType   Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acImport default
If you leave this argument blank, the default constant (acImport) is assumed.

Note  The acLink transfer type is not supported for Microsoft Access projects (.adp).

DatabaseType   Optional Variant. A string expression that's the name of one of the types of databases you can use to import, export, or link data.

Types of databases:
Microsoft Access (default)
Jet 2.x
Jet 3.x
dBase III
dBase IV
dBase 5.0
Paradox 3.x
Paradox 4.x
Paradox 5.x
Paradox 7.x
ODBC Databases
In the Macro window, you can view the database types in the list for the Database Type action argument of the TransferDatabase action.

DatabaseName   Optional Variant. A string expression that's the full name, including the path, of the database you want to use to import, export, or link data.

ObjectType   Optional AcObjectType.

AcObjectType can be one of these AcObjectType constants.
acTable default
This is the type of object whose data you want to import, export, or link. You can specify an object other than acTable only if you are importing or exporting data between two Microsoft Access databases. If you are exporting the results of a Microsoft Access select query to another type of database, specify acTable for this argument.

If you leave this argument blank, the default constant (acTable) is assumed.

Note  The constant acDefault, which appears in the Auto List Members list for this argument, is invalid for this argument. You must choose one of the constants listed above.

Source   Optional Variant. A string expression that's the name of the object whose data you want to import, export, or link.

Destination   Optional Variant. A string expression that's the name of the imported, exported, or linked object in the destination database.

StructureOnly   Optional Variant. Use True (1) to import or export only the structure of a database table. Use False (0) to import or export the structure of the table and its data. If you leave this argument blank, the default (False) is assumed.

StoreLogin   Optional Variant. Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.


Expert Comment

ID: 22694310
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 260 total points
ID: 22694333

DoCmd.TransferDatabase acExport, "Microsoft Access", sPath, acTable, "zug411tblLabor", "tblLabor", True

LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 120 total points
ID: 22694332
this will copy table products from db1 to db2

dim sql
        sql = "SELECT Products.* INTO Products IN '" & CurrentProject.path & "\db2.mdb" & "'"
        sql = sql & " FROM Products;"
        CurrentDb.Execute (sql)

Expert Comment

ID: 22713547
How is it going?  Just checking in.  Were you able to copy the table okay, or do you need further assistance.  If it worked for you, please close the question.  With so many people answering, be sure and give partial credit as you feel it applies.

Author Closing Comment

by:Mohammad Alsolaiman
ID: 31505309
thanks to all

Expert Comment

ID: 22733690
You are welcome.



Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question