Geek Answers Handbook

Lines showing as #DELETED

I have data rows in a table showing how #DELETED on one computer when using Access, but they are good both in the SQL database and on other computers using Access. It seems to be only the last 200 lines. Access 2007 and the ODBC MSJet drivers look the same and newer on every computer. One suggestion was to change any PK or FK to int, but they already exist.

Any ideas for a fix for this?

+7
source share
10 answers

This happens when the primary key value of the table exceeds the range supported by MS Access, usually if you use the "BigInt" type in SQL Server, if you only want to read the data, then just create a "binding", shot "for the table, and all the rows will display correctly since the snap-shot does not need to read all indexes.

If you need to update the data in these rows at any time, I suggest using the ADO recordset instead.

+10
source

(18,0) bigint SQL. MS Access , SQL Server. SQL 2008R2 Access 2010, "#DELETED" bigint PK.

+4

Access front end SQL Server 2000, 2008 R2, 2014 , . SQL Server 2014 Windows 7 (64-), Access 2010 #Deleted " ".

, Windows 7 (64-) . , . SQL Server 2014 , Native Client 11.0, ODBC DRIVER = SQL Server Native Client 11.0 VBA . #Deleted .

"" , , "" , #Deleted, / Native Client 10.0. 10.0 Microsoft, , ODBC. DRIVER = SQL Server 10.0.

, #Deleted problems.

+3

, SQL ROW_NUMBER(), int. ROW_NUMBER() - int64 (bigInt).

+3

, .

MS Access, . , Integer , VARCHAR. ( MS Access), "#DELETED" . , "ALTER VIEW" sp_refreshview "VIEW_NAME".

. , - .

+2

#DELETED, , bigint. , , , CAST int ( , , , ).

+2

, SQL 2017 ( SQL 2008R2). DSN ODBC ( SQL 11), .

+2

BigInt Access.

File > Options > Current Database > Data Type Support Options

, .

Access Support BigInt

+1

, #Deleted, ( ), ... . , , , , , (int (11)), . BigInt ( odbc bigint ).

ODBC 5.03.13 . 5.03.04 msi, , . , , , ! , , , - , ( ).

0

SQL, , ROW_NUMBER() - bigint, MS Access #Deleted, , . , . ..

CAST((Row_number() OVER(ORDER BY AllColumns.AnyColumnNameSuchAsDateColumn DESC)) AS INT) AS 'id', AllColumns.* FROM (SELECT AnyColumnNameSuchAsDateColumn, ...etc FROM YourTableName WHERE YourCondition) AS AllColumns

0

More articles:


All Articles