Have Geek Brain

SQL query and MS Access query return different data

I am using MS Access as front-end for my MS SQL server.

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

Has anyone figured out how to fix this?

Thanks in advance

/ Iljitj

SQL and Access query and result:

(This is the "Projekt" column which is different)

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

+2


a source to share


4 answers


Ok, I solved the problem. However, Access won't do anything without a unique identifier. Since I didn't have this in my table, Access used CVRnr as the identifier, but since it is not unique, Access only pulled the first row and printed it out, which means the data is "invalid".

I added a unique ID and bind it correctly to Access and now it works.



Thanks so many guys for your help and suggestions, I don't think I have ever figured out if the ID was the reason, if some of you haven't suggested it.

You saved me;)

+3


a source


Restore the table associated with the access. Sometimes access to internal definitions is impaired, especially when there are some changes in the structures of the linked servers, tables / columns are recreated / modified or similar.



By the way, access itself is not so bad, it is just intended to simply "denature" the desktop. It has many limitations and some quirks, but it is quite applicable.

+2


a source


I would just stop using Access. It seems to be very broken. First of all, let's find out what kind of selection selection access is actually being used.

In SSMS (SQL Server Management Studio) select "Tool" → "SQL Server Provider".

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

+1


a source


Okay, I see a couple of things ...

  • Do you have a primary key setup for a MS SQL table? This can confuse MS Access.
  • Boolean column has different meanings, but I think that because MS Access likes to represent them differently than MS SQL. That you will have to live.
+1


a source






More articles:


All Articles