Insert Records with VBA and get new autonum value - different variants and possible problems (1 Viewer)

[Josef P.]

Inspired by https://www.access-programmers.co.u...oid-the-domain-functions-in-ms-access.330010/ I am trying to compare insert variants in VBA in this thread.

First of all: I only discuss the variants that can be used with Jet/ACE (e.g. executing procedures in an active DBMS is not included).

In principle, there are 2 basic variants:
1. Execute an SQL insert statement (SQL directly, stored query with parameters, ... + get autonum/identity from select @@identity, max(id), ....)
2. Insert via a recordset (DAO or ADDOB)

Principle approach for 1:

InsertSql = "insert into tabTest(N, T, D) ...."
db.execute InsertSql
' a) @@Identity
With db.OpenRecordset("select @@Identity", dbOpenForwardOnly, dbReadOnly)
     NewId = .Fields(0)
     .Close
End With

' b) Max(AutoNumFieldName) ... (if ascending)
With db.OpenRecordset("select Max(AutoNumFieldName) from ...", dbOpenForwardOnly, dbReadOnly)
     NewId = .Fields(0)
     .Close
End With

Problem with @@Identity:
If an insert trigger is running and an autonum is assigned in another table in this trigger, this is output.

Problem with Max(AutoNumFieldName)
In a multi-user environment, it is essential to ensure that the insert statement and the query of Max(AutoNumFieldName) run within a transaction so that no values are output by another user. Of course, AutoNum must also be set to ascending. (But that will be true most of the time.)


Principle approach for 2:

With db.OpenRecordset(Source, dbOpenDynaset, dbAppendOnly)
   .AddNew
   .Fields(...).Value = ...
   ...
   .Update
   .Bookmark = .LastModified
   NewId = .Fields(AutoNumFieldName).Value
   .Close
End With

In my opinion, this is the most stable option.
The disadvantage of this variant is that it is difficult to write in a general reusable function, as field names and their values must be transferred.
With the SQL variant, it is basically sufficient to pass the SQL statement (and with Max(...) the name of the data field to be queried)

Example file see attachment.
Note: The procedures contained in the example are intentionally not designed as reusable (for different data sources) procedures so that only the essential code is easier to read because no complex interfaces are required.
However, we can discuss the design of the reusable procedures and show possibilities.

 

[theDBguy]

Problem with @@Identity:
If an insert trigger is running and an autonum is assigned in another table in this trigger, this is output.

In that case, try using SCOPE_IDENTITY instead.

PS. I haven't tried using it in Access though. Not sure if it will work.

 

[ebs17]

get new autonum value

I would connect this task with the question of why you need it.

NewID ... certainly means that a new record is created by inserting it, and you need the primary key created immediately for further use. It occurs to me that this ID is subsequently required as a foreign key in order to be able to insert records with the correct assignment into a dependent table.
Are there other uses and needs?

It should also be noted that in all variants only one record is inserted and only one NewID is and can be returned. In a mass update you need many individual runs.

It also works a little differently. The autonum value as a primary key is a surrogate key. A proper database table should therefore have at least one unique attribute field. This attribute field is therefore connected 1:1 to the PK. If I initially note the value of the inserted attribute, I can determine the associated ID in a subsequent query.

This then enables you to return more than one NewID.
I use something like this to divide the content of an unnormalized Excel table coming from outside into the tables of an existing data model, i.e. at least into a one-to-many relationship. You then only need one append query per table.

 

[Josef P.]

[@@Identity]

In that case, try using SCOPE_IDENTITY instead.

Not in Jet/ACE.
This SQL function would also return the identity value from the table that was filled by the trigger in the SQL server.
/edit: see #5, #6

 

[theDBguy]

[@@Idendity]

Not in Jet/ACE.
This SQL function would also return the identity value from the table that was filled by the trigger in the SQL server.

Okay, it's not for Access. However, this is a snippet from MS Help site:

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY returns the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

It says the trigger shouldn't affect SCOPE_IDENTITY like it would @@IDENTITY. I have no way of testing it right now though.

 

[Josef P.]

It says the trigger shouldn't affect SCOPE_IDENTITY like it would @@IDENTITY. I have no way of testing it right now though.

Right, I've just tested it. (I remembered it differently).

 

[Pat Hartman]

The autonum value as a primary key is a surrogate key. A proper database table should therefore have at least one unique attribute field.

No, it doesn't. There are cases where this is true but for most it is not true. What is the natural key of a Customer table? CustomerName, nope, not guaranteed to be unique. CustomerName + StreetAddress, maybe. CustomerName + StreetAddress + City, probably.

 

[ebs17]

To put it more precisely, as desired, one would replace "one unique attribute field" with a combination of fields, which then provides the natural key for the uniqueness of the record.

CustomerName + StreetAddress + City

However, this example natural key shows very clearly how advantageous a surrogate key made up of a single long content will be.

Keys are primarily used to link tables. With a single field for linking, queries become much simpler and easier to read. Above all, JOINs are comparative operations, and in terms of performance, it makes a significant difference in terms of the bit width of the key to be compared whether you have to compare 4 bytes or, for example, 3 strings with an average of 70 bytes en masse.
In addition, the contents of natural keys can change. If the person marries and takes the partner's name, is that still the same person? The surrogate key does not have to change because of this.

 

[Pat Hartman]

We know what keys are used for. Why are you asserting that a "proper" table should have at least one unique attribute field?

 

[ebs17]

A "proper" table in a database should not be a collection of duplicates, as far as I understand. Therefore.
I don't think I'm reporting anything new. But at least the topic has been taken up again after many months and seems to have some significance besides eliminating boredom.