DAO and Access97 WHERE clause fails

[v.davis2]

Hi all,

I am attempting to use Access97 as the database to hold the results of a
python script. I seem to be able to make simple SELECT clauses work (like
SELECT * FROM TableName), but have not been able to figure out how to add a
WHERE clause to that (e.g., SELECT * FROM TableName WHERE myFieldName = 34)
This fails complaining that the wrong number of parameters are present.
I haved tried DAO36 and I have tried the ADO version with the same results.
Therefore I have to conclude it is my screwup!
Help in the forum or via email would sure be appreciated! (v.******@cox.net)

Here is the skeleton code:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.35')
sDBname = 'vpyAnalyzeDirectorySize.mdb'
sDB = 'c:\\documents and settings\\vic\\my
documents\\tools\\python25\\_myscripts\\'+sDBname
daoDB = daoEngine.OpenDatabase(sDB)

sSQL1 = 'SELECT * FROM T_Index2DirName'
daoRS = daoDB.OpenRecordset(sSQL1) # this works FINE and I can play
with the record set

#<snip>

hsDB = hash(sDB)
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB' # names are
all correct in mdb file
daoRStest = daoDB.OpenRecordset(sSQL3) # this FAILS, even though the
record is there

daoRS.Close()
Traceback (most recent call last):
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py" ,
line 310, in RunScript
exec codeObject in __main__.__dict__
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\_MyScripts\TestForPosting 01.py", line 14, in
<module>
daoRStest = daoDB.OpenRecordset(sSQL3) # this FAILS, even though
record is there
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\lib\site-packages\win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0.py",
line 523, in OpenRecordset
, Type, Options, LockEdit)
com_error: (-2147352567, 'Exception occurred.', (0, 'DAO.Database', 'Too few
parameters. Expected 1.', 'jeterr35.hlp', 5003061, -2146825227), None)

[John Machin]

On Jun 9, 10:17 am, "v.davis2" <[e-mail]:

Hi all,

I am attempting to use Access97 as the database to hold the results of a
python script. I seem to be able to make simple SELECT clauses work (like
SELECT * FROM TableName), but have not been able to figure out how to add a
WHERE clause to that (e.g., SELECT * FROM TableName WHERE myFieldName = 34)
This fails complaining that the wrong number of parameters are present.
I haved tried DAO36 and I have tried the ADO version with the same results.
Therefore I have to conclude it is my screwup!
Help in the forum or via email would sure be appreciated! ([e-mail])

Here is the skeleton code:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.35')
sDBname = 'vpyAnalyzeDirectorySize.mdb'
sDB = 'c:\\documents and settings\\vic\\my
documents\\tools\\python25\\_myscripts\\'+sDBname
daoDB = daoEngine.OpenDatabase(sDB)

sSQL1 = 'SELECT * FROM T_Index2DirName'
daoRS = daoDB.OpenRecordset(sSQL1) # this works FINE and I can play
with the record set

#<snip>

hsDB = hash(sDB)
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB' # names are
all correct in mdb file

Disclaimer: I've never used DAO.

That SQL statement appears to be incorrect/meaningless.
Don't you need something like
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = %d' % hsDB
?

You may need to read this (changing Office10 to whatever you have):
C:\Program Files\Common Files\Microsoft Shared
\Office10\1033\DAO360.CHM

[v.davis2]

Hello all.
Thanks for the help! John pointed out to me the flaw in my code:
Change:
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB'
to:
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = %ld' % hsDB
That did the trick. I had looked at the statement so often that it was
*obviously* correct.
John also pointed me to the DAO help file that I had not been able to find.
Dennis also pointed out the correction, but went on to educate me much more
on what I was trying to do.
BTW, I had searched extensivly on line for the answer before posting, but
was missing the obvious, stupid coding error.
Thanks to all for getting me back on track!
--Vic
"v.davis2" <v.******@cox.netwrote in message
news:Ok*********************@newsfe14.phx...

Hi all,

I am attempting to use Access97 as the database to hold the results of a
python script. I seem to be able to make simple SELECT clauses work (like
SELECT * FROM TableName), but have not been able to figure out how to add
a WHERE clause to that (e.g., SELECT * FROM TableName WHERE myFieldName =
34) This fails complaining that the wrong number of parameters are
present.
I haved tried DAO36 and I have tried the ADO version with the same
results. Therefore I have to conclude it is my screwup!
Help in the forum or via email would sure be appreciated!
(v.******@cox.net)

Here is the skeleton code:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.35')
sDBname = 'vpyAnalyzeDirectorySize.mdb'
sDB = 'c:\\documents and settings\\vic\\my
documents\\tools\\python25\\_myscripts\\'+sDBname
daoDB = daoEngine.OpenDatabase(sDB)

sSQL1 = 'SELECT * FROM T_Index2DirName'
daoRS = daoDB.OpenRecordset(sSQL1) # this works FINE and I can
play with the record set

#<snip>

hsDB = hash(sDB)
sSQL3 = 'SELECT * FROM T_Index2DirName WHERE iIndex = hsDB' # names are
all correct in mdb file
daoRStest = daoDB.OpenRecordset(sSQL3) # this FAILS, even though the
record is there

daoRS.Close()
Traceback (most recent call last):
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py" ,
line 310, in RunScript
exec codeObject in __main__.__dict__
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\_MyScripts\TestForPosting 01.py", line 14, in
<module>
daoRStest = daoDB.OpenRecordset(sSQL3) # this FAILS, even though
record is there
File "C:\Documents and Settings\Vic\My
Documents\Tools\python25\lib\site-packages\win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0.py",
line 523, in OpenRecordset
, Type, Options, LockEdit)
com_error: (-2147352567, 'Exception occurred.', (0, 'DAO.Database', 'Too
few parameters. Expected 1.', 'jeterr35.hlp', 5003061, -2146825227), None)

[stefaan]

I should point out that I don't do DAO (or ADO) -- and if I had to

code Python to access JET, I'd probably hijack a copy of mxODBC in order
to get a "sane" SQL interface.

I have successfully used the dejavu object-relational mapper (http://
projects.amor.org/docs/dejavu/1.5.0RC1/) to access MS ACCESS databases
recently.

Bestregards,
Stefaan.