I am unable to run an append query that copies records to a destination
database containing a password.

I'm using Access 2007 and both the source and destination databases are
saved in Access 2003. The append query copies all records from table A in
the source database to table A in the destination database. The fields in
the two tables are identical.

If the destination database has no password, the append query runs correctly.

I add a password to the destination database, then in the source database I
point the append query to the destination database and I add the password as
required by a dialog box. But when I run the query, I get a message box
saying "Not a valid password". The error is 3031.

How can I transfer records to an existing table in a destination database
that is protected by a password?

Thanks.

Jim

I am unsure how to set this property other than the actions I have taken.

I open the database, open the query in design view, click Design in the menu
and note that Append is selected under Query Type. I click Append and the
Append dialog box opens. It already shows the table name, the radio button
"Another Database" is selected and File Name displays:
C:\MyPath\Mydb.mdb;"PWD=Mypwd"

If I open the Properties Sheet, no data displays.

I'm unsure what to try next.

Jim



"Chris O'C via AccessMonster.com" wrote:

> The password has to be saved in the query, not typed in later when the query
> runs. Open the query in design view and add the path and password to the
> Dest Connect Str property using this syntax:
>
> ;DATABASE=C:\MyPath\Mydb.mdb;PWD=Mypwd
>
> Chris
> Microsoft MVP
>
>
> JBA wrote:
> >I add a password to the destination database, then in the source database I
> >point the append query to the destination database and I add the password as
> >required by a dialog box. But when I run the query, I get a message box
> >saying "Not a valid password". The error is 3031.
> >
> >How can I transfer records to an existing table in a destination database
> >that is protected by a password?
>
> --
> Message posted via http://www.accessmonster.com
>
>

With the append query in design view, I right-click on the upper panel and
select Properties. That displays a property sheet that shows Selection Type:
Field List Properties. It's empty. If I click again on the upper pane, the
property sheet displays query properties.

Destination Table contains the correct table name.
Destination DB contains the path to the db.
I enter the string described below for Dest Connect Str.
I run the query and again get the message "Not a valid password".

I then remove the password from the destination db and delete the password
reference at the end of the string for Dest Connect Str. Now, the query
appends records correctly.

Jim


"Chris O'C via AccessMonster.com" wrote:

> When the query is in design view and you see the query grid, right-click in
> the upper panel and select properties. The Dest Connect Str property is
> empty. You must type the connection string in that property using the
> following syntax:
>
> ;DATABASE=C:\MyPath\Mydb.mdb;PWD=Mypassword
>
> The Destination Table property may be empty, but it must have the name of the
> table in the destination database:
>
> TableA
>
> Save it and close the properties sheet, then run the query.
>
> Chris
> Microsoft MVP
>
>
> JBA wrote:
> >I am unsure how to set this property other than the actions I have taken.
> >
> >I open the database, open the query in design view, click Design in the menu
> >and note that Append is selected under Query Type. I click Append and the
> >Append dialog box opens. It already shows the table name, the radio button
> >"Another Database" is selected and File Name displays:
> >C:\MyPath\Mydb.mdb;"PWD=Mypwd"
> >
> >If I open the Properties Sheet, no data displays.
> >
> >I'm unsure what to try next.
> >
> >Jim
> >
> >> The password has to be saved in the query, not typed in later when the query
> >> runs. Open the query in design view and add the path and password to the
> >[quoted text clipped - 12 lines]
> >> >How can I transfer records to an existing table in a destination database
> >> >that is protected by a password?
>
> --
> Message posted via http://www.accessmonster.com
>
>

Yes,
you need to remove the password to access the data. After you have finished
with the spreadsheet, remember to set the password again.
I suppose this means that the password does work to protect the data in the
excel sheet - otherwise there would not be much advantage to setting a
password in the first place.


Jeanette Cunningham



"JBA" <(E-Mail Removed)> wrote in message
news:4AD66D49-3982-4685-9AFE-(E-Mail Removed)...
> With the append query in design view, I right-click on the upper panel and
> select Properties. That displays a property sheet that shows Selection
> Type:
> Field List Properties. It's empty. If I click again on the upper pane,
> the
> property sheet displays query properties.
>
> Destination Table contains the correct table name.
> Destination DB contains the path to the db.
> I enter the string described below for Dest Connect Str.
> I run the query and again get the message "Not a valid password".
>
> I then remove the password from the destination db and delete the password
> reference at the end of the string for Dest Connect Str. Now, the query
> appends records correctly.
>
> Jim
>
>
> "Chris O'C via AccessMonster.com" wrote:
>
>> When the query is in design view and you see the query grid, right-click
>> in
>> the upper panel and select properties. The Dest Connect Str property is
>> empty. You must type the connection string in that property using the
>> following syntax:
>>
>> ;DATABASE=C:\MyPath\Mydb.mdb;PWD=Mypassword
>>
>> The Destination Table property may be empty, but it must have the name of
>> the
>> table in the destination database:
>>
>> TableA
>>
>> Save it and close the properties sheet, then run the query.
>>
>> Chris
>> Microsoft MVP
>>
>>
>> JBA wrote:
>> >I am unsure how to set this property other than the actions I have
>> >taken.
>> >
>> >I open the database, open the query in design view, click Design in the
>> >menu
>> >and note that Append is selected under Query Type. I click Append and
>> >the
>> >Append dialog box opens. It already shows the table name, the radio
>> >button
>> >"Another Database" is selected and File Name displays:
>> >C:\MyPath\Mydb.mdb;"PWD=Mypwd"
>> >
>> >If I open the Properties Sheet, no data displays.
>> >
>> >I'm unsure what to try next.
>> >
>> >Jim
>> >
>> >> The password has to be saved in the query, not typed in later when the
>> >> query
>> >> runs. Open the query in design view and add the path and password to
>> >> the
>> >[quoted text clipped - 12 lines]
>> >> >How can I transfer records to an existing table in a destination
>> >> >database
>> >> >that is protected by a password?
>>
>> --
>> Message posted via http://www.accessmonster.com
>>
>>

I removed everything from the Destination DB property. The Dest Connect Str
already has my info. I still get the "Not a valid password" message.

To try to get something to run, I set up this test:
I use Access 2007 only, the source db is D1, the destination db is D2, both
databases have the same table called Table1 and a single field called Field1.
D2 is pw protected with pw=me. D1 is not pw protected.

I set up the append query in D1 with Destination Table=Table1, Destination
DB is empty and Dest Connect String=;DATABASE=C:\Documents and
Settings\James\My Documents\D2.accdb;PWD=me. I run the query and get the
"Not a valid password" message. The SQL is:
INSERT INTO (;DATABASE=C:\Documents and Settings\James\My
Documents\D2.accdb;PWD=me



) Table1 ( Field1 )
SELECT Table1.Field1
FROM Table1;

Then, I remove the pw from D2, and modify the append query so it's
properties are Destination Table=Table1, Destination DB=C:\Documents and
Settings\James\My Documents\D2.accdb and Dest Connect String is empty. The
query runs correctly.

I'm unsure what to try next.

Thanks.

Jim



"Chris O'C via AccessMonster.com" wrote:

> Remove the path and file name in the Destination DB property. It should be
> empty.
>
> In the Dest Connect Str property, change the path, file name, and password to
> match your own destination database. The ones I suggested earlier were only
> for an example.
>
> Chris
> Microsoft MVP
>
>
> JBA wrote:
> >With the append query in design view, I right-click on the upper panel and
> >select Properties. That displays a property sheet that shows Selection Type:
> > Field List Properties. It's empty. If I click again on the upper pane, the
> >property sheet displays query properties.
> >
> >Destination Table contains the correct table name.
> >Destination DB contains the path to the db.
> >I enter the string described below for Dest Connect Str.
> >I run the query and again get the message "Not a valid password".
> >
> >I then remove the password from the destination db and delete the password
> >reference at the end of the string for Dest Connect Str. Now, the query
> >appends records correctly.
>
> --
> Message posted via http://www.accessmonster.com
>
>