Need to delete a MS SQL Server row that displays as #Deleted

It sounds like when you linked the table, a incorrect unique ID was used. Double check the primary key on the main table, as well as the linked table. 

Anders.. the table has 950,000 records.. two of them are showing up this way... this was actually a common problem with Access tables... especially with tables that used memo fields.. I’m wondering whether the records were corrupt when they were inported... regardless I cans seem to delete them.  the primary key is unique

Because of the way JET works with ODBC, #Deleted can be from a number of issues.

Not having a unique key as Ander's said is one.   Another is not having a Row Version/Time Stamp field.   Add one if you don't have it.

 Also you want to avoid bit fields that allow nulls.   Also any float values if you can.

 Finally, watch out for triggers that might update the record.

Jim.

I have a field called RecordCreatedDate which is converted by MS SQL to datetime2(0),null)
Does this meet the requirement you mentioned?

I also have a number of bit fields that are setup as FieldName (bit,null) in SQL server.
These are field that I have used to set flags.  Three are at least a half dozen of them, although the field is only used in specific processes.

Should I change them to not allow nulls.  It will take some time but is possible.

Add a time stamp/row version field first and see if that clears up the problem.

With JET and ODBC there are two issues:

1. Determining you have the correct record.
2. Concurrency (has the record been changed).

When JET goes to update/delete a record, it must reselect it.   If anything messes with the key value (trigger changes something or a key field is based on a float), JET may not be able to re-locate a record and will throw up #Deleted as a result.

Second is the concurrency.   Without a rowversion field, JET needs to scan all the columns to figure out if anything has changed since it fetched the record.   For bit fields and floats, that's a problem as JET will think the record has changed.

Using SQL with Access:

1. Use a RowVersion field in every table.
2. Avoid bit fields if possible (use a small init).  If you cannot, don't allow nulls.
3. Avoid key fields based on floats, and triggers that might modify any of the key fields.

Jim.      

and FYI, for some SQL/Access tips, check out "Best of both worlds" here:

http://www.JStreetTech.com/downloads 

Jim

I'll check the link as soon as I finish this post.
I noticed there is a field called SSMA_TiemSteapm (timestamp, not null)
It appears as the the SQL Server Migration Assistant created this field.
My primary key field is based upon two fields... an int and nvarchar field.. neither of which all nulls.

if I perform a select query from Access using the linked table:
Select * from ComponentMaster where ID=617995 all fields show as #Deleted

if use exactly the same query from SSMS the record with the correct data is displayed.

<<I have a field called RecordCreatedDate which is converted by MS SQL to datetime2(0),null) >>

I just found out that this is a problem with a new feature, which should now be turned off.

If you delete the table and re-link, see if the field gets mapped as a DateTime (not DateTime2).

Jim.

Thanks for the link to Best of Both Worlds, Anders.

Jim, if you include a RowVersion field in a table, you don't need to avoid using nullable Bit fields or floating point numbers. RowVersion will take care of them.

So, you need to:

include a RowVersion field in the table
-- OR --
avoid using nullable Bit fields and floating point numbers

Most developers feel that the downsides of using RowVersion are minor enough that they might as well always include them.  Then they don't need to worry about the fields that are problematic for concurrency.

Cheers,
Armen Stein

Yes, I could have been clearer that it's an either or, but for me it's standard practice to do both in case I would ever want to take RowVersion off.

Jim.

I'm very confused.  Is RowVersion a Column Name or a Data Type?
So I have a Column named SSMA_TimeStamp which is Data Type Time Stamp.  Does that meet the requirement?

RowVersion is a data type.  But it has an older name of TimeStamp, which has been deprecated.  The shiny new name for it is RowVersion.

The name of the field containing a RowVersion can be anything you want.  We often just call it RV.  You don't actually use it for anything - you don't even need to include it in your Access queries, forms or reports.  Access and ODBC automatically use it for concurrency checking regardless.

Thanks for that clarification, Armen.  So in my case, the Migration Assistant created a field called SSMA-TimeStamp with is data type TimeStamp... so then I do not need to  create a RowVersion field and SQL Server 2017 is using the old, not so shinny, name "TimeStamp".

I still need to relink as suggested by Jim.. I'm very confused why I get different results in Access and in SQL server with the same query!..

I'll report back....

Yes, SSMA is still using the vintage naming - they should really stop doing that.  RowVersion was introduced, and TimeStamp was deprecated, in SQL Server 2008!

https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver15#:~:text=The%20timestamp%20syntax%20is%20deprecated,that%20currently%20use%20this%20feature. 

Thanks for the link, Armen...

I'm having a new problem... I deleted the link as suggested so I could recreate and solve the RecordCreatedDate problem... but now that its been deleted I can not recreate.. because I have since migrating.. exceeded the indexe max of Access...

I wasn't sure what the proper etiquette is as this is a new problem, so I create a new thread.
https://www.experts-exchange.com/questions/29192938/Unable-to-link-SQL-table-to-MS-Access-Front-End.html 

Are you using a DSN for your connection?

my connection string is:

ODBC;DSN=ComponentMasterSQL-1;UID=MyAdminAccount;PWD=MyPassword;APP=Microsoft Office;DATABASE=MyDatabaseName;Network=DBMSSOCN

Thank you for your help.

The problem is solved.  I was able to locate and delete the records, and the problem when away.
Thank you again.