By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 895 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Query works differently on two PCs

P: n/a
I have an Access 2003 database with a query that works fine on my PC
but does not work on another PC. My PC has older versions of Access
installed as well as 2003 - the other PC only has Access 2003.

The query is as follows:

SELECT tblOrganization.strOrgName, tblPerson.strNameL,
tblPerson.strNameF, tblPersonFCL_Courses.lngIDPerson,
tblPersonFCL_Courses.strHOTest, tblPersonFCL_Courses.strCert,
tblPersonFCL_Courses.ysnProcessed, tblStaffCert.IDPerson,
tblStaffCert.strCert
FROM (tblOrganization INNER JOIN tblPerson ON
tblOrganization.IDorganization = tblPerson.lintFKOrganization) INNER
JOIN (tblPersonFCL_Courses LEFT JOIN tblStaffCert ON
(tblPersonFCL_Courses.strCert = tblStaffCert.strCert) AND
(tblPersonFCL_Courses.lngIDPerson = tblStaffCert.IDPerson)) ON
tblPerson.IDPerson = tblPersonFCL_Courses.lngIDPerson
WHERE (((tblPersonFCL_Courses.lngIDPerson) Not In (select lngIDPerson
from tblPersonFCL_Courses where strCert="ML" and ysnProcessed =
false)) AND ((tblPersonFCL_Courses.strCert)="ML") AND
((tblPersonFCL_Courses.ysnProcessed)=True) AND
((tblStaffCert.IDPerson) Is Null));

When I run this query on my PC, it returns one record (which is
correct).

When we run the query on the problem PC - it returns a different
record, but it is in some sort of loop because we get hundreds of
records (all the same person - yet a different record from what
appears on the other PC with the same data files). We have to control
break to get out of this query.

I'm trying to resolve this remotely - I've got a fresh copy of both
the front end and back end that is on the problem PC... I can't
recreate any problems - all works great on mine.

We have tried:
compact/repair
new blank database for both front and back end, importing all objects
autoname correct is turned off
double-checked all library references and order of same (am using dao
3.6, excel and outlook libraries along with the standard vba, access
11, OLE automation and Active X Data objects)
We also tried converting it to Access 2000 format on both PC's - the
converted one works great on mine, does not work on the client PC -
returns the same error.

Any ideas of what else to check??
Thanks in advance.

Jan 31 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Since the query works on one computer an not on another (assuming identical
data), the first thing to look for would be a difference in versions.

Locate msjet40.dll (typically in windows\system32.)
Right-click it in the Windows Explorer, and choose Properties.
On the Version tab, what is the minor version?
It should be at least 4.0.8015.0.
Anything that starts with an 8 or 9 is fine. (It varies with your version of
Windows.) But if the minor version starts with 7 or less, download the
service pack from:
http://support.microsoft.com/kb/239114

If that does not solve the problem, and you are quite sure that the field
types and sizes match correctly, there is a potential issue with the yes/no
field in the WHERE clause and the outer join. Try:
AND (CBool(Nz(tblPersonFCL_Courses.ysnProcessed,False) ) = True)

There is a whole class of bugs in Access based on the fact that JET cannot
handle nulls in yes/no fields, despite the fact that outer join queries
regularly yield nulls in boolean fields. Sometimes it crashes. Sometimes it
gives wrong results. Sometimes it gives nonsense errors as in this example:
http://allenbrowne.com/bug-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cindy" <ck*********@yahoo.comwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
>I have an Access 2003 database with a query that works fine on my PC
but does not work on another PC. My PC has older versions of Access
installed as well as 2003 - the other PC only has Access 2003.

The query is as follows:

SELECT tblOrganization.strOrgName, tblPerson.strNameL,
tblPerson.strNameF, tblPersonFCL_Courses.lngIDPerson,
tblPersonFCL_Courses.strHOTest, tblPersonFCL_Courses.strCert,
tblPersonFCL_Courses.ysnProcessed, tblStaffCert.IDPerson,
tblStaffCert.strCert
FROM (tblOrganization INNER JOIN tblPerson ON
tblOrganization.IDorganization = tblPerson.lintFKOrganization) INNER
JOIN (tblPersonFCL_Courses LEFT JOIN tblStaffCert ON
(tblPersonFCL_Courses.strCert = tblStaffCert.strCert) AND
(tblPersonFCL_Courses.lngIDPerson = tblStaffCert.IDPerson)) ON
tblPerson.IDPerson = tblPersonFCL_Courses.lngIDPerson
WHERE (((tblPersonFCL_Courses.lngIDPerson) Not In (select lngIDPerson
from tblPersonFCL_Courses where strCert="ML" and ysnProcessed =
false)) AND ((tblPersonFCL_Courses.strCert)="ML") AND
((tblPersonFCL_Courses.ysnProcessed)=True) AND
((tblStaffCert.IDPerson) Is Null));

When I run this query on my PC, it returns one record (which is
correct).

When we run the query on the problem PC - it returns a different
record, but it is in some sort of loop because we get hundreds of
records (all the same person - yet a different record from what
appears on the other PC with the same data files). We have to control
break to get out of this query.

I'm trying to resolve this remotely - I've got a fresh copy of both
the front end and back end that is on the problem PC... I can't
recreate any problems - all works great on mine.

We have tried:
compact/repair
new blank database for both front and back end, importing all objects
autoname correct is turned off
double-checked all library references and order of same (am using dao
3.6, excel and outlook libraries along with the standard vba, access
11, OLE automation and Active X Data objects)
We also tried converting it to Access 2000 format on both PC's - the
converted one works great on mine, does not work on the client PC -
returns the same error.

Any ideas of what else to check??
Thanks in advance.
Jan 31 '07 #2

P: n/a
Hi Allen -
The dll's on all machines are up to date. I modified the boolean
field as indicated, but the problem persists. I'm going to get around
it by using temp tables to denormalize - and in the meantime have
decided that I'm swearing off Access booleans!!! Many thanks for your
time.

The inconsistencies sure do seem to be getting worse though - sounds
like I've got to do some more planning ahead to make sure I'm not
going to hit this again!

Cindy

Feb 6 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.