ODBC Linked tables on SQL 2005 messed up (1 Viewer)

[dirkpitt]

Guys,

I have a problem I can't understand. You may have already experience this.

We have in our company Access db that were designed in version lower than our current (access 2007).
In some of those access db, users used some linked tables (through ODBC connection on MS SQL).

We needed to change their ODBC so that point to another database (same scheme = views instead of tables). We did some extensive tests and everything worked fine.

Now from time to time (I was not able to find any pattern), a query that use N linked tables (and NO access tables) returns funky data. It seems that the Engine that pass the query to SQL (the Jet engine I guess) generates stupid SQL statements and as a results multiply by x the records or miss some records.

I did a compact and repair. Still.

I had to delete the linked tables and recreate the link (and did not specifiy 'unique record identifier').
then the resulys were correct.

Any idea why ? Can it be a pb from 2007 ?

Thank you in advance

 

[mihabaki]

do you have any (long) text fields on your MSSQL backend? If so, try changing to nvarchar()

also any numeric field types on your MSSQL that are "bigint" or "int(>11)" could cause your linked tables to Access to not show correctly

m