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!