How keep ADO connection to get AutoNumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Office 2003 and Windows XP;

I am using ADO to insert a new record into a table and I need to return the
"RcdID" which is an AutoNumber field.

As I understand, if I can keep the connection open, I can be reasonably sure
that I can return the RcdID for the record just inserted.

I normally use: Set rsADO = CurrentProject.Connection.Execute sSQL

Does this maintain an ADO connection? Or does Execute sever the connection
once the execute is done? If so, can someone please post a generic ADO code
example on how to run an INSERT INTO and then return the AutoNumber value?

Thanks again for your assistance.
 
XP said:
Using Office 2003 and Windows XP;

I am using ADO to insert a new record into a table and I need to
return the "RcdID" which is an AutoNumber field.

As I understand, if I can keep the connection open, I can be
reasonably sure that I can return the RcdID for the record just
inserted.

I normally use: Set rsADO = CurrentProject.Connection.Execute sSQL

Does this maintain an ADO connection? Or does Execute sever the
connection once the execute is done? If so, can someone please post a
generic ADO code example on how to run an INSERT INTO and then return
the AutoNumber value?

Thanks again for your assistance.
Click to expand...

'----- start of example code -----
Dim rsID As ADODB.Recordset
Dim sSQL As String
Dim lRcdID As Long

sSQL = _
"INSERT INTO MyTable (Field1, Field2) " & _
"VALUES(123, 'foo')"

With CurrentProject.Connection
.Execute sSQL
Set rsID = .OpenRecordset("SELECT @@IDENTITY")
lRcdID = rsID.Fields(0).Value
rsID.Close
End With
'----- end of example code -----
 
Back
Top