Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

copy table from ms access database to another

Posted on 2008-10-11
12
Medium Priority
?
1,894 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 120 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.
0
 
LVL 8

Assisted Solution

by:sstone55423
sstone55423 earned 120 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.
0
 
LVL 26

Assisted Solution

by:dannywareham
dannywareham earned 120 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
:-)
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:Mohammad Alsolaiman
ID: 22694254
Sorry
i need to do it by VBA code
0
 
LVL 1

Assisted Solution

by:vacnet
vacnet earned 120 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.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1040 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.
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
 
LVL 8

Expert Comment

by:sstone55423
ID: 22694310
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1040 total points
ID: 22694333
Example:

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

mx
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 480 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)
0
 
LVL 8

Expert Comment

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

Author Closing Comment

by:Mohammad Alsolaiman
ID: 31505309
thanks to all
0
 
LVL 1

Expert Comment

by:vacnet
ID: 22733690
You are welcome.

Cheers,

vacnet
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 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