Left Join Query won't run on all PC's

[GPM4663]

Hi Everyone,
I have the following query containing a left join:

SELECT LGDOCA.UNIQID, Trim([NARR1]) AS ItemComments
FROM (LGDOCB INNER JOIN (LGDOCA INNER JOIN LGDOCE ON LGDOCA.UNIQID = LGDOCE.DOCID) ON LGDOCB.DOCID = LGDOCA.UNIQID) LEFT JOIN LGDOCF ON LGDOCA.UNIQID = LGDOCF.DOCID
WHERE (((LGDOCA.DOCTYPD)="PSL"));

It sits on a network PC which has the same version and service pack as my PC (access 2003 SP3). When I run the query from my PC I get the results I expect but when I run it from the network PC I get no results!!

I have run the query from a number of PCs with 2003 and 2007 and it runs no problem. I cannot get the query to run on the Network PC that hosts the database and have no idea why. If I remove the left join I get some of the results but obviously this is not what I want.

Has anyone experienced this or have any ideas how I can solve this?

Many thanks

GPM

 

[Remou]

Are you using linked tables and are you sure that the links are up to date and to the right back end?

http://lessthandot.com

 

[GPM4663]

Hi Remou,
Thanks for you response, you are correct that all the tables mentioned are linked to another database. They are in fact linked to a parallax database using ODBC. I have checked and rechecked the ODBC settings and they are identical to what I have on my PC. I even went as far as uninstalling and reinstalling the ODBC software (easysoft) but it didn't work!

Any other thoughts?

GPM

 

[Remou]

No, I do not have any other ideas. I guess you checked the tables separately to see that they contain the data you would expect?

http://lessthandot.com

 

[kcmass]

Try writing a separate query to each involved table to make sure the connection is working and the data is there and you have rights to it. Then write queries to each join separately to ensure that matches exist and data is returned.

You say that you have run the query from a number of PCs and it works but when you run it of the "Network" PC it won't work.

Are the "PCs" workstations on the network? accessing the data through the network? and is the "Network PC" a network server?

When you try to run it from the "NetworkPC" do you use a separate/distinct sign in (different from your PC)?

 

[GPM4663]

Hi Everyone,
Thanks for all you input. I've done some testing and the problem appears to be with LGDOCF. If I change the query to

SELECT LGDOCA.UNIQID, Trim([NARR1]) AS ItemComments
FROM (LGDOCB INNER JOIN (LGDOCA INNER JOIN LGDOCE ON LGDOCA.UNIQID = LGDOCE.DOCID) ON LGDOCB.DOCID = LGDOCA.UNIQID) INNER JOIN LGDOCF ON LGDOCA.UNIQID = LGDOCF.DOCID
WHERE (((LGDOCA.DOCTYPD)="PSL"));

So that the join between LGDOCF and LGDOCA is just and inner join and not a left join then I get the results I would expect on any PC. I don't understand why.

LGDOCF is one of those tables that if you try to look at directly through the ODBC link you don't see any records - it needs to have corresponding records on another table

KCmass in answer to your question the "Network PC" in question is actually a server PC and runs windows server 2003, would this be causing the problem? It is logged on as administrator which is what i use to go on it and "try" to run the query.

thanks for all the help,

GPM

 

[kcmass]

Hi GPM

I would be surprised if the Server and the workstations were configured remotely the same. User permissions would certainly be different between a workstation and a server. Most places I am familiar with would not even put non server type application (ie MSAccess.exe) on a server. You often put the back end piece (a db with tables)out on a server but not the Access application. If people on workstations open the app out on the server you create a lot of unnecessary network traffic and probable license problems.

If you have to run it from there look at the versions and configuration of the ODBC driver, the tables actually linked to on both configurations and as I suggested before, run some more limited test queries.

Try something like this and see if you get any data back from LGDOCF:

SELECT LGDOCA.UNIQID, LGDOCF.DOCID
FROM LGDOCF INNER JOIN LGDOCA ON LGDOCF.DOCID = LGDOCA.UNIQID;

 

[GPM4663]

Hi Everyone,

Kcmass, I took your advice and started doing some test queries based on what you had given me. I slowly started to build it up, testing it at each change to make sure I was getting results. I ended up with the same query that wouldn't run earlier and it ran perfectly! I don't understand it. SO I've deleted the old query and replaced it with this new one. I have never come across a lone query becoming corrupt in access without the whole database becoming corrupt.

If anyone could shed some light on this I would love to hear it.

thanks for all your help.

GPM