Go to Top

Inserting a record in SQL Server and retrieving key ID.

There are many ways to insert records and retrieve that records key value, this approach will probably be among the quickest since all of the processing occurs on the server, not in Access. The technique uses ADODB recordsets to fetch records from SQL Server.

CODE:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

Private Function CreateDatabaseRecord() As Boolean
Dim strSQL As String
Dim rsDoc As ADODB.Recordset
Dim rs_Value As ADODB.Recordset
    
    ‘Create database record
   On Error GoTo CreateDatabaseRecord_Error
 
    strSQL = “Insert Into tblEventLetters(LetterTypeID, EventID, CreatedBy) Values(” & lngDocumentTypeID & “, ” & lngEventID & _
        “, ‘” & GetNetworkName() & “‘); SELECT SCOPE_IDENTITY() as NewEventLetterID”
    If con.State = adStateClosed Then
        OpenMyConnection
    End If
    Set rsDoc = con.Execute(strSQL)
    Set rs_Value = rsDoc.NextRecordset
    With rs_Value
        lngDocumentID = !NewEventLetterID
    End With
    
    Set rs_Value = Nothing
    Set rsDoc = Nothing
    
    CreateDatabaseRecord = True
 
   On Error GoTo 0
   Exit Function
 
CreateDatabaseRecord_Error:
    CreateDatabaseRecord = False
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure CreateDatabaseRecord of Class Module clsDocManager”
End Function
 
Private Function CreateDatabaseRecord() As Boolean
Dim strSQL As String
    Dim rsDoc As ADODB.Recordset
    Dim rs_Value As ADODB.Recordset
    
    ‘Create database record
    ‘strSQL = “Insert Into tblEventLeters(LetterTypeID, EventID) Values(” & ReadGV(“LetterTypeID”, lngNumber) & “, ” & ReadGV(“EventID”, lngNumber) & _
        “)”
   On Error GoTo CreateDatabaseRecord_Error
 
    strSQL = “Insert Into tblEventLetters(LetterTypeID, EventID, CreatedBy) Values(” & lngDocumentTypeID & “, ” & lngEventID & _
        “, ‘” & GetNetworkName() & “‘); SELECT SCOPE_IDENTITY() as NewEventLetterID”
    If con.State = adStateClosed Then
        OpenMyConnection
    End If
    Set rsDoc = con.Execute(strSQL)
    Set rs_Value = rsDoc.NextRecordset
    With rs_Value
        lngDocumentID = !NewEventLetterID
    End With
    
    Set rs_Value = Nothing
    Set rsDoc = Nothing
    
    CreateDatabaseRecord = True
 
   On Error GoTo 0
   Exit Function
 
CreateDatabaseRecord_Error:
    CreateDatabaseRecord = False
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure CreateDatabaseRecord of Class Module clsDocManager”
End Function
 
CODE Review
You will notice that the SQL string contains two statements separated by a “;”. Using this technique will allow you to submit multiple statements for processing to the server. The gist of the technique is the second recordset being retrieved with the KEY ID, in this case NewEventLetterID. SCOPE_IDENTITY() insures you receive your KEY ID and not the latest one generated for that table, which may be from another user in the system.
 

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]