ADO data controls can't seem to handle auto-generated fields

[Dave Rudolf]

Hey all,

I have an ADO DC (in Visual Basic 6) that is connected to an Access '97 MDB
file via an ODBC driver. The DC's record source is set to a table in this
database that has a integer key field that is automatically generated (i.e.,
each new record increments some counter and uses that value as its key). Now,
when I do an AddNew call on the ADODC's record set, it obviously doesn't know
the value that the database is going to use and thus that field is "Empty". If
I then call Update on that record set, the field always returns 0, no matter
what value the database has assigned to that field. I want to know what key
value was assigned to that record.

To complicate things, that key field is the only thing that is unique about
the records in the table. Since I have no idea what it is, I can't just tell
the ADODC to Refresh and then search for the newly added record. Is there some
other way to get the record set to figure out what key value is?

Thanks

Dave

 

[Ken Snell [MVP]]

Did you try to read the value of that field in the middle of the AddNew
block? If that field is an autonumber field, then the value is generated and
inserted into that field as soon as you write the value into a field in the
recordset.

Dim varPrimary
With ADODB.Recordset ' just a generic way to reference the recordset
.AddNew
.Fields("NotThePrimaryKey").Value = "somevalue"
varPrimary = .Fields("ThePrimaryKey").Value
' continue with setting values to rest of the fields
.Update
End With

 

[Dave Rudolf]

Tried reading before and after the call to Update. Before, the field is set to
"Nothing" according to the debugger and after, it appears as 0. In both cases,
if I try to write to an integer variable, I get the value 0.

 

[Ken Snell [MVP]]

I just tested this here and it works just fine. I am able to read the
autonumber field's value during the adding of the new record. Here is test
code that I used with a sample database that I have here:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblscientists", cnn, adOpenDynamic, adLockOptimistic
rst.AddNew
rst.Fields("sciname").Value = "thisisatest"
' this is the autonumber field: sciid
Debug.Print "sciid = " & rst.Fields("sciid").Value
rst.Fields("sciphone").Value = "alsotest"
rst.Fields("ttt").Value = 1909
rst.Update
rst.Close
Set rst = Nothing
Set cnn = Nothing

Post the exact code that you're trying to use.
--

Ken Snell
<MS ACCESS MVP>

 

[Dirk Goldgar]

I just tested this here and it works just fine. I am able to read the
autonumber field's value during the adding of the new record. Here is
test code that I used with a sample database that I have here:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblscientists", cnn, adOpenDynamic, adLockOptimistic
rst.AddNew
rst.Fields("sciname").Value = "thisisatest"
' this is the autonumber field: sciid
Debug.Print "sciid = " & rst.Fields("sciid").Value
rst.Fields("sciphone").Value = "alsotest"
rst.Fields("ttt").Value = 1909
rst.Update
rst.Close
Set rst = Nothing
Set cnn = Nothing

Post the exact code that you're trying to use.

I think the key point is probably that Dave is using an ODBC driver to
connect to the MDB file. I don't know much about that, but it wouldn't
surprise me to find that the autonumber value of the autonumber field
isn't available immediately. On the other hand, it may depend on the
updating mode of the recordset.

I have these questions:

1. Why not use the Jet driver?

2. Does the following variant on the code make a difference?

Dim varBookmark As Variant
' ...
With rst
.AddNew
.Fields("foo") = "bar"
.Update
varBookmark = .Bookmark
.Bookmark = varBookmark
Debug.Print .Fields("ID") ' the autonumber
End With

3. I'm pretty sure there's a way using Jet 4.0 SQL to query the last
autonumber generated. I can't remember the syntax, though. Do you,
Ken?

 

[Ken Snell [MVP]]

Dirk -

You are right -- I missed the reference to ODBC driver when I read the first
post. My test was with a local table in my .mdb file, so Dave's results
probably won't match mine. Google searching suggests that using ODBC driver
isn't going to make it easy to get that number.

I'm not familiar with using Jet 4.0 SQL for reading last-entered ADO
records. I did some Google searching, and found some things that suggest a
way to do it; and also found some that suggest using "INSERT INTO" SQL
statements instead of the ADO recordset so that the unique key can be read
as a result of that method having been used.

Sorry that I can't add to this info!

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

What about if you try to Debug.Print the value? Is it possible that the
value is too large to fit into an Integer variable? Did you try Long
variable?

 

[Ken Snell [MVP]]

Here are a few KB articles that may assist Dave:

http://support.microsoft.com/?kbid=221931

http://support.microsoft.com/?kbid=232144

http://support.microsoft.com/?kbid=233299

http://support.microsoft.com/?kbid=190370


--

Ken Snell
<MS ACCESS MVP>