• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1456
  • Last Modified:

Copy table between MS Access files using VB

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
siva_gk
Asked:
siva_gk
  • 2
  • 2
1 Solution
 
DhaestCommented:
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
 
DhaestCommented:
How do I import/copy a database object from another database?
http://www.vbforums.com/showthread.php?t=409309
0
 
ema20Commented:
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
 
ema20Commented:
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
 
siva_gkAuthor Commented:
thanks ema...

I already changed that in my ocde.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now