How to retrieve next value for SQL Server SEQUENCE from Microsoft Access VBA-CodePudding

I have a front-end Form in Access that allows entry and editing of rows from a back-end table in SQL Server. Originally the form was based on an Access table, but I've migrated the table to SQL Server for a number of reasons. The table has a single integer column, TestNumber, that serves as the primary key. In the Access table, the data type of that column was AutoNumber, so that each new record would automatically be assigned a unique number. In the new database, however, I would prefer to use a SQL Server SEQUENCE to supply primary key values. My question is, what is the best way to obtain the next value of that SEQUENCE from within an Access VB script and populate the TestNumber field with it so the record can be successfully inserted in the SQL Server table. Essentially, the code has to execute select next value for dbo.TestNumberSeq in SQL Server and return the value to the script.
Here is the script where I need to insert this logic, right before the DoCmd.DoMenuItem.
(I realize I could probably use an IDENTITY column and this issue would take care of itself, but there is a similar application where I will need to show the operator the new key value, so I really need to figure out how to do it this way.)

Private Sub cmdSaveRecord_Click()

On Error GoTo Err_cmdSaveRecord_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    cmdPrint.SetFocus

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
    
End Sub

CodePudding user response：

I realize I could probably use an IDENTITY column and this issue would take care of itself, but there is a similar application where I will need to show the operator the new key value, so I really need to figure out how to do it this way.

Regardless of how you generate your ID value, you can use an OUTPUT clause on the INSERT statement to return the value back to your application. Observe:

use tempdb;
go
drop table if exists dbo.WidgetsIdentity;
drop table if exists dbo.WidgetsSequence;
drop sequence if exists dbo.SQ_WidgetID;

create sequence dbo.SQ_WidgetID
    as int
    start with 1
    minvalue 1
    maxvalue 1000000
    increment by 1;

create table dbo.WidgetsIdentity (
    WidgetID int not null identity 
        constraint PK_WidgetsIdentity primary key clustered
);

create table dbo.WidgetsSequence (
    WidgetID int not null
        constraint PK_WidgetsSequence primary key clustered
        constraint DF_WidgetID default (next value for dbo.SQ_WidgetID)
);

insert into dbo.WidgetsIdentity output inserted.WidgetID default values;
insert into dbo.WidgetsSequence output inserted.WidgetID default values;

Note: in neither case was it necessary to first get the ID value and then use it to insert nor was it required to know the method of ID generation for the table.

CodePudding user response：

I still recommend if you can using a "identity" column, and also as PK, then that is a good choice. (however, in general, such values should not have meaning to the end user).

While access back ends SHOWS the "ID" right away, in sql server, addin a new reocrd, start editing of course does not show that new number.

However, you can dirty the record (one of the columns needs to get dirty). You can then do this:

if me.dirty = true then me.dirty = false.

(dump your do menu command - they are flakey, and above one liner is rather simple. As noted, the ONLY "got ya", (even with the menu command), is no save can/will occur UNLESS the form gets dirty in the first place!

so, I might have say this:

 me!CreateDate = date.now
 me.dirty = false

The instant you do above, then the auto number value will populate.

However, for things like say a invoice number or whatever? well, such numbers are custom, and like many applications, then I have a table "nextInvoiceNumber".

I have that table, so we can set/change what the starting invoice number supposed to be.

So, then you have say this code:

Public Function GetNextInvoiceNum() As Long

  Dim myRST         As dao.Recordset
  Set myRST = CurrentDb.OpenRecordset("dbo_NextNumber", dbOpenDynaset, dbSeeChanges)
  
  GetNextInvoiceNum = myRST!NextInvoiceNum
  
  myRST.Edit
     myRST!NextInvoiceNum = myInvoiceNum   1
     myRST.Update
  myRST.Close

End Function

So, now, the forms before -insert event - (only triggers when the user starts typing - makes the form dirty, you have this:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me!InvoiceNumber = GetNextInvoiceNum    

End Sub

of course, often when you jump/land on a new record, you really don't want to add that record and generate the next invoice number until such time the user actually starts typing.

The before insert event can only ever fire one time - and only trigger WHEN the user starts typing into that form.

So, for autonumber? The you have to dirty SOME column, and then go me.dirty = false - you see the autonumber.

For a custom PO, or invoice number? Then database generated counter numbers tend to not work, and are not appropriate. (but a custom invoice table with a number you want to start at, as per above is just fine).