Solved

Copy table between MS Access files using VB

Posted on 2008-06-16
5
1,416 Views
Last Modified: 2012-05-05
I am having 2 MDB Access files say MDB_EMPTY and MDB_DATA which are having 1 table each.
Both are same tables say DB_ADDRESS. But DB_ADDRESS in MDB_EMPTY is having no data in it
and whereas MDB_DATA is having some data in it (say some 500 records).

Now I have to write a program in VB 6.0 to transfer those data from MDB_DATA to MDB_EMPTY.

Can anybody suggest in which way I have to do this?
0
Comment
Question by:siva_gk
  • 2
  • 2
5 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 21791380
The destination database does not need to be open. The source database does. If you dont open the database how do you expect to do a simple copy operation?

The only other way to "copy" a database object from one database to another with both being closed is to use a sql action query where you have to painstakenly use ADOX or such to retrieve the source objects table schema and recreate it in the other database. Then performa select into statement to copy the data. I honestly dont see why you cant have the 1 database opened via code for the easier methods I linked you to in my faq item.

This is from the first method in my faq and works without any problems having the destination database closed.

Option Explicit

'Add a reference to MS Access xx.0 Object Library

Private Sub Command1_Click()

    Dim oApp As Access.Application

    Set oApp = New Access.Application

    oApp.OpenCurrentDatabase "C:\Users\VB-Guru\Documents\RobDog888.mdb", False

    oApp.DoCmd.SetWarnings False

    oApp.DoCmd.CopyObject "C:\Users\VB-Guru\RobDog888.mdb", "NewTable", acTable, "Table1"

    oApp.DoCmd.SetWarnings True

    oApp.CloseCurrentDatabase

    oApp.Quit acQuitSaveAll

    Set oApp = Nothing

End Sub

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21791387
How do I import/copy a database object from another database?
http://www.vbforums.com/showthread.php?t=409309
0
 
LVL 2

Expert Comment

by:ema20
ID: 21791724
First you need to open the source database to copy the data in it by making instance of the ADO connection class, Using Excute method of the Connection object via action query INSERT INTO and specify the destination within the INSERT INTO string. Please see the code Snippet below.

I hope it will help you..



'Using ADO Connection Class

'References Microsoft ActiveX Data Objects

Private Sub Command1_Click()

Dim MDB_Data As New ADODB.Connection, destination As String 

destination = "d:\MDB_DATA.mdb" 'Destination of Data

MDB_Data.CursorLocation = adUseClient

MDB_Data.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\MDB_DATA.mdb" 'Source of Data

MDB_Data.Execute "INSERT INTO DB_ADDRESS IN '" & destination & "' SELECT * from DB_ADDRESS"

End Sub

Open in new window

0
 
LVL 2

Accepted Solution

by:
ema20 earned 500 total points
ID: 21791801
sorry i've send the wrong source code for destination of data instead of MDB_DATA please replace it by destination = "d:\MDB_EMPTY". im sorry this my first time to answer question on this forums.
'Using ADO Connection Class

'References Microsoft ActiveX Data Objects

Private Sub Command1_Click()

Dim MDB_Data As New ADODB.Connection, destination As String 

destination = "d:\MDB_EMPTY.mdb" 'Destination of Data

MDB_Data.CursorLocation = adUseClient

MDB_Data.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\MDB_DATA.mdb" 'Source of Data

MDB_Data.Execute "INSERT INTO DB_ADDRESS IN '" & destination & "' SELECT * from DB_ADDRESS"

End Sub

Open in new window

0
 

Author Comment

by:siva_gk
ID: 21872085
thanks ema...

I already changed that in my ocde.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

757 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

18 Experts available now in Live!

Get 1:1 Help Now