Password Prompt on Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have an Access 2003 query which uses 3 linked tables to an Oracle DB.
Even if I select the remember password option when linking the tables I am
still prompted for the password when using queries etc...

I would like to be able to have the query running on startup using the
autoexec macro, however, I am prompted for the password and would like the
whole process to be automated.

I am only a novice user of Access and VB and have the following code so far.
Can I include a password for this so that the whole routine can be automated
and palced on a scheduled task?

Function Autoexec()
On Error GoTo Autoexec_Err

DoCmd.OpenQuery "Ex2k3AddJobs", acViewNormal, acReadOnly
DoCmd.OutputTo acQuery, "", "MicrosoftExcelBiff5(*.xls)",
"d:\excel.xls", False, "", 0

Autoexec_Exit:
Exit Function

Autoexec_Err:
MsgBox Error$
Resume Autoexec_Exit

End Function

Many Thanks
Elliott Millin
 
If you are only reading the data and not attempting to update the data then
you ought to use a passthrough query. Not only are they much faster for
querying Oracle but you can also store the user name and password for the
Oracle account in the connection string. Here is an example for one that I
use...

ODBC;DRIVER={ORACLE in OraHome92}
;SERVER=A215;DBQ=A215;UID=USERNAME;PWD=PASSWORD;DBA=W;APA=T;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;

This connection string uses the Oracle driver directly and does not use a DSN
entry. UID is the Oracle account and PWD is the password. SERVER and DBQ
are the Oracle instances where the Oracle database is at.

Cheers,
Eric
 
Eric, thanks for your reply.

I have done some digging around and am now using a function that will
attempt to get the data I am after using a passthrough query as you have
suggested (see code below).

Function GetData()

Dim db As Database

Set db = CurrentDb()

CurrentDb.QueryDefs.Delete "PFDATA"

'Create a temporary passthrough query
Set LPass = db.CreateQueryDef("PFDATA")

'Use ODBC connection
LPass.Connect = "ODBC;DSN=*****;UID=*****;PWD=*****;SERVER=*****"
LPass.SQL = "SELECT SYNTECH_JCJAUD.ACTION_DATE,
SYNTECH_JCJAUD.ACTION_TYPE, SYNTECH_JCJAUD.JOB, SYNTECH_JCJAUD.PROJECT,
SYNTECH_JCPRJCT.NAME_SHORT, SYNTECH_PEPERSON.SURNAME,
SYNTECH_PEPERSON.FORENAME FROM (SYNTECH_JCJAUD INNER JOIN SYNTECH_JCPRJCT ON
(SYNTECH_JCJAUD.COMPANY = SYNTECH_JCPRJCT.COMPANY) AND
(SYNTECH_JCJAUD.PROJECT = SYNTECH_JCPRJCT.PROJECT)) INNER JOIN
SYNTECH_PEPERSON ON (SYNTECH_JCPRJCT.COMPANY = SYNTECH_PEPERSON.COMPANY) AND
(SYNTECH_JCPRJCT.MANAGER = SYNTECH_PEPERSON.PERSONNEL_NO) GROUP BY
SYNTECH_JCJAUD.ACTION_DATE, SYNTECH_JCJAUD.ACTION_TYPE, SYNTECH_JCJAUD.JOB,
SYNTECH_JCJAUD.PROJECT, SYNTECH_JCPRJCT.NAME_SHORT, SYNTECH_PEPERSON.SURNAME,
SYNTECH_PEPERSON.FORENAME HAVING (((SYNTECH_JCJAUD.ACTION_DATE) = Date()) And
((SYNTECH_JCJAUD.ACTION_TYPE) = 'Insert') And ((SYNTECH_JCJAUD.Project) >
'0000001')) ORDER BY SYNTECH_JCJAUD.ACTION_DATE DESC;"

LPass.ReturnsRecords = True

End Function

The problem I am having is that when I run the function and then attempt to
open the query I receive a message back (see below):

ODBC--Call Failed

[Oracle][ODBC][Ora]ORA-00928: missing SELECT keyword (#928)

If I simply create a new query within my DB and use exactly the same SQL
statement the results are returned normally. Is it because I am using the
wrong query type or something?

Confused!!
Elliott
 
One of the problems that you are running up against is the syntax of the sql
string. 'HAVING' is a term used only in Access. Typing sql is much much
much easier in Oracle than it is in Access. Your sql string needs to look
something like this...

LPass.SQL = "SELECT SYNTECH_JCJAUD.ACTION_DATE,
SYNTECH_JCJAUD.ACTION_TYPE, SYNTECH_JCJAUD.JOB, SYNTECH_JCJAUD.PROJECT,
SYNTECH_JCPRJCT.NAME_SHORT, SYNTECH_PEPERSON.SURNAME,
SYNTECH_PEPERSON.FORENAME
FROM SYNTECH_JCJAUD, SYNTECH_JCPRJCT, SYNTECH_PEPERSON
WHERE SYNTECH_JCJAUD.COMPANY = SYNTECH_JCPRJCT.COMPANY AND
SYNTECH_JCJAUD.PROJECT = SYNTECH_JCPRJCT.PROJECT AND
SYNTECH_JCPRJCT.COMPANY = SYNTECH_PEPERSON.COMPANY AND
SYNTECH_JCPRJCT.MANAGER = SYNTECH_PEPERSON.PERSONNEL_NO AND
SYNTECH_JCJAUD.ACTION_DATE = Date() AND
SYNTECH_JCJAUD.ACTION_TYPE = 'Insert' AND
SYNTECH_JCJAUD.Project > '0000001'
GROUP BY
SYNTECH_JCJAUD.ACTION_DATE, SYNTECH_JCJAUD.ACTION_TYPE, SYNTECH_JCJAUD.JOB,
SYNTECH_JCJAUD.PROJECT, SYNTECH_JCPRJCT.NAME_SHORT, SYNTECH_PEPERSON.SURNAME,

SYNTECH_PEPERSON.FORENAME
ORDER BY SYNTECH_JCJAUD.ACTION_DATE DESC;"

The 'Joins' in Access are simply WHERE clauses in real sql. Try this out
and lemme know what happens.

Cheers,
Eric
 
Eric,

Once again many thanks for your reply, and the help on my code.

I will get my head around this and let you know how I get on.

Many Thanks
Elliott
 
Back
Top