
Returning ID from SQL through ADO recordset
This function should return the ID of the most recently added record.
Unfortunately, it always returns 0 event though the record gets added
correctly in the db with a non-zero ID value. Interestingly, the
recordset object doesn't take on the CursorType of adOpenKeySet or the
LockType of adLockOptimistic. I'm thinking this might be related to the
problem. The recordset gets the default CursorType and LockType even
though I'm explicitly providing them as part of the rs.open statement.
Anybody know what might be going on here?
Function GetNewID(strTableName As String, strIDName As String) As
Integer
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer
Set cn = CreateObject("ADODB.Connection")
cn.Open sConnectionString, sRuntimeUserName, sRuntimePassword
rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
rs.AddNew
rs.Update
i = rs.Fields(strIDName)
GetNewID = i
rs.close
Set rs = Nothing
Set cn = Nothing
End Function