471,624 Members | 1,966 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,624 software developers and data experts.

DAO and Access97 WHERE clause fails

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)
Jun 9 '07 #1
3 2053
On Jun 9, 10:17 am, "v.davis2" <v.dav...@cox.netwrote:
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.dav...@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
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
Jun 9 '07 #2
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)

Jun 9 '07 #3
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.

Jun 10 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Chris Dugan | last post: by
5 posts views Thread by JJM0926 | last post: by
1 post views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
1 post views Thread by ZEDKYRIE | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.