0

This is similar to another question and I have given it the same name. But my situation is a bit different.

The first question for reference: Access Linked to SQL: Wrong data shown for a newly created record

I have an Access front end linked to tables in SQL Server. For all relevant tables, there is an autonumber (int with Identity Specification) as Primary Key. About half of the linked tables have the following issue, the others do not, despite being set up similarly:

When adding a new record to the table, the record is inserted in the SQL database, but then in the access front end view, be it a table or form, the added record is filled up with data of another record.

In the other question, it was explained that Access is querying SQL Server with @@IDENTITY. I saw the same thing in a trace. In my case it tries SELECT @@IDENTITY twice, then attempts to pull the new record with a sp_prepexec generated SQL that I can't read, and consistently gets the wrong one, in certain tables, not in others, which are set up basically the same.

The wrong record being returned seems to be an earlier autonumber in the table, and if I do it several times in a row, it returns a series of autonumbers in sequence, for instance, 18347, 18348, 18349. (These are the incorrect autonumbers being displayed, along with all data from their records, instead of the newly created record.) But if I wait a few minutes, there will be a gap, it might return 18456 next, for instance.

Refreshing does bring the correct record into view.

The autonumber fields do show up in Access design view as Primary Keys. The Access front end is an .mdb file. We are using Access for Microsoft 365 MSO 64 bit.

eep
  • 53
  • 7

1 Answers1

1

As a general rule, this issue should not show up.

However, there are two cases to keep in mind. First case: Access when you START typing in a record, with a Access back end (BE), then the auto number is generated, and displayed instant, and this occurs EVEN before the record save.

And in fact if the record is not saved (user hits Esc key, or un-do from menu, or even ctrl-z). At that point, the record is not dirty and will not be saved. And of course this means gaps will and can appear in the autonumber.

WHEN using a linked table to sql server? You can start typing, and the record becomes dirty, but the AUTONUMBER will NOT display, and has NOT yet been generated. And thus your code cannot use the autonumber quite yet. The record has to be saved first before you can get/grab/use the autonumber.

Now for a form + sub form? Well, they work because access (for sql or access tables) ALWAYS does a record save of the main form when focus moves to the child form. So these setups should continue to work.

I note, and mention the above since SOME code that uses or requires use of the autonumber during a record add process MIGHT exist in your application. That code will have to be changed. Now to be fair, even in a fair large application, I tend to find few places where this occurs.

Often the simple solution is to modify the code, and simply force the record to be written, and then you have use of the autonumber. You can do this:

if me.IsNewReocrd = True then
  if me.dirty = true then me.Dirty = false
end if
' code here that needs the PK autonumber
lngNewID = me!id   ' the autonumber is now generated and available for use.

The next common issue (and likely YOUR issue).

The table(s) in question have triggers. You have to modify the store procedures to re-select the PK id, and if you don't, then you see/find the symptoms you see. If the store procedure updates other tables, then it can work, but the last line of the store procedure will need to re-select the PK id.

So, in the last line of your store procedure that is attached to the table? you need to re-select the existing PK value. eg:

SELECT @MyPK as ID
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51