Access Linked Table from SQL Shows #Delete

Question

I created this table:

CREATE TABLE [dbo].[dbo_Country]
(
    [Country] [nvarchar](100) NOT NULL,
    [ISO3166Code] [smallint] NULL,
    [CountryEn] [nvarchar](255) NULL,
    [Abriviation] [nvarchar](255) NULL,

    CONSTRAINT [dbo_Country$PrimaryKey] 
        PRIMARY KEY CLUSTERED ([Country] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Then I linked it to a MS Access database I try to open the table and see the information but see this:

Does anyone have a solution?

Spelling correction: it's **abbreviation** - not "Abriviation" — marc_s, Dec 30 '21 at 13:14

score 2 · Answer 1 · answered Dec 30 '21 at 13:44

2

#Deleted is normally shown when rows have been deleted in the underlaying database table while the table is open in Access. They may have been deleted by you in another window or by other users. #Deleted will not show initially when you open the table in Access. Type Shift-F9 to requery. The deleted rows should disappear.

answered Dec 30 '21 at 13:44

Stringeater

170
1
9

No this is not the reason always show this. Suppose that this is a single user database – Mehdi Shahgholi Dec 30 '21 at 22:13
That effect is new to me, I apologize. You may use the XEventProfiler in SSMS. When you open the table in Access, Access should first query all the values of the primary key column (`SELECT "dbo"."dbo_Country"."Country" FROM "dbo"."dbo_Country"`). Then it should fetch the first 10 rows by `exec sp_prepexec ...` and each following 10 rows by `exec sp_execute ...` but just as many as needed for the visible rows. This may give you a hint. – Stringeater Dec 31 '21 at 11:52
thanks for your comment I find that this cause by primary key. when the primary key of the SQL table is a nvarchar(x) type linking a table to MS-Access does not work properly. When the primary key is a numeric field it seems good but these are a data of a client and I cannot change them – Mehdi Shahgholi Dec 31 '21 at 12:31
If you cannot change them you cannot... If you find a way, you may consider adding a rowversion column to the table. This has helped me avoiding conflicts in many cases. – Stringeater Dec 31 '21 at 13:03

score 1 · Answer 2 · answered Dec 31 '21 at 16:55

Set a default of 0 for the number (ISO3166Code) (update all existing number column to = 0.)

Add a row version column (timestamp - NOT date time).

Re-link your table(s).

This is a long time known issue. With bit fields (or int) as null, then you will get that error. As noted, also add a timestamp column (not a datetime column).

Access Linked Table from SQL Shows #Delete

2 Answers2