I'm having a strange problem. I have a database that was created in Access
2002, but I am now working with it in Access 2003. I'm trying to write code
that adds a record and then gets the primary key of the new record (which is
an autonumber). I found a post that should help me to do this, but it seems
that VBA does not have DAO loaded. The reason I suspect this is that the
following statements fail:

Dim rs as DAO.RecordSet
Dim db as DAO.Database
(Also fails if I use "Dim db as Database")

I am also unable to view DAO objects in the Object Browser. I get an error
that the object "is in the library 'DAO' which is not currently referenced".

BTW can anyone suggest an easier way to get the index than the following
snippet?

Dim rs As Recordset
Dim db As Database
Dim key as Integer

Set db = DBEngine(0)(0)
CurrentDb.Execute "INSERT INTO Residents (NameLast, NameFirst) VALUES ('" _
& GetLastName(NewData) & "' ,'" & GetFirstName(NewData) & "');"
Set rs = db.OpenRecordset '("SELECT @@IDENTITY AS LastID;")
key = rs!LastID

Thanks,

Paul Warhsawsky

Silly me, just needed to add a reference to DAO 3.6. Sorry.

"Paul Warshawsky" wrote:

> I'm having a strange problem. I have a database that was created in Access
> 2002, but I am now working with it in Access 2003. I'm trying to write code
> that adds a record and then gets the primary key of the new record (which is
> an autonumber). I found a post that should help me to do this, but it seems
> that VBA does not have DAO loaded. The reason I suspect this is that the
> following statements fail:
>
> Dim rs as DAO.RecordSet
> Dim db as DAO.Database
> (Also fails if I use "Dim db as Database")
>
> I am also unable to view DAO objects in the Object Browser. I get an error
> that the object "is in the library 'DAO' which is not currently referenced".
>
> BTW can anyone suggest an easier way to get the index than the following
> snippet?
>
> Dim rs As Recordset
> Dim db As Database
> Dim key as Integer
>
> Set db = DBEngine(0)(0)
> CurrentDb.Execute "INSERT INTO Residents (NameLast, NameFirst) VALUES ('" _
> & GetLastName(NewData) & "' ,'" & GetFirstName(NewData) & "');"
> Set rs = db.OpenRecordset '("SELECT @@IDENTITY AS LastID;")
> key = rs!LastID
>
> Thanks,
>
> Paul Warhsawsky

That's odd: Access 2003 has a reference to DAO 3.6 by default!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Paul Warshawsky" <(E-Mail Removed)> wrote in
message news:C5C7C842-39B2-44C7-94F8-(E-Mail Removed)...
> Silly me, just needed to add a reference to DAO 3.6. Sorry.
>
> "Paul Warshawsky" wrote:
>
>> I'm having a strange problem. I have a database that was created in
>> Access
>> 2002, but I am now working with it in Access 2003. I'm trying to write
>> code
>> that adds a record and then gets the primary key of the new record (which
>> is
>> an autonumber). I found a post that should help me to do this, but it
>> seems
>> that VBA does not have DAO loaded. The reason I suspect this is that the
>> following statements fail:
>>
>> Dim rs as DAO.RecordSet
>> Dim db as DAO.Database
>> (Also fails if I use "Dim db as Database")
>>
>> I am also unable to view DAO objects in the Object Browser. I get an
>> error
>> that the object "is in the library 'DAO' which is not currently
>> referenced".
>>
>> BTW can anyone suggest an easier way to get the index than the following
>> snippet?
>>
>> Dim rs As Recordset
>> Dim db As Database
>> Dim key as Integer
>>
>> Set db = DBEngine(0)(0)
>> CurrentDb.Execute "INSERT INTO Residents (NameLast, NameFirst) VALUES ('"
>> _
>> & GetLastName(NewData) & "' ,'" & GetFirstName(NewData) & "');"
>> Set rs = db.OpenRecordset '("SELECT @@IDENTITY AS LastID;")
>> key = rs!LastID
>>
>> Thanks,
>>
>> Paul Warhsawsky