Append to Password Protected Database

[markwakelin]

Aaaaarrggghhh !!!!!!!!!!

Can anybody PLEASE help me ! This is driving me mad !

I am using an append query to update data from one database to another. This works fine when the database i am appending to does NOT have a database password....However ! When the database I am appending to, DOES have a database password, no joy !!
I am using Access 2000 to do this for me, so you would think it would work !!! Nope !
Here is my SQL..

INSERT INTO [;PWD=driftwood].AcceptedPayment IN 'C:\TO.mdb'[;PWD=driftwood]
SELECT AcceptedPayment.*
FROM AcceptedPayment;

As you can see, my password is driftwood...

Please, please, please, please can anybody shed some light on this matter.

Ta,
Mark

PS As you can tell, i am REALLY struggling with this !

 

[Fizzio]

have you tried without the ; ie.

INSERT INTO [PWD=driftwood].AcceptedPayment IN 'C:\TO.mdb'[PWD=driftwood]
SELECT AcceptedPayment.*
FROM AcceptedPayment;

 

[markwakelin]

Yes, still doesn't work.
Brings up different error message.

I am using the Access design screen, selecting Append Query.
This is why i can't figure out why it is not working, Access is generating the SQL code !

Mark

 

[Fizzio]

what are the error messages you are getting from both methods?

 

[markwakelin]

when i have the initial SQL code.. (ie. with the
i get "Not a valid password"
Now, I know what ur gonna say !! But it IS the right password.

when i have the new SQL code.. (ie. without the
i get "Syntax error In INSERT INTO statement"

sigh......

 

[markwakelin]

i have attached two sample databases, one with password.
I am trying to get the data from 'Test_FROM.mdb' into 'Test_To.mdb'

the database password is '1'

 

[Jon K]

Try this:-

INSERT INTO AcceptedPayment In '' 'C:\TO.mdb;PWD=driftwood;'
SELECT AcceptedPayment.*
FROM AcceptedPayment;

or this:-

INSERT INTO [C:\TO.mdb;PWD=driftwood;].AcceptedPayment
SELECT AcceptedPayment.*
FROM AcceptedPayment;

 

[markwakelin]

WOO HOOO !!!

Thanks. That has done the trick !

Mark.

 

[nlaslett]

Nearly 5 years on, and this is still the best - and only - answer I have been able to find on-line for this VERY vexing problem. Access 2003 SP3 is still generating incorrect syntax for Append queries targeting password-protected databases. Note that in Jon K's solution, Access will automatically convert the first option into the seciond option. Both work equally well.

Anybody know if this has been fixed in Access 2007?

Neil