Can a form's query use dbSeeChanges to get new autoincremented key?

  • Thread starter Thread starter sandal
  • Start date Start date
S

sandal

When a form was bound to a jet table I could get the new autoincrement
pkey value in the before insert event. Is there a way to do with with a
slq server 2005 odbc linked tables? Something like the dbSeeChanges
param that one can use with recordset inserts?
 
No, you can't because of the case of an ODBC linked table to a SQL-Server,
the new autoincremented value will be defined only after the creation of the
new record. Access/JET seeds the new value before creating the record - so
you can access it even if the record doesn't exist yet in the table - but
SQL-Server doesn't work the same way: the new value is seeded only when the
new record is created in the table and never before.

Finally, you should post this kind of question in a newsgroup dedicated to
ODBC linked tables such as m.p.access.odbcclientsvr as they are no ODBC
linked table in an ADP project (the topic if this newsgroup).
 
In a form, just *force* a disk write in your code.

old way:

somevalue = me!ID ' grab autonumber id

New way:

if isnull(me!id) = true then
if me.dirty = true then
me.Dirty = false '<-- this forces the disk write.
end if
end if

somevalue = me!id ' you can now grab/get record id

For general recorset code:

Dim rstRecords As DAO.Recordset
Dim lngNext As Long

Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew

' code can go here to add data, or set values to the reocord...
' or, you could "grab" the record id at this time
' the id of the reocrd added..

For sql server, you MUST force the udpate of the reocordset.

Thus, you keep the above code and THEN go:

rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID

Note how I use the LastModified to move back to the record id. DAO has a
quirk in that when adding NEW records, an update looses the record position
(ado does not do this). Regardless, using dao, or ado...simply force the
update to occur, and your home free.
 
I'm just working on solving a similar problem today. I have a 1-1
relationship which worked fine when the be was Jet. The form is bound to a
query and as long as at least one field in each table is populated, Jet was
able to handle the dual insert. However, the code is now failing with a SQL
server back end. Luckily the "parent" record has only a couple of fields
that are used to identify the class of record being inserted so it can be
built with no user input. Here's what I put in the Form's BeforeUpdate
event.

Dim rsADO As ADODB.Recordset
Dim objconn As ADODB.Connection
Dim strSQL As String
Dim ConnectString As String

If Me.NewRecord Then
strSQL = "Set Nocount on "
strSQL = strSQL + " Insert Producer (ProducerType, ChangedBy,
ChangedDate)"
strSQL = strSQL + " VALUES ('Agency', " + "'" + Environ("UserName") +
"'" + ", GetDate()) "
strSQL = strSQL + " select IdentityInsert = SCOPE_IDENTITY()"
strSQL = strSQL + " set nocount off"
ConnectString = DLookup("Connect", "qGetConnectionString")
Set objconn = New ADODB.Connection
objconn.ConnectionString = ConnectString
objconn.Open
Set rsADO = objconn.Execute(strSQL)
Me.AgencyID = rsADO("IdentityInsert")
End If

That leaves the form bound only to the Agency side of the relationship and
it works fine now that I can populate the AgencyID with the value of the
autonumber from the Producer table. I have the same code, except for the
ProducerType value in the Licensee form.

The pass through query sets no count on so that the procedure doesn't see
the messages. It inserts the row in the producer table and then retrieves
the identity column using SCOPE_IDENTITY() rather than @@IDENTITY which will
return an invalid result if the table has a trigger that causes an insert in
another table. IMHO, it is better to be safe than sorry and so even though
I do not have such a trigger and do not foresee adding one in the future,
someone else may and so why take the chance?

I use a query (qGetConnectionString) to look up the current connection
string for the linked tables from the MSysObjects table. This will allow
the code to work regardless of whether the fe is linked to the test or
production back end.
 
Back
Top