INSERT INTO excel interface with two password protected databases

[jrdnoland]

I'm using an excel interface, ado, and sql.

I have two access 07 databases that I'm trying to append a record from one into the other.

The receiving database runs the generated sql with this:

Public Function Run_AppendQuery(SQLSTRING As String)
Dim cnt As ADODB.Connection
Dim stCon As String
Dim StrDB As String
On Error GoTo ErrHandle:
StrDB = c_Drive & c_MainFolder & c_SubFolder_RMS & c_SubFolder_RMS_PrimDB & c_DBName_RMS_PrimDB  'Primary
stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & StrDB & ";Persist Security Info=False;Jet OLEDB:Database Password=roscoe;"

Set cnt = New ADODB.Connection
cnt.Open stCon
cnt.Execute SQLSTRING
cnt.Close
Set cnt = Nothing

ExitHere:
Exit Function

ErrHandle:
    Dim cnErrors As ADODB.Errors
    Dim ErrorItem As ADODB.Error
    Dim stError As String
    Set cnErrors = cnt.Errors
   With Err
        stError = stError & vbCrLf & "VBA Error # : " & CStr(.Number)
        stError = stError & vbCrLf & "Generated by : " & .Source
        stError = stError & vbCrLf & "Description : " & .Description
        MsgBox Err.Description
   End With
    For Each ErrorItem In cnErrors
        With ErrorItem
            stError = stError & vbCrLf & "ADO error # : " & CStr(.Number)
            stError = stError & vbCrLf & "Description : " & .Description
            stError = stError & vbCrLf & "Source : " & .Source
         stError = stError & vbCrLf & "SQL State : " & .SqlState
        End With
    Next ErrorItem
    If InStr(stError, "3021") > 0 Or InStr(stError, "13") > 0 Or InStr(stError, "2147") > 0 Then
        Resume Next
    Else
        MsgBox stError, vbCritical, "SystemError"
        Resume ExitHere
    End If
i_Err:
    MsgBox "There are no Specifications for this Query.", vbInformation, "Query Info"
    GoTo ExitHere
End Function

The insert into is taking a record from the second database and trying to append it to the first (the one above). The record from the second db then gets deleted (that works).

This is a generated sql:

 INSERT INTO   tblManufacturersSecondary(P_Man_Numb_Revisions,P_Man_Stock_Code,P_Man_Iteration,P_Man_Name,
   P_Man_Trade_Name,P_Man_Address,P_Man_City,P_Man_State,P_Man_Zip,P_Man_Phone,P_Man_Fax,P_Man_Website,
   P_Man_Comments,P_Man_Expiry) 

   IN 'D:\RMS Database System\Raw Material Specification\Primary RMS Database\
   Raw Material Specification Primary Database.accdb'

  SELECT tblManufacturers.Man_Numb_Revisions,tblManufacturers.Man_Stock_Code,tblManufacturers.Man_Iteration,
   tblManufacturers.Man_Name,tblManufacturers.Man_Trade_Name,tblManufacturers.Man_Address,
   tblManufacturers.Man_City,tblManufacturers.Man_State,tblManufacturers.Man_Zip,tblManufacturers.Man_Phone,
   tblManufacturers.Man_Fax,tblManufacturers.Man_Website,tblManufacturers.Man_Comments,
   tblManufacturers.Man_Expiry 

   FROM tblManufacturers 

   IN 'D:\RMS Database System\Raw Material   Specification\Secondary RMS Database\
   Raw Material Specification Secondary   Database.accdb;PWD=roscoe;' 

   WHERE (((tblManufacturers.Man_Stock_Code)='0804-440' AND   (tblManufacturers.Man_Numb_Revisions)=5 
   AND (tblManufacturers.Man_Iteration)=1));

I can't seem to get the second database to open up with the supplied password.

I get an error message stating that the password in wrong or that it cannot find the file.

What is the syntax to run a sql that will append a record from one access 07 database to another.

I've even tried to let access generate the sql but it still doesn't work. Note the spacing is wrong in the second code block; it's correct in my application, I tried to make it easier to see.

I have also tried just using two connection strings and changing both passwords multiple times.

I can't find any information on the correct syntax for this type of situation.