Re: Best way to find ID of new record

Thanks Dan. I will look into using a "Transaction on the Connection" to avoid
the problem you mention.

I studied my original question on MSDN and found the answer I needed (not
necessarily the question I asked). I said I used an mdb file but did not
mention that I also, in other applications, utilize SQL Server. I'm pretty
sure now that the code I listed will work with an access db but will not work
with SQL Server. That was what was confusing me. I have developed my
applications utilizing ADO and Queries in such a way that I might adapt to
either a mdb file or SQL Server by changing the provider part of the
connection string. Interestingly this is the first specific difference (and
incompatability) I have found between using an MDB file or SQL Server.
But studying MSDN Article 221931 raised some other questions. The sample
code lists the following:
objRS.Open "SELECT * FROM Customers", objConn, adOpenStatic, adLockOptimistic
AND THEN
objRS.AddNew
And ADO help says adOpenStatic is read only!
--
Thanks John Brown


"Daniel Crichton" wrote:

John wrote on Tue, 21 Mar 2006 10:43:52 -0800:

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


That's pretty much how I tend to do the same thing, except that I read the
ID into a variable after the .Update and then close the recordset, and then
reuse the recordset object for the other table and use the variable to pass
the ID. Saves having 2 recordsets open, and keeps the amount of time the
first recordset is open for to a minimum. I also tend to explicitly declare
the recordset location as adUseServer just in case something changes it
elsewhere - you might have problems with adUseClient. Also look into using
adOpenKeyset for the cursor type - I don't think this will affect you with
using Jet, but it might if you move to SQL Server later.

You might also want to look at use a Transaction on the connection to
enclose the whole update so that if anything fails you don't end up with a
parent trans record with no invoiceitem records.

Dan



.

Relevant Pages

  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: commit or rollback a transaction without first beginning a transaction
    ... I understood the opposite; that when a recordset ... the recordset before entering the transaction, ... > what happens with a Jet recordset. ... >> a single connection, so when SQLTransact is called, all statements on the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Delete event not followed up with BeforeDelConfirm or AfterDel
    ... I take it you are trying to wrap your own transaction around all the ... inserts, edits, and deletes in both a main form and a subform. ... decision to either commit or rollback. ... recordset within that transaction, open a form, and assign your recordset to ...
    (microsoft.public.access.forms)
  • Re: "cant open any more tables"
    ... Assign the workspace variable, then the transaction, ... If you do need to close a recordset, ... >> updates every time through the loop. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Testing for rs.AddNew
    ... transaction until lock is released on Commit or RollBack. ... Otherwise just read ADO help about rules of AddNew method, ... Update method implicitly and CancelUpdate will not work. ... because there is no that recordset anymore. ...
    (microsoft.public.vb.database)