Help Copying Access Table Data to Remote Access Table

[splaisance]

Hello!

I have two similar access databases. One has more functions, is linked to an as/400, and resides on the main NT server. The second database does not have as/400 links and is on a remote server. I need to copy the data from three tables in the first database into the second database.

Sometimes my vba code works doing this but sometimes the program simply hangs up. When I look at task manager the program is no longer responding. Below is the snippet of code of how I am connecting to the two databases. Also I am doing the copy from vba module in the first database (on the main server)

Dim TableName As String
Dim wsp As Workspace
Dim dbsOrig As Database
Dim dbsAnother As Database
Dim tdf As TableDef
Dim tdfAnother As TableDef
Dim rsAnother As Recordset
Dim rsOrig As Recordset
Dim fldOrig As Field
Dim qdf As QueryDef


' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)

' Return reference to current database.
Set dbsOrig = CurrentDb

' Keep track of the transactions made to the database
wsp.BeginTrans

' Return reference to the remote database
Set dbsAnother = wsp.OpenDatabase("Q:\Mort\LookUp.mdb&quot
TableName = "Q:\Mort\LookUp.mdb"



Any advice or suggestions would be greatly appreciated!

 

[Aivars]

Hi!

If you already have database "Q:\Mort\LookUp.mdb" then you can make links to tables of this DB. Accordingly you will be created simple queries for updating, inserting and more else for linked tables.

Aivars

 

[splaisance]

The second database "Q:\Mort\LookUp.mdb" is on a remote server. Every time the first database is used, we do not want it to link over the LAN to the second database as that will slow everything else down. Does that make sense?

 

[Aivars]

You can make links only in update moment:

strLinkSourceDB="Q:\Mort\LookUp.mdb"
DoCmd.TransferDatabase acLink,"Microsoft Access", _
strLinkSourceDB, acTable, "TableName", "TableName2"

..........
do...Update operations
...........

DoCmd.DeleteObject acTable, "TableName2"

Aivars

 

[splaisance]

I think perhaps my problem is I don't know how to copy data from a table in database1 to a table (with the same name) in database2. No matter what command I try Access gives me errors.