Upsizing Wizard Problem

[Cheryl]

The upsizing wizard in Access 2002 works well, with the exception of any
table with autonumber fields. I found this explanation in the knowledge
base:

Incrementing AutoNumber values in Microsoft Access (called "counters" in
previous versions) and the triggers created by the Upsizing Wizard to
emulate incrementing AutoNumber values (when upsizing to SQL Server 4.21
and 6.0) or the identity columns (created when upsizing to SQL Server
6.5) don't behave identically. In Microsoft Access, an AutoNumber value
is pre-allocated; that is, it is generated as soon as a user starts
editing a new record, before it is saved. On SQL Server, a trigger or
identity column generates the number only when the new record is saved.
If your Microsoft Access application depends on using the value
generated by an AutoNumber field before the record is saved, you will
need to redesign this functionality when upsizing to SQL Server.

Can anyone interpret that for me? Exactly how would I "redesign this
functionality?" Thanks for any insight you can give me on this!

 

[Alex - IntraLAN]

Hi Cheryl,

Autonumber or counter are great for bottom level tables in other word
if you have a relational structure like

Contacts
Contacts_Calls

I put the autonumber fields on the bottom level tables e.g.
Contact_Calls, but for the top level tables I use a control table that
has a field for each table I want to keep count of, incrementing this
field whenever I add a record, this gives me great flexability in
programming because I can get the next primary key value before adding
the record. This idea works well for the top level tables e.g. invoice
but not invoice details, contact but not contact calls. A mixture of
the two works well.

Public Function inCre(strFieldName As String) As Long
Dim adoRC As New ADODB.Recordset
Dim SQL As String
Dim lngCounter As Long = -1
SQL = "Select " & strFieldName & " as myField from TblControl"
adoRC.Open(SQL, CurrentProject.Connection,
adOpenKeyset,adLockOptimistic)
With adoRC
If .RecordCount > 0 Then
If Not IsNull(.Fields("myField").Value) Then
.Fields("myField").Value += 1
lngCounter = .Fields("myField").Value + 1
.Update()
End If
End If
.Close()
End With
inCre = lngCounter
End Function

Hope this helps.

Regards

Alex