Copying access table from one database to another database
'-----------------------------------------------------------------------------
Even though this is an old post, i would like to give the solution for this. Because i searched a lot for this and i didnt get a good sample for this. So i think this solution should benefit people who are searching for a similar solution. By making little change this can be used with other databases also.
This is my sample solution.... Try this.
Make two connections of the database from which you want to copy file and another to copy file to.
Sample code......
'--------------------------------------------------------------------------
Public connServer As ADODB.Connection
Public connClient As ADODB.Connection
Public strServerDBPath, strClientDBPath, strServerDB, strClientDB As String
'----------------------------------------------------------------------
strServerDBPath ="C:\Temp\"
strServerDB="Mydata.mdb"
strConnServer= "Driver={Microsoft Access Driver (*.mdb)}; dbq=" & strServerDB & "; DefaultDir=" & strServerDBPath & "; pwd=123456"
strClientDBPath ="C:\Test\"
strClientDB="MyClient.mdb"
strConnClient= "Driver={Microsoft Access Driver (*.mdb)}; dbq=" & strClientDB & "; DefaultDir=" & strClientDBPath & "; pwd=123456"
Set connServer = New ADODB.Connection
connServer.CursorLocation = adUseClient
connServer.Open strConnServer
Set connClient = New ADODB.Connection
connClient.CursorLocation = adUseClient
connClient.Open strConnClient
connClient.Execute "Delete from MyTableName"
' MyTableName is the name of table exists in the destination database
' If it is not exists first create a table through sql.
connServer.Execute "Insert into [" & strClientDBPath & "\" & strClientDB & ";PWD=123456].MyTableName SELECT * From MyTableName"
'--------------------------------------------------------------------------
In this example i used two database, in both the database, same table
should be present.
If not first create the table using sql through "connServer.Execute " and
insert the record. This is not a direct method..... but this will help and
is an easy solution.
Be happy....
sankollam
|
