Invalid password when using IN clause

[Francisco R]

I have two password-protected databases (Access 2000).
Both have the same password. I'm creating a query to copy
some data from one database to the other one. I use this:

INSERT INTO Table1 IN 'c:\mydb1.mdb' SELECT Field1, Field2
FROM Table2 WHERE Field1=anynumberhere

Table1 already exists so thats why I use INSERT INTO but I
received an 'Invalid Password' message when I run the
query...why it could be happening? Something I noticed is
that sometimes it works when the source (Table2) has data
and it fails when it doesn't.....

Is there a way to specify the password within the query?

Any help would be very appreciated...I'm stuck on this.
Regards.

 

[Gary Walter]

I have two password-protected databases (Access 2000).
Both have the same password. I'm creating a query to copy
some data from one database to the other one. I use this:

INSERT INTO Table1 IN 'c:\mydb1.mdb' SELECT Field1, Field2
FROM Table2 WHERE Field1=anynumberhere

Table1 already exists so thats why I use INSERT INTO but I
received an 'Invalid Password' message when I run the
query...why it could be happening? Something I noticed is
that sometimes it works when the source (Table2) has data
and it fails when it doesn't.....

Is there a way to specify the password within the query?

Hi Francisco,

Typically one would just link to the table in the remote
database and save yourself time and grief.

If you do not wish to do it that way for some reason....

The target of an INSERT sql can be a table
* or a query.*

To create a query to the remote, password-protected
table....

- Start a new query (don't choose any tables).
- In SQL View, type in

SELECT * FROM Table1

- Right-mouse click in empty part of upper pane and choose "Properties"

-in "SourceConnectStr" row type in
(following example will use 'homer' as your password
--change 'homer' to your password)

MS Access;PWD=homer;DATABASE=c:\mydb1.mdb

-save your query with name like "qryRemote"

- test query that returns all of Table1
("Source Database" row in Properties should be blank)

-use "qryRemote" in your INSERT query instead of "Table1"

INSERT INTO qryRemote SELECT Field1, Field2
FROM Table2 WHERE Field1=anynumberhere

All thanks go to "Doug" who demonstrated this remote
db w/password query technique in this newsgroup.

Good luck,

Gary Walter