Solved

copy table from ms access database to another

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

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 30 total points
Comment Utility
Yes.  Right click the table you want to copy, choose export, and follow the wizard to copy it to another file.
0
 
LVL 8

Assisted Solution

by:sstone55423
sstone55423 earned 30 total points
Comment Utility
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.
0
 
LVL 26

Assisted Solution

by:dannywareham
dannywareham earned 30 total points
Comment Utility
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
:-)
0
 

Author Comment

by:Mohammad Alsolaiman
Comment Utility
Sorry
i need to do it by VBA code
0
 
LVL 1

Assisted Solution

by:vacnet
vacnet earned 30 total points
Comment Utility
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.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 260 total points
Comment Utility
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.
acExport
acImport default
acLink
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
WSS
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.
acDataAccessPage
acDefault
acDiagram
acForm
acFunction
acMacro
acModule
acQuery
acReport
acServerView
acStoredProcedure
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.

*****
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 8

Expert Comment

by:sstone55423
Comment Utility
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 260 total points
Comment Utility
Example:

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

mx
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 120 total points
Comment Utility
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)
0
 
LVL 8

Expert Comment

by:sstone55423
Comment Utility
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.
0
 

Author Closing Comment

by:Mohammad Alsolaiman
Comment Utility
thanks to all
0
 
LVL 1

Expert Comment

by:vacnet
Comment Utility
You are welcome.

Cheers,

vacnet
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Move SQL 2005 Express to Server 2012R2 19 68
Data center mess 4 45
Numbers are exporting as text 5 24
Max per month 3 12
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now