SQL SELECT INTO another database that is password protected

Question

Hi.  I am using Microsoft Access 2010 / VBA and I am trying to copy a record from the current database into another database that is password protected.  I can not seem to pass the password into the other database.  (Meaning I keep getting the run-time error '3031: Not a valid password').

This is the code that I am using:

Dim db as Database

Dim strSQL as string

set db = CurrentDb()

  strSQL = "INSERT INTO " & _

        "['; PWD = Password1234;DATABASE=\Server\Folder\DatabaseName2.accdb'].tbl_Table1 " & _

        "(FieldA, FieldB, FieldC, FieldD, FieldE) " & _

        " SELECT A.Field1, A.Field2, A.Field3, B.Field4, B.Field5 " & _

        " FROM tbl_TableA A INNER JOIN TableB B ON A.Field1 = B.Field1 " & _

        " WHERE A.Field1 = Criteria;"

 db.Execute strSQL, dbFailOnError

Any help would be appreciated.

B

Answer 1

Hi,

replace

"['; PWD = Password1234;DATABASE=\Server\Folder\DatabaseName2.accdb'].tbl_Table1 " & _

by

"[;DATABASE=\Server\Folder\DatabaseName2.accdb;PWD=Password1234].tbl_Table1 " & _

Answer 2

That was it!  Thank you!