Supplying a password in SQL

  • Thread starter Thread starter Phred Bear
  • Start date Start date
P

Phred Bear

I have a switchboard which extracts data from another Access 2000 db and
creates a new Project file. It all works fine on an unprotected db but I
can't get the hang of supplying the password to a protected db. Using the
OpenDatabase method works fine on a normal SELECT Query because I can supply
the pw in the 'Connect' option but I am stumped using RunSQL to execute an
action query using the "Select...INTO...IN" clause .

Any hints?

Many thanks.
 
Phred Bear said:
I have a switchboard which extracts data from another Access 2000 db
and creates a new Project file. It all works fine on an unprotected
db but I can't get the hang of supplying the password to a protected
db. Using the OpenDatabase method works fine on a normal SELECT Query
because I can supply the pw in the 'Connect' option but I am stumped
using RunSQL to execute an action query using the
"Select...INTO...IN" clause .

Any hints?

Many thanks.
Click to expand...

Which database -- source, or target -- is your code running in? Which
database is the password-protected one?
 
<< Which database -- source, or target -- is your code running in? Which
database is the password-protected one?>>

Oops!. It is all in Access 2000.

Thanks
 
Phred Bear said:
<< Which database -- source, or target -- is your code running in?
Which database is the password-protected one?>>

Oops!. It is all in Access 2000.
Click to expand...

No, that wasn't the question I was asking, though it's always a good
idea to mention the version. But I meant, you have two databases: one
from which you want to extract data (the "source" database), and one in
which you want to store data (the "target" database). Which of these
databases is running the code? And which one is password-protected?
 
<< No, that wasn't the question I was asking, though it's always a good
idea to mention the version. But I meant, you have two databases: one
from which you want to extract data (the "source" database), and one in
which you want to store data (the "target" database). Which of these
databases is running the code? And which one is password-protected?>>

Ahaaa,

I have an Access 2000 db with no content other than the code, to extract the
data, in a module and a single button to execute the code(db1). It is not
protected.

I have another Access 2000 db, which is password protected, with the data I
am looking to extract(db2).

I extract the data from db2 and create a new Project 98 file based on this
data, which isn't protected. All the code runs in db1 which quits at the end
of the procedure.(Issues with tidying up the running server, which running
the code in a seperate 'Switchboard' db and Quitting afterwards seems to
resolve satisfactorily.)

Here is a bit of the code which works:
dbName is a variable containing the path and db name.

SQLString = "SELECT Min([Date ToR Agreed]) AS MinDate FROM BarChart Barchart
IN '" & dbName & "';"

Set db = OpenDatabase(dbName, False, False, "MS Access; pwd=ThePassword")
Set rs = db.OpenRecordset(SQLString)
TempDate = rs!MinDate
ExtractMinDate = DateAdd("m", -1, TempDate)

Here is the bit which doesn't: (Although it works fine on an unprotected db)
Problem is: how do I insert the password?

SQLString = "SELECT [Ref No], [Date ToR Agreed], EstimatedEndDate,
[Legacy/DWP], NoOfInv, [Lead Investigator], [Support Investigator], [Ops
Mngr] INTO BarChart IN '" & dbName & "' "
SQLString = SQLString & " FROM Main IN '" & dbName & "' WHERE
((([Investigation Type]) <> ""6"") And (([End Date]) Is Null)) "
SQLString = SQLString & " ORDER BY [Ref No], [Investigation Type];"

DoCmd.RunSQL SQLString



Hope this explains it OK.


Many thanks for responding.
 
Phred Bear said:
<< No, that wasn't the question I was asking, though it's always a
good idea to mention the version. But I meant, you have two
databases: one from which you want to extract data (the "source"
database), and one in which you want to store data (the "target"
database). Which of these databases is running the code? And which
one is password-protected?>>

Ahaaa,

I have an Access 2000 db with no content other than the code, to
extract the data, in a module and a single button to execute the
code(db1). It is not protected.

I have another Access 2000 db, which is password protected, with the
data I am looking to extract(db2).

I extract the data from db2 and create a new Project 98 file based on
this data, which isn't protected. All the code runs in db1 which
quits at the end of the procedure.(Issues with tidying up the running
server, which running the code in a seperate 'Switchboard' db and
Quitting afterwards seems to resolve satisfactorily.)

Here is a bit of the code which works:
dbName is a variable containing the path and db name.

SQLString = "SELECT Min([Date ToR Agreed]) AS MinDate FROM BarChart
Barchart IN '" & dbName & "';"

Set db = OpenDatabase(dbName, False, False, "MS Access;
pwd=ThePassword") Set rs = db.OpenRecordset(SQLString)
TempDate = rs!MinDate
ExtractMinDate = DateAdd("m", -1, TempDate)
Click to expand...

Actually, that looks to me like you are specifying the database name
redundantly. If you have opened the database and set the object
variable db as a reference to it, then you wouldn't need to specify the
IN clause for that database when you use OpenRecordset to open a query
from it.
Here is the bit which doesn't: (Although it works fine on an
unprotected db) Problem is: how do I insert the password?

SQLString = "SELECT [Ref No], [Date ToR Agreed], EstimatedEndDate,
[Legacy/DWP], NoOfInv, [Lead Investigator], [Support Investigator],
[Ops Mngr] INTO BarChart IN '" & dbName & "' "
SQLString = SQLString & " FROM Main IN '" & dbName & "' WHERE
((([Investigation Type]) <> ""6"") And (([End Date]) Is Null)) "
SQLString = SQLString & " ORDER BY [Ref No], [Investigation Type];"

DoCmd.RunSQL SQLString

Hope this explains it OK.
Click to expand...

I don't see where Project 98 comes into it, but I'm going to proceed on
the assumption that it doesn't, as far as this question is concerned. I
note that the above SQL statement is extracting data from a table in
<dbName> and sticking that data into a new table in that same <dbName>
database. So it appears the source and target databases are one and the
same. Is that correct? If so, you don't really need the IN clause in
the SQL statement at all, so long as you execute that statement via a
database object that has been opened directly on the database you want
to manipulate. You should be able to do this:

'----- start of suggested code -----
Set db = OpenDatabase( _
dbName, False, False, _
"MS Access;pwd=ThePassword")

SQLString = _
"SELECT [Ref No], [Date ToR Agreed], EstimatedEndDate, " & _
"[Legacy/DWP], NoOfInv, [Lead Investigator], " & _
"[Support Investigator], [Ops Mngr] " & _
"INTO BarChart " & _
"FROM Main " & _
"WHERE " & _
"((([Investigation Type]) <> ""6"") And " & _
"(([End Date]) Is Null)) " & _
"ORDER BY [Ref No], [Investigation Type];"

db.Execute SQLString, dbFailOnError
'----- end of suggested code -----

As you see, since you specified the password when opening the database
object, and you use that database object to execute the query, that's
the database whose tables are going to be manipulated, and you don't
need an IN clause or any additional specification of the database
password.

It is also possible to specify the necessary connect string, including
the password, in the query itself, and thus not have to open a separate
database object (and be able to use RunSQL instead of db.Execute). But
it seems simpler to do it this way.
 
Dirk,

<< It is also possible to specify the necessary connect string, including
the password, in the query itself, and thus not have to open a separate
database object (and be able to use RunSQL instead of db.Execute). But
it seems simpler to do it this way.>>

That's what I was trying to do but I couldn't fiure out how you specified
the password in the query itself. However, your other solution will do just
fine.

Your correct, the Project file has got nothing to do with it. It is just the
final destination of the extracted data.

Many thanks.
 
Phred Bear said:
Dirk,

<< It is also possible to specify the necessary connect string,
including the password, in the query itself, and thus not have to
open a separate database object (and be able to use RunSQL instead
of db.Execute). But it seems simpler to do it this way.>>

That's what I was trying to do but I couldn't fiure out how you
specified the password in the query itself. However, your other
solution will do just fine.
Click to expand...

Here's a simple SELECT-Query example:

SELECT Expenses.*
FROM Expenses
IN "" "MS Access;Database=C:\Temp\SomeDB.mdb;pwd=foo";

And here's another variation that works:

SELECT Expenses.*
FROM [MS Access;Database=C:\Temp\SomeDB.mdb;pwd=foo].Expenses;
 
<< Here's a simple SELECT-Query example:

SELECT Expenses.*
FROM Expenses
IN "" "MS Access;Database=C:\Temp\SomeDB.mdb;pwd=foo";

And here's another variation that works:

SELECT Expenses.*
FROM [MS Access;Database=C:\Temp\SomeDB.mdb;pwd=foo].Expenses;>>

Sterling stuff,

As the man on "Who wants to be a millionaire" says , "It's not hard, you
either know it or you don't

many thanks Dirk.
 
Back
Top