SQL query and MS Access query returns different data

views:

answers:

+1 Q:

SQL query and MS Access query returns different data

Hi there

I use MS Access as a front end to my MS SQL server.

When I use a SQL query in ms Access that is (in my opinion) equivalent to a query I run from the SQL server, Access returns different data. The pictures below illustrate the difference. I use Access 2003 and I have connected the Access table to the SQL server (2008) through ODBC.

Anybody got an idea how to fix this??

Thanks in advance

/Iljitj

The SQL and Access Query and result:

(It's the "Projekt" column that is different)

http://i.imagehost.org/0218/SQL_access_query_and_result.jpg

+1 A:

I would just stop using Access. It appears to be very broken. First off let's find out what select statement access is really using.

In SSMS (SQL Server Management Studio) go Tool -> SQL Server Profiler.

Attach to the same database then see what Access is doing. File -> New -> connect -> run.

NitroxDM 2010-04-21 15:44:31

+1 for abandoning Access.....

marc_s 2010-04-21 15:48:05

First off: I did the trace, but I'm not sure what to make of it. The link is a ss of what Access did:http://j.imagehost.org/0808/trace.jpgSecondly: I agree that Access i crap, but I'm not much of a programmer, I know some VBA and a little C#. Is there another application or something like Access I can use as front end for my SQL server instead of Access?

iljitj 2010-04-21 16:03:28

Is CVRNr the primary key on the table? Can you post the table schema?

SqlACID 2010-04-21 16:28:16

Looking at the trace it's like you said it's the same table and db. But access is asking for just the one row. How about those primary keys?

NitroxDM 2010-04-21 18:08:11

-1 for abandoning Access. Only the ignorant would suggest that as a response to this question.

David-W-Fenton 2010-04-22 03:54:22

The trace is doing something odd that I don't understand, and suggests to me that the table perhaps doesn't have a PK, or proper indexing. For Access to be able to use tables from ODBC data sources, they have to have a primary key. Also, I've found it helpful to make sure every table has a timestamp field. This helps Access bound forms refresh data appropriately. What happens when you change the criteria to WHERE CVRNr='10598184'? It does look like there's redundant data there, given that Kunde and CVRNr seem to be dependent data (and that's suggested by the trace, too).

David-W-Fenton 2010-04-22 03:59:52

+1 A:

Ok, I see a couple of things...

Do you have a Primary Key setup for the table on MS SQL? That might be confusing MS Access.
The boolean column do have different values, but I think that's because MS Access likes to represent them differently than the MS SQL. That you're going to have to live with.

CodeSlave 2010-04-21 16:44:29

The results in the Projekt column don't match, and I think that's the actual problem, not the standard Boolean conversion.

David-W-Fenton 2010-04-22 03:57:51

+2 A:

Recreate Access linked table. Sometimes Access internal definitons go bad, especially when there are some changes in linked server data structures, tables/columns are recreated/altered or similar.

BTW, Access itself is certainly not bad, just it is meant for simple desktop 'databasing'. It has many limitations and some quirks, but it is quite usable.

Arvo 2010-04-21 17:02:07

"Internal definitions" do no "go bad," it's just that metadata stored in the table links can become outdated if the target table/view is altered. It's bog-standard to deleted and recreate all links to tables/views when any of them are changed on the server.

David-W-Fenton 2010-04-22 03:52:50

"[Access" is meant for simple desktop 'databasing'". It's true that Access supports that, but it is fully extensible to cover a large proportion of all types of database applications out there. There is nothing at all in the question that suggests there's something inherent to the poster's situation that makes Access unsuitable as front end.

David-W-Fenton 2010-04-22 03:53:52

Look, I'm not an english speaker; I can't produce statements like 'metadata stored in the table links can become outdated if the target table/view is altered' on the fly. 'Go bad' of course meant the same, just expressed in much shorter (and apparently but unfortunately wrong) way ;)

Arvo 2010-04-22 08:40:25

"Go bad" is not the same as "no longer accurate". If you changed the server-side objects, you may need to change the client-side interaction with that. Would wouldn't say code that uses a field that you deleted from the target table has gone bad -- you'd say the code needs to be updated to reflect the field definition. Likewise, linked tables need to be deleted and recreated to reflect changes to the server-side objects they are linked to.

David-W-Fenton 2010-04-23 02:34:55

+2 A:

Okay I solved the problem. Apperently Access won't do anything without a unique identifier. Since I didn't have that in my table, Access used CVRnr as identifier, but since it isn't unique, Access only pulled the first row and dublicated it, hence the "incorrect" data.

I added the unique identifier, and linked it correctly to Access and now it works.

Thanks a lot guys for all your help and suggestions, I don't think I would have ever figured out the cause was the identifier if some of you haven't suggested it.

You saved me ;)

iljitj 2010-04-22 09:02:50

ansaurus

tags:

views:

answers:

SQL query and MS Access query returns different data

related questions