Append Query

[JBA]

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

 

[JBA]

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

 

[JBA]

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

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

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?

 

[Jeanette Cunningham]

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

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

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

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?

 

[JBA]

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