Not a Valid Password problem Access

[Alby22]

I am doing an append query that takes data from a table(TIMESHEET )from data base "A" and appending that data to the same table in database B. Database B has a password and when I try to run the Query I get the not a valid password message box. Below is what access gave me when I ran the Query Wizard. I found other similar questions online but none of the solutions for those worked.
For reference "bmi test.mdb" would be my database B as mentioned. Also I realize the are errors in here but I'm just showing what I have to work with.

INSERT INTO [;PWD=password].TIMESHEET ( [RECORD NUMBER], [G/L CODE], [LINE TYPE], [JOB NUMBER], [OPERATION NUMBER], [RATE INDICATOR], [EMPLOYEE NUMBER], [DATE], [RUN TIME], [NON-PRODUCTIVE CODE], [EMPLOYEE RATE], POSTED, [PAY TYPE], POSTABLE, USE, [FIRST PR WEEK], EmployeeID, MILEAGE, EXPENSE, [PART NUMBER], QTY, [RECEIPT LINE TYPE], COST, [KIT ISSUE], [KIT POSTED], BIN ) IN 'M:\server docs\Payroll\bm i test.mdb'[;PWD=password]

The select and from parts have been removed because they are not part of the problem.

[MMcCarthy]

Firstly, have you tried reversing it and putting the code in the target database.

INSERT INTO TableName SELECT * FROM TableName IN [MS Access;DATABASE='M:\server docs\Payroll\bmi test.mdb';Jet OLEDB:Database Password=password;]

Otherwise try this ...

INSERT INTO [TableName] ( [RECORD NUMBER], [G/L CODE], [LINE TYPE], [JOB NUMBER],
[OPERATION NUMBER], [RATE INDICATOR], [EMPLOYEE NUMBER], [DATE],
[RUN TIME], [NON-PRODUCTIVE CODE], [EMPLOYEE RATE], POSTED, [PAY TYPE],
POSTABLE, USE, [FIRST PR WEEK], EmployeeID, MILEAGE, EXPENSE, [PART NUMBER],
QTY, [RECEIPT LINE TYPE], COST, [KIT ISSUE], [KIT POSTED], BIN )
IN [MS Access;DATABASE='M:\server docs\Payroll\bmi test.mdb';Jet OLEDB:Database Password=password;]

[Alby22]

I tried the second solution and I received an "invalid bracketing" error on the IN statement.I figured i would try that first since I already had the code for it. I'm using Access 2003 so I don't know if that changes anything. I will go ahead to try the first solution as well and then let you know how that goes. Also, does anyone happen to know why the Access generated code doesn't work? Seems strange the the code generated by the program itself would be incorrect.I read that there was an issue if your password had a "/" as the last character but other than that its supposed to work i guess. I have little experience with Access so hopefully these aren't "dumb questions". I tried solution 1 which also did not work. It does look easier thought to import it rather than export it to eliminate the issue with the password. I will try to work at it from that angle.Do you have to use an INSERT INTO/IN because of access or could I also try an INSERT INTO/VALUES/SELECT/FROM?

[Alby22]

I decided to go a different route and link two tables between the databases and then run an append query to pull the data form the linked table. Thanks for the input though!

[MMcCarthy]

If I understood you correctly you can use an INSERT INTO/VALUES/SELECT/FROM statement.

If you would like to try option 2 again remove the single quotes as per the following and see if it makes any difference.

INSERT INTO [TableName] ( [RECORD NUMBER], [G/L CODE], [LINE TYPE], [JOB NUMBER],
[OPERATION NUMBER], [RATE INDICATOR], [EMPLOYEE NUMBER], [DATE],
[RUN TIME], [NON-PRODUCTIVE CODE], [EMPLOYEE RATE], POSTED, [PAY TYPE],
POSTABLE, USE, [FIRST PR WEEK], EmployeeID, MILEAGE, EXPENSE, [PART NUMBER],
QTY, [RECEIPT LINE TYPE], COST, [KIT ISSUE], [KIT POSTED], BIN )
IN [MS Access;DATABASE=M:\server docs\Payroll\bmi test.mdb;Jet OLEDB:Database Password=password;]