Hash Deleted and autoincrement ID by 2 not 1

Question

Hash Deleted and autoincrement ID by 2 not 1

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Matt Miller (#4) (9/15/2014)
the #deleted marker is there only as a reminder from Access' ADO or ODBC connector that you attempted to insert something. It's flagged that way to denote that something that was received back from the connection no longer matches what was in memory/local. You could see it if 10 people open the same form, and someone else wiped out a record while you're vewing the list (and any number of other scenarios like that).
It's visual only - doesn't get persisted anywhere.

(sorry for the multiple posts - it's been a few years since I really dabbled in Access).

One more - if you were getting truly spurious "duplicate" errors (i.e. not due to duplicates in the unique column), you might get around that with a new column in AQS being filled with using the TIMESTAMP function in access (which will naturally use it as the rowversionID and not lose its place.

Don't think it will help in your case though.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 2

Lynn Pettis (9/15/2014)
Greg Edwards-268690 (9/15/2014)
I created a simple table in SQL Server 2012
Then a new Access db
Then linked the table by importing and choosing the linked option
Right clicking on a row (on the gray to the left), select Copy
Then right click on the gray to the left of new row, then select Paste
Then arrow down
Works just fine, leaving out the id, and incrementing as expected.
So I can't replicate the issue.
Did you put a unique constraint on any of the columns and try to copy a row of data where there was unique data in those columns?

Doesn't make sense to copy a row of data with unique constraint.

Guess I was assuming that the id / auto increment was the unique one, which Access ignores.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Greg Edwards-268690 (9/15/2014)
Lynn Pettis (9/15/2014)
Greg Edwards-268690 (9/15/2014)
I created a simple table in SQL Server 2012
Then a new Access db
Then linked the table by importing and choosing the linked option
Right clicking on a row (on the gray to the left), select Copy
Then right click on the gray to the left of new row, then select Paste
Then arrow down
Works just fine, leaving out the id, and incrementing as expected.
So I can't replicate the issue.
Did you put a unique constraint on any of the columns and try to copy a row of data where there was unique data in those columns?
Doesn't make sense to copy a row of data with unique constraint.
Guess I was assuming that the id / auto increment was the unique one, which Access ignores.

Just going by what the OP posted.

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 4

I think it is very normal to copy an entire record given I just want to change the name of the unique-constraint column instead of having to insert all the values from scratch.

So it's not normal to have the #deleted happen after changing the duplicate name to a non-duplicate one .

I still cannot find a good explanation or workaround . Last thing attempted was doing a form requery which works but I can still see the #deleted record quickly appear then disappear which doesn't look good.

Any help with explanations/better workaround is much appreciated.

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 5

I am the only user manipulating the form, there are no triggers on the view bound to the form - just an instead of delete trigger in 2 of the base tables.

I am still not able to find a really good and specific explanation to this.

At first thought, I would say I am inserting a record and the view is not yet refreshed with this new data however #deleted does not happen when I insert values for all of the records' columns

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 6

In a datasheet view, until you press enter, it does not submit the insert.

You are able to edit the column in question, then press enter.

In reality, auto increment is just ensuring a primary key for data integrity.

Over time, records are sure to be deleted anyways, so gaps will naturally occur.

Both SQL Server and Access are doing what is expected.

And the message you get initially implies you tried to break the rules of the constraint.