Re: inserting a new record and retriving an autonumber value immediately...

From: Veign (NOSPAMinveign_at_veign.com)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 12:04:51 -0500

Oops.
Set RS = .Execute("SELECT @@Identity FROM tblMyTable")

should have been

Set RS = CNN.Execute("SELECT @@Identity FROM tblMyTable")

Where CNN is your connection object

Better complete example
'-----------------------Start Insert Code------------------------------
Dim RS As Recordset
Dim CNN As Connection
Set CNN = New Connection
Set RS = New Recordset

'Build SQL Statement
Dim strSQL
strSQL = "INSERT INTO MyTable (myField) " & _
                "VALUES ('Value1')"

With CNN
    .Open ConnectionString

    .Execute strSQL

    'Get the assigned autonumber
    Dim lngID As Long
    Set RS = .Execute("SELECT @@Identity FROM Customer")
    lngID = RS.Fields(0).Value

    If RS.State = adStateOpen Then RS.Close
    Set RS = Nothing

    .Close
End With

Set CNN = Nothing
'---------------------------------------------------------------------

Where:
ConnectionString - Is your connection string
myTable - Is your table name
myField - List if fields in your table 

-- 
Chris Hanscom - Microsoft MVP (VB)
http://www.veign.com
--
"Veign" <NOSPAMinveign@veign.com> wrote in message
news:%23$aEYCZzEHA.4028@TK2MSFTNGP15.phx.gbl...
> Use the @@Identity
>
> '-----------------Start Code-------------------------
>     'Get the assigned autonumber
>     Dim lngID As Long
>     Set RS = .Execute("SELECT @@Identity FROM tblMyTable")
>     lngID = RS.Fields(0).Value
> '-----------------End Code-------------------------
>
> Where tblMyTable is your table name...
>
>
> -- 
> Chris Hanscom - Microsoft MVP (VB)
> http://www.veign.com
> --
>
> "Brad Pears" <donotreply@notreal.com> wrote in message
> news:%237b6fsYzEHA.2568@TK2MSFTNGP11.phx.gbl...
> > I am working on a batch app which is using ADO to create records in a
> couple
> > of tables. The database is an Access 2000 db.
> >
> > In one situation, I am inserting a record in a table that has a primary
> key
> > based on an "autonumber" field. Access handles the creation of this
field
> > when it is inserted into the db.
> >
> > The problem is that, immediately after inserting this record, I also
need
> to
> > insert a related record in another table. The relation is based on this
> > primary "autonumber" field, so in order to create the related record, I
> need
> > to know what the value of the autonumber field was that Access created.
> >
> > I could simply get the most recent record written to the first table
> > immediatley after the "insert" as there is a datestamp field in there.
> > However, how could I be sure that the record I get is actually the most
> > recent? Is it possible that there could be a delay in writing the first
> > record and my "select" statement I issue after the insert actually gets
> the
> > wrong record?? This would cause all kinds of problems!!!
> >
> > Is there a better way to do this or am I on the right track?
> >
> > Also, isn't there a "dbengine.idle" or something like that that waits
for
> > updates to the DB to be done? Could I use this? Is it applicable with
> ADO??
> >
> >
> > Thanks,
> >
> > Brad
> >
> >
>
>

Relevant Pages
Loading