Import Tables From one Database to the Other!

Posted on 2003-03-20
Medium Priority
Last Modified: 2010-05-01
Hi All!

i use this code to create link between databases!

i got this code from vbcode.com

can anybody tell me that hw can i do the import tables function??

even i dont require in same format........but what ever easier and effective!

Dim dbname As String
Dim Eng As String
Dim engcn As ADODB.Connection
Dim engcat As ADOX.Catalog
Dim engtbl As ADOX.Table
Dim tablename As String
Dim suitecat As ADOX.Catalog
Dim suitecn As ADODB.Connection
Dim suitetbl As ADOX.Table
Dim designcat As ADOX.Catalog
Dim designcn As ADODB.Connection
Dim designtbl As ADOX.Table

Private Sub Command2_Click()
dbname = "D:\My Documents\Access Files\Budget.mdb"
Eng = "D:\My Documents\Access Files\Copy of db1.mdb"
Set engcn = New ADODB.Connection
   engcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Eng & ""
   Set suitecn = New ADODB.Connection
   suitecn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbname & ""
   Set engcat = New ADOX.Catalog
   Set suitecat = New ADOX.Catalog
    engcat.ActiveConnection = engcn
    suitecat.ActiveConnection = suitecn
    For Each suitetbl In suitecat.Tables
    If suitetbl.Type = "TABLE" Then
    Set engtbl = New ADOX.Table
    tablename = suitetbl.Name
    engtbl.Name = tablename
    Set engtbl.ParentCatalog = engcat
    engtbl.Properties("Jet OLEDB:Link Datasource") = dbname
    engtbl.Properties("Jet OLEDB:Remote Table Name") = tablename
    engtbl.Properties("Jet OLEDB:Create Link") = True
    engcat.Tables.Append engtbl
    End If
End Sub
Question by:Sankar_V
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

makman earned 100 total points
ID: 8179245
Hi SankarV
you can use a TransferDatabase Method.
Syntax below:
TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)
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
Refrence Link:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/html/acmthactTransferDatabase.asp

Author Comment

ID: 8179772
Thanks makman!

Thanks for ur reference!

I got the result which i want!

Thanks a lot!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 17 hours left to enroll

752 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