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:
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.
Muchas gracias por tus aportaciones!
No sé si este es el foro adecuando, pero estoy empezando a migrar mi DB hacia SQL server y he adaptado tu código para recuperar el Id del registro recién creado, pero después de crearlo siempre me devuelve 0.
La segunda instrucción parece no funcionar, te has encontrado alguna vez con este problema?
Gracias anticipadas