Using IN clause with passworded database

  • Thread starter Thread starter GeorgeMar
  • Start date Start date
G

GeorgeMar

I am having problem setting the password for an external
database in a query. In the Query Properties form, I
enter the following:

Source Database: C:\MyDir\MyFile.mdb
Source Connect Str: ;PWD=mypassword

The resulting SQL script is: SELECT table1.* FROM table1
IN 'C:\MyDir\MyFile.mdb'[;PWD=mypassword]

I have also tried to put the two lines in the Source
database line.

Whatever I try, I get the error meaasge: Not a Valid
Password.

Can any one help?

many thanks
george

P.S I also cannot get help on anything SQL. E.g FROM,IN
etc.
 
Probably a silly question, but: are you absolutely positive that the
password you entered in the source connect string, is identical to the one
that actually opens that database? Try copying the password directly from
the query window. Then close that db, start the other one, paste-in the
copied password, & see if that works. If not, you've just got the password
wrong.

HTH,
TC
 
Thanks TC

I have been over the password many times, to the point
where I changed the external password to just "a". Thus,
the source connect string in the query properties form
is ;PWD=a

Could there be a syntax error in the above line, am I
missing a ' or " or , anywhere? Is what I am doing the
right way to do it?

If I can get it working in the query window, I can get it
working in the SQL statement.

Getting desperate!
Thanks
george
-----Original Message-----
Probably a silly question, but: are you absolutely positive that the
password you entered in the source connect string, is identical to the one
that actually opens that database? Try copying the password directly from
the query window. Then close that db, start the other one, paste-in the
copied password, & see if that works. If not, you've just got the password
wrong.

HTH,
TC


GeorgeMar said:
I am having problem setting the password for an external
database in a query. In the Query Properties form, I
enter the following:

Source Database: C:\MyDir\MyFile.mdb
Source Connect Str: ;PWD=mypassword

The resulting SQL script is: SELECT table1.* FROM table1
IN 'C:\MyDir\MyFile.mdb'[;PWD=mypassword]

I have also tried to put the two lines in the Source
database line.

Whatever I try, I get the error meaasge: Not a Valid
Password.

Can any one help?

many thanks
george

P.S I also cannot get help on anything SQL. E.g FROM,IN
etc.
Click to expand...


.
Click to expand...
 
GeorgeMar said:
Thanks TC

I have been over the password many times, to the point
where I changed the external password to just "a". Thus,
the source connect string in the query properties form
is ;PWD=a

Could there be a syntax error in the above line, am I
missing a ' or " or , anywhere? Is what I am doing the
right way to do it?

If I can get it working in the query window, I can get it
working in the SQL statement.
Click to expand...

Huh. Seems to me TC is right, and that should work. Try entering this
in SQL view and see if it works:

SELECT Table1.* FROM Table1
IN "" "MS Access;Database=C:\MyDir\MyFile.mdb;PWD=password";

I'm not sure whether it makes a difference if the path or file name
contains embedded spaces, but that might also be something to look at.
 
Thank you TC and Dirk

I have found the answer after many trials and tribulations.

The answer is that in the Query Properties window leave
the Source Database blank and enter the following in he
source connect str:
;DATABASE=C:\MyDir\MyFile.mdb;PWD=mypassword
and it works.

NOTE: There must be a ; before DATABASE for it to work.

The resulting SQL statement is (similar to Dirk's)

SELECT Table1.* FROM Table1
IN "" ";Database=C:\MyDir\MyFile.mdb;PWD=password";


Thank you again and I hope that this helps others.
regards
george
 
Well done.

TC


GeorgeMar said:
Thank you TC and Dirk

I have found the answer after many trials and tribulations.

The answer is that in the Query Properties window leave
the Source Database blank and enter the following in he
source connect str:
;DATABASE=C:\MyDir\MyFile.mdb;PWD=mypassword
and it works.

NOTE: There must be a ; before DATABASE for it to work.

The resulting SQL statement is (similar to Dirk's)

SELECT Table1.* FROM Table1
IN "" ";Database=C:\MyDir\MyFile.mdb;PWD=password";


Thank you again and I hope that this helps others.
regards
george
Click to expand...
 
Back
Top