Solved

Query result incorrect when using SQL-server tables (ODBC)

Posted on 2001-07-24
28
251 Views
Last Modified: 2009-12-16
Hi,

We are using Access and SQL-server. We have found a problem in the result of the queries. I will describe the situation:
We create a query that uses two tables of SQL-server via ODBC. The link between the tables is an outer join because not all items of the first table exists in the second table. The result is that only the items that exists in both tables will be displayed (strange!! It should be all items of the first).

We are sure that the query is correct and that there is a problem with the system, ODBC-driver or something because if we run the same query on another computer and the result is correct (all items of the first table).

Maybe it is an already known bug but we can't find a solution now.

Johan
0
Comment
Question by:JohanT
  • 10
  • 9
  • 3
  • +5
28 Comments
 
LVL 1

Expert Comment

by:yitz99
ID: 6311521
Confidence aside, it would be a good idea to post your query here for reference.
0
 

Author Comment

by:JohanT
ID: 6311609
Here is an example of a query that isn't working correct on some systems:

SELECT Table1.Field1
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
GROUP BY Table1.Field1;

So table1 will link to table2. The result should be at least the same number of items in table1.

Johan
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6311621
Hi JohanT,

If Table1.Field1 is NOT a unique field (i.e. it can contain DUP values through out the table), then you will definatly not get all the records in table1, as you are using the GROUP BY clause.

Is it possible that your intention was to use the ORDER BY clause instead?

Nosterdamus
0
 
LVL 3

Expert Comment

by:casassus
ID: 6311629
With a left join, you have all the record of the table1 and record from table2 if join is possible.

For example :

Case 1
Table1.Field1

1
2
3

Table2.Field1
1
3

Your query give you
1
2
3

Case 2
Table1.Field1

1
2
3

Table2.Field1
1
3
4

Your query give you
1
2
3
0
 

Author Comment

by:JohanT
ID: 6311725
Thanks for your comments,

The given query is just a simple example of a query that is giving different results if I run them on different machines. It is not logical query but is showing that we are having a problem (it works correct on 8 systems and incorrect on 4 systems of the systems we checked).

Be aware that I know that my query is correct.

JohanT
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6311746
Are all machines using the same MDAC version?

Are all machines using the same OS? If not, then on what OS versions it runs OK and on what OS it wont?

Are all machines using the same MS Office environment (Version, SR etc.)? If not, then on what MS Office versions it runs OK and on what MS Office it wont?

Nosterdamus
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6312044
are u deffinately linked to the correct sql tables ?
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6312098
this may be a silly question, but why don't you create a view on your SQL server that does the join for you?

just a thought...

the only issues i've run into that are silly like what you are experiencing with access is when sorting views in the results window. a lot of times the results aren't sorted the way i'd expect, but i've never seen a loss of data.

an alternative would be to use a passthrough query from the client PC. this will often times be much faster as ONLY the returned records are transferred from server-client and it's guaranteed to work on all platforms because the server is doing ALL the work...

another thought...

dovholuk
0
 

Author Comment

by:JohanT
ID: 6312295
We have installed the MDAC_Type 2.5 on all the machines I used for testing.

Most of the systems are using WinNT and some Win2000. The machines with Win2000 don't give a problem, only some of the WinNT machine do.

On the WinNt machines we are working with Access '97. We thought that there was a problem with the dutch version of
Access because all the machines with the english version were working ok. But we completely reinstalled a machine and it is still not working.

Any idea?

Johan
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6312359
can u run make table queries and run the query against the new tables
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6312363
Was your MS Access app developed under Access 97 or Access 2000?
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6312543
JohanT,

have you had a chance to try out the ideas i posed in my previous post?

do you need any help in this direction?

dovholuk
0
 

Author Comment

by:JohanT
ID: 6313093
Hello,

Thanks for all your comments but it is not where I'm searching/asking for.
We have a database (read 1 database) that is working correct on most of the WinNT machines but not on all machines. It's a database designed and used under Access '97 and with the same MDAC_Type installed.
There should be in some way a difference between the machines but I really don't know what the difference is.

Johan
0
 
LVL 9

Expert Comment

by:perove
ID: 6316190
My first guess in these scenarios when equal things work on one machine but not on another, is that there are someething with international settings, I see that you is not from US, and -as me- have (or will have) struggle with this thing. MS does not take to good care of us not-US developers..

Is there some date, number etc.. criteria in qour query?
Is there any difference in the OS language?
Is there any difference in the international settings (date, number, keyboard...etc)

perove


0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6316320
hi JohanT,

Just to add to perove's comment:

If you are quering the DB for dates (e.g. WHERE mydate = #06/07/2001#) then notice that you MUST use a US date format in the SQL statement.

In the WHERE clause example, the SQL statement will search for mydate = 'June 7'th 2001' (mm/dd/yyy) and NOT for 'July 6'th 2001', as expected in European format (dd/mm/yyyy).

Nosterdamus
0
 

Author Comment

by:JohanT
ID: 6316631
I checked the regional settings. The setting is English(US), so this should be ok.
The language of the operating system is the same (also Access) and we are not linking on a date or a time. We just want to link an Id or a text field.

Johan
0
 
LVL 4

Expert Comment

by:mcmahon_s
ID: 6316793
Hi Johan,

What version of SQL server (and service pack) are you using. I have had problems with things like this where the client PC has an older version of some of the SQL dll's than those that are shipped with the SQL server service packs.

Your problem sounds similar given that all the WIN 2000 PC's seem to be working and they would have the latest versions.
0
 

Author Comment

by:JohanT
ID: 6317538
We are working with SQL-server version 7 with SP2. On the WinNT machines with the problem there no SQL-Client installed but there is a Win95 machine with the SQL enterprise manager and this machine has the problem also.

If that is really the problem, why isn't it working on the Win95 machine?

Johan
0
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 100 total points
ID: 6318008
Hi JohanT,

Does this apply to your problem?
http://support.microsoft.com/support/kb/articles/Q171/9/48.asp

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6318047
If it does, then you might find this article (ACC: How to Create an SQL Pass-Through Query in Code) helpful:
http://support.microsoft.com/support/kb/articles/Q112/1/08.asp

Hope this helps,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6318054
Sorry for the multiple posts, but this article (Create the ODBC connection string for an SQL pass-through query) could be helpful as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/achowUseOdbcConnectionStringBuilderCreateConnectioS.asp
0
 

Author Comment

by:JohanT
ID: 6322154
Hello Nosterdamus,

Thanks for your links. It could be that your pointing into the correct direction but there are some points (strange) left. It could be that subqueries with ODBC-linked tables are the problem, I wasn't thinking about that. But we cannot solved that (I think) because we are using them in forms.
There is still a point that I don't understand. There are some WinNT machines that work incorrect but also some that work correct. We tried to check the ODBC-driver version number, but we couldn't find a difference.
Any Idea why there is a difference? Could we get all the systems working in the correct way?

Johan
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6322538
does each machine have its own copy of the access database?


0
 

Author Comment

by:JohanT
ID: 6333325
Sorry that I let you waiting so long.

We have a database on our network and if anyone needs the database, he opens the database.
So for each machine it is the same database with the same data but with different results.

Johan
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6333336
Hi JohanT,

Are all the NT machines use the same SP?

If I'm not miskaging, the latest and most stable SP is 6. Micro$oft reccomends to upgrade to this one....

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6333340
GRrrrrrrr,

If I'm not miskaging

Should be:
If I'm not mistaking

;-)
0
 

Author Comment

by:JohanT
ID: 6340795
You were pointing into the correct direction Nosterdamus!
Solution: use the english version of office (some were dutch but also the english version was giving problems) and install SR2.

Thanks!!

Johan
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6343444
Glad I could help,

Nosterdamus
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now