473,238 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 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 2149
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Chris Dugan | last post by:
Hi, has anybody come across a solution to running an Access97 database on Windows XP. The situation I have is an Access 97 database that runs perfectly from Win95/98 clients running Access97, the...
1
by: Mario Crevits | last post by:
My name is Mario Crevits, I'm from Belgium (Roeselare) and I'm working with Access97 for several years now. We are in an Access97-2000 migration project. I'm writing a wizard for the end-users to...
12
by: Tony Ciconte | last post by:
We are evaluating the prospect of integrating and/or using Crystal Reports with some of our current products. Some of these are still in Access 97 and are running well. Since we cannot include the...
0
by: lesperancer | last post by:
I've got a sql server view that returns data, as a linked table to access97 (qryPlannedPurPT) this link table is used in an access query (qryPlannedPUR) that uses all of the linked table fields...
1
by: IamKJVonly | last post by:
I have office 97 which includes access97 and have built many access97 databases and use VB4 as the front end. I have just gotten a new computer which has 1 gig of memory on it and when I try to...
5
by: JJM0926 | last post by:
I need some help with sorting some part numbers that are alphanumeric. I have a table with a part number field which are in the following format--sw1000, sw1200, sw2000, sw2600, sw3000, sw21000,...
2
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd...
0
by: Roger | last post by:
can anyone explain this ? originally using access97 with a linked table to an mdb backend, to create a worksheet using SELECT * FROM qryIPS WHERE location = 999 and it works fine with either...
3
by: Ramchandar | last post by:
Hi, I am creating reports using VBA code. I have the same query in a querydef residing both in Access97 and Access2003. The result of this querydef is then moved to a table in Access97 and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

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.