sql server odbc: Using Microsoft Access (2003 and 2007) to link to tables on a SQL 2005 SP2
server is working most of the time. On some particular tables (MS Dynamics
AX database) the results of the linked table for all rows and columns is
#Deleted. It has the correct record count and importing the data or using a
pass-through SQL query will show the data correctly, it's only the linked
table that has this issue. We've tried both "SQL Server" and "SQL Native
Client" ODBC driver connections with the same results.

I've seen reference to this in KB articles that mention an upgrade to the
MDAC components but we're on the latest version already. Also seen reference
to Unique indexes with null values - not the case here. The table in
question is a simple address table with 52 columns, three indexes (one
unique), and works perfectly fine in the Dynamics AX environment or through
SQL Server Management Studio. It only seems to be Access that has this issue.

Help is appreciated.

Nate

check out this

http://www.utteraccess.com/forums/showthreaded.php?Cat=&Number=1441905&p
age=&view=&sb=5&o=&vc=1

Thank you for this link. The key was this repsonse:
----------------------------
Do these tables have a primary key of data type BIGINT? I was receiving the
same #error on certain tables and it turns out Access can't handle tables
with a primary key column of BIGINT. These are some possible work arounds for
that scenario:

1) Change the datatype of the primary key to INT.
OR
2) Create a view of the table using "CAST as INT" for the BIGINT field.
----------------------
While I couldn't do a cast as INT because INT wasn't large enough to store
the value, I was able to CAST as FLOAT and make it work. I don't plan to
update the data in that primary field ever, so I'm not too nervous about this
change. Is there any reason this could cause issues if the BIGINT value is
converted to a FLOAT?

Thanks again,

Nate


[quoted text, click to view]