Assigning value to AutoNumber (Identity Insert)

[Mark A. Sam]

I am changing a program from an MDB to a Project and having a problem
assigning a value to an Autonumber field:

Dim rst As New ADODB.Recordset
rst.Open "seller", CurrentProject.Connection, adOpenStatic,
adLockPessimistic

rst.Find "[coID] = " & 0
rst.AddNew
rst![coID] = 0
rst![Company] = "Do not delete"
rst.Update


Seller is a table. I get the error:

Run-time error '-2147217887 (80040e21)
Multiple-step operation generated errors. Check each status value.

I also need to assure that there is a record with a [coID] value of 0.

My DAO code was

rst.FindFirst "[coID] = " & 0
If rst.NoMatch Then
rst.AddNew
rst![coID] = 0
rst![Company] = "Do not delete"
rst.Update
End If

I also don't know how to test if the Value was found, since there is no
NoMatch property.

Thanks for any help.

Mark A. Sam

 

[Guest]

You cannot assign a value to an AutoNumber field. Whether it is an mdb or an
adp makes no difference. If you must assign your own numbers, you need to
set up your own number incrementing logic. You will find numerous posts on
the subject here.

 

[Mark A. Sam]

Hello Klatuu,

In an .mdb, you can assign an Autonumber using DAO. I have done it many
times including the code I posted.. You can also assign set the values
using an append query, in an .mdb and a project. I don't know if ADO
doesn't allow it or I need a different method.

God Bless,

Mark

 

[Guest]

You may be correct about DAO, I am only recently beginning to use it. ADO
does not allow it. If you need to control the numbering scheme, why are you
using Autonumber? It would be simpler to assign your own numbering as
needed. If it needs to be sequential, then use a DMAX() on the field to get
the current high number, and increment it as you need to.
IMHO, autonumbers are good only for foreign keys in a relational model.

 

[Mark A. Sam]

Hello Klatuu,

I don't need to control the numbering scheme, only assure that one record
contains an ID of 0. In this case, however there are only 4 other records,
so I can remove the Autonumber. I wasn't thinking that I didn't need
it...lol.

I can't figure out why they ever implemented ADO when DAO is much better to
use. At least they could have kept the same methods and properties.


Thanks and God Bless,

Mark

 

[Guest]

ADO has been around a lot longer in Access that DAO has. I think DAO is an
improvement, but it takes some getting used to.

 

[Marshall Barton]

ADO has been around a lot longer in Access that DAO has. I think DAO is an
improvement, but it takes some getting used to.

Whoaa there, ADO is the late comer by many years.

While ADO may have its uses when working with SQL Server and
other database systems, DAO has been the original, native
access library of the Jet database engine since version 1.

 

[Guest]

oops! got my acronymns mixed up again.
Dislexics Untie!