RE: Best way to find ID of new record

oops, I forgot. I am using a Jet database, .mdb, and ADO 2.5

I read Article ID 221931 which addresses this problem. But they open the
recordset with "SELECT * FROM Table" which I am trying to avoid because I
understand that can be resource and time consuming when you have 100,000
records. I wonder why my method works after reading that article.

--
Thanks, John Brown


"John Brown" wrote:

I often need to add a new record to a table, find out what that new record’s
auto-increment ID is, and add records to another table that refer the new
record in the first table using the before mentioned auto-incremented ID. For
example, lets call the first table Transaction and I want to add an Invoice.
I open a recordset, rsTrans, (with no records to minimize overhead) from the
Transaction table, rsTrans.addnew, put values in the fields/columns
(including only the invoice total amount $100, not individual amounts from
items), and rsTrans.update. In my experience this is where it gets squirrely.
Sometimes you can accurately refer to rsTrans(“ID”) and sometimes it is not
available or not accurate. Then I immediately want to open another no record
recordset, rsInvoiceItems, from the InvoiceItems table and add 3 records;
item1 for $60, item2 for $35, and item3 for $5 where each
rsInvoiceItems(“TransID”) = rsTrans(“ID”)

I know from trial and error that the following code works but I am not
confident that it will always work the way it should.

Dim conWW As ADODB.Connection
Dim rsTrans As ADODB.Recordset
Dim rsInvoiceItem As ADODB.Recordset
Dim i As Integer

Set conWW = New ADODB.Connection
conWW.Mode = adModeReadWrite
conWW.Open strConnection

Set rsTrans = New ADODB.Recordset
rsTrans.Open "SELECT * FROM Transactions WHERE 1=0", _
conWW, adOpenDynamic, adLockOptimistic

rsTrans.AddNew
rsTrans("Amount") = 100
rsTrans("TypeTrans") = "Invoice"
rsTrans("UserID") = User.ID
rsTrans.Update

Set rsInvoiceItem = New ADODB.Recordset
rsInvoiceItem.Open "SELECT * FROM InvoiceItems WHERE 1=0", _
conWW, adOpenDynamic, adLockOptimistic

rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 60
rsInvoiceItem("Description") = "item1"
rsInvoiceItem.Update

rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 35
rsInvoiceItem("Description") = "item2"
rsInvoiceItem.Update

rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 5
rsInvoiceItem("Description") = "item3"
rsInvoiceItem.Update

rsInvoiceItem.Close: Set rsInvoiceItem = Nothing
rsTrans.Close: Set rsTrans = Nothing
conWW.Close: Set conWW = Nothing

Thanks, John Brown

.

Relevant Pages

  • RE: Best way to find ID of new record
    ... Dim conWW As ADODB.Connection ... Dim rsTrans As ADODB.Recordset ... Set rsTrans = New ADODB.Recordset ... Set rsInvoiceItem = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Best way to find ID of new record
    ... Dim conWW As ADODB.Connection ... Dim rsTrans As ADODB.Recordset ... Set rsTrans = New ADODB.Recordset ... Set rsInvoiceItem = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: Best way to find ID of new record
    ... Then I immediately want to open another no record recordset, ... Dim conWW As ADODB.Connection ... Dim rsTrans As ADODB.Recordset ... Set rsInvoiceItem = New ADODB.Recordset ...
    (microsoft.public.data.ado)