Compacting in 2002 does not reset Autonumbering

C

Connie

In previous versions, I have compacted the backend & it would reset the next
available autonumber, even if there were records in the table. Now I find
that compacting will only reset the autonumbering if the table is empty. I
know I'm probably being anal with this issue, but when I enter some testing
data to make sure all is working well I then want to delete that last record
& have the next record the client will enter to be the next consecutive
autonumber. Have I lost it, or is 2002 different than the previous
versions.

Thanks
Connie
 
C

Connie

I forgot to add my question .... seeing compacting isn't doing it anymore,
is there another way where I can have the next consecutive autonumber come
up for users after I'm done testing?

Thanks
 
K

Ken Snell

Short of copying your records to a temporary table, deleting all the records
from the permanent table, and then running an append query (which includes
the autonumber field as a target field) to reload your records into the
permanent table.....

No.
 
G

Graham Mandeno

Hi Connie

Yes, it can be done.

The following function will reset the autonumber to given value (defaulting
to the current Max+1). It also works for linked tables. The second
function below performs the process for all tables in the current database.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

=================== START CODE =============

Public Function ResetAutonumber( _
sTable As String, _
Optional sField As String, _
Optional lSeed As Long _
) As Long
'sTable = Table containing autonumber field
'sField = Name of the autonumber field
' (default is the first Autonumber field found in the table)
'lSeed = Long integer value you want to use for next AutoNumber
' (default is one more than the current maximum)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim col As Object 'ADOX.Column
Dim tbl As Object 'ADOX.Table
Dim sRemoteTable As String
On Error GoTo ProcErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTable)
If tbl.Type = "LINK" Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open _
CurrentProject.Connection.ConnectionString & ";" & _
"Data Source=" & _
tbl.Properties("Jet OLEDB:Link Datasource")
cat.ActiveConnection = cnn
sRemoteTable = tbl.Properties("Jet OLEDB:Remote Table Name")
Set tbl = cat.Tables(sRemoteTable)
End If
If Len(sField) = 0 Then
For Each col In tbl.Columns
If col.Properties("AutoIncrement") Then
sField = col.name
Exit For
End If
Next
If Len(sField) = 0 Then GoTo ProcEnd
Else
Set col = tbl.Columns(sField)
End If
If lSeed = 0 Then
lSeed = Nz(DMax(sField, sTable), 0) + 1
End If
col.Properties("Seed") = lSeed
ResetAutonumber = lSeed
ProcEnd:
On Error Resume Next
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

Public Sub ResetAllAutoNumbers()
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.name, ResetAutonumber(tdf.name)
End If
Next
End Sub
============ END CODE ==============
 
C

Connie

I was afraid that would be the ans. Wonder why MS changed this!

Thanks for you reply
Connie
 
C

Connie

Fantastic!! Too bad we have to now manually write code to do something that
was already in place in previous version. But hey at least I have a
work-around.

Thanks so much for the code!!
Connie



Graham Mandeno said:
Hi Connie

Yes, it can be done.

The following function will reset the autonumber to given value (defaulting
to the current Max+1). It also works for linked tables. The second
function below performs the process for all tables in the current database.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

=================== START CODE =============

Public Function ResetAutonumber( _
sTable As String, _
Optional sField As String, _
Optional lSeed As Long _
) As Long
'sTable = Table containing autonumber field
'sField = Name of the autonumber field
' (default is the first Autonumber field found in the table)
'lSeed = Long integer value you want to use for next AutoNumber
' (default is one more than the current maximum)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim col As Object 'ADOX.Column
Dim tbl As Object 'ADOX.Table
Dim sRemoteTable As String
On Error GoTo ProcErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTable)
If tbl.Type = "LINK" Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open _
CurrentProject.Connection.ConnectionString & ";" & _
"Data Source=" & _
tbl.Properties("Jet OLEDB:Link Datasource")
cat.ActiveConnection = cnn
sRemoteTable = tbl.Properties("Jet OLEDB:Remote Table Name")
Set tbl = cat.Tables(sRemoteTable)
End If
If Len(sField) = 0 Then
For Each col In tbl.Columns
If col.Properties("AutoIncrement") Then
sField = col.name
Exit For
End If
Next
If Len(sField) = 0 Then GoTo ProcEnd
Else
Set col = tbl.Columns(sField)
End If
If lSeed = 0 Then
lSeed = Nz(DMax(sField, sTable), 0) + 1
End If
col.Properties("Seed") = lSeed
ResetAutonumber = lSeed
ProcEnd:
On Error Resume Next
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

Public Sub ResetAllAutoNumbers()
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.name, ResetAutonumber(tdf.name)
End If
Next
End Sub
============ END CODE ==============

Connie said:
I forgot to add my question .... seeing compacting isn't doing it anymore,
is there another way where I can have the next consecutive autonumber come
up for users after I'm done testing?

Thanks


the
next empty.
I
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

I was afraid that would be the ans. Wonder why MS changed this!

Because (although you might not notice it), autonumber was
broken in Jet 4.0, and after 3 years trying to fix it, they
gave up. Also because the feature was just useful enough so
that people used it, but not useful enough so that people
liked it. It confused naive users (the target market for
Access). Imagine having to compact your database just to
reset the autonumbers!

(david)
 
T

Tim Ferguson

Fantastic!! Too bad we have to now manually write code to do
something that was already in place in previous version. But hey at
least I have a work-around.
Click to expand...
For most of us, the old behaviour was bugged, and the new behaviour is
fixed. It should be possible to rely on (a) all autonumbers being different
and (b) once a number is used, it never comes back. Outside of these rules,
"if you care what an autonumber value is, then you shouldn't be using an
autonumber".

What do you do when an autonumber is used and wasted (e.g. by user pressing
<cancel>) and the next one is issued, leaving the gap, before you have a
chance to compact? There is no bullet-proof way of getting Access
Autonumbers to be sequential, and IMO you shouldn't be trying. If you need
sequential numbers, then create your own properly.

B Wishes



Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA to delete records in Access table and reset the Autonumber 1
Autonumber associated with date 0
Autonumber restarting after compact and repair 4
AutoNumber Using Duplicate ID 3
Compact & Repair 4
Compacting Database to reset autonumber 3
Resetting (Autonumber) - Solution 8
Autonumber deleted IDs resued after Compact? 2

Top