ADO connection Execute question

[Vigilante001]

Hello,

I am new to ADO, and would like to know how I can connect to a remote password-protected .mdb, and copy a remote table onto a local .mdb.

My current attempt is below, and does everything I want EXCEPT it creates the table copy on the remote .mdb. I want it on the local database, where the code is being run. Any ideas?

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim SQL As String
    
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Lorain\BCMHDB.mdb;Jet OLEDB:Database Password=nottelling;"
        .CursorLocation = adUseClient
        .Open
    End With
    
       
    On Error GoTo Transaction_Error
    cn.BeginTrans
    cn.Execute "SELECT PatientID, ClientFirstName, ClientLastName, DOB INTO tblTempPatientID FROM BCMHPatient"
    cn.CommitTrans
    Application.RefreshDatabaseWindow
    

    
Transaction_Complete:
    
    rs.Close
    cn.Close
    
    Exit Sub

Transaction_Error:
    cn.RollbackTrans
    MsgBox "Whoops!"
    Resume Transaction_Complete

Thank you!

[si_the_geek]

The SQL runs in the database you tell it too, which you do using the connection object (cn).

In order to have two databases interact with each other (which is you want here), you need to explicitly tell the one you are connected to that it needs to connect to the other as apt. There is an explanation of how to do that here: http://msdn2.microsoft.com/en-us/library/bb177907.aspx

[Vigilante001]

si_the_geek:

That's great... all the SELECT INTO sql descriptions didn't give me the dual path explanation. I'll post back if I run into trouble. Thank you!

[Vigilante001]

Ok.. I added the location in the IN statement, but now it is giving an error generated because my destination is locked. (this is also the same database running the code). Any ideas on how to copy the table into the destination without triggering the "Locked" error?

Here's the current code:

     Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Lorain\BCMHDB.mdb;Jet OLEDB:Database Password=nottelling;"
        .CursorLocation = adUseClient
        .Open
    End With
    
       
    'On Error GoTo Transaction_Error
    cn.BeginTrans
    cn.Execute "SELECT PatientID, ClientFirstName, ClientLastName, DOB INTO tblTempPatientID IN 'C:\Local\DailyActivity.mdb' FROM BCMHPatient"
    cn.CommitTrans
    Application.RefreshDatabaseWindow
    

    
Transaction_Complete:
    
    'cn.Close
    Set cn = Nothing
    Exit Sub

'Transaction_Error:
    'cn.RollbackTrans
    MsgBox "Whoops!"
    Resume Transaction_Complete

[si_the_geek]

What is the actual error message?

If it refers to the specific table, you need to make sure that you don't have that table open (either in a form, or a database window) when you run the code.

If it refers to the database as a whole, try doing the transfer the other way around - set the connection to use the current database, and use IN for the other database.

[Vigilante001]

si_the_geek:

It seems to be an entire database error. I'll try the reverse connection method, and report back. Thanks!