How to reset AutoNumber sequence?

[deko]

Is there a way to reset the AutoNumber sequence?

I have several tables that use the AutoNumber field as the Primary Key, and
I'd like to somehow do an Import/Export that will make remove the breaks in
the sequence. A few breaks in sequence is not a big deal, but I have one
table with under 200 records, but the last AutoNumber PK ID field is over
1500 - due to a lot of edits....

[DFS]

deko,

* create new tables and import all fields except the AutoNumber -
problematic if you have parent/child relationships.

* drop the AutoNumber field, save the table, repair and compact the database
(just because), and add back an AutoNumber field - again, problematic if you
have parent/child relationships.

Recommendation (not being sarcastic): don't get hung up on sequential
AutoNumbers, or small AutoNumbers, or AutoNumbers matching the number of
records in the table. I have SQL Server tables with 8-digit AutoNumbers
(IDENTITYs in SQL).

Your pk fields aren't used for Account Numbers, Invoice Numbers, etc.,
right? So it's just a unique number, and small - let it be.

"deko" <dj****@hotmail.com> wrote in message
news:BC***********@newssvr29.news.prodigy.com...

Is there a way to reset the AutoNumber sequence?

I have several tables that use the AutoNumber field as the Primary Key, and I'd like to somehow do an Import/Export that will make remove the breaks in the sequence. A few breaks in sequence is not a big deal, but I have one
table with under 200 records, but the last AutoNumber PK ID field is over
1500 - due to a lot of edits....

[deko]

> Your pk fields aren't used for Account Numbers, Invoice Numbers, etc.,

right? So it's just a unique number, and small - let it be.

The ID numbers are visible to the user and can be used for searching, but
sequence is not really important. I don't expect the Entity table to exceed
100,000 records, and a 4 or 5 digit Entity_ID is just fine. But I'd like
the "Help" entry to be Entity_ID 1 - it would be nice to purge the test data
and insert the Help entry as the first record.

Perhaps I could create a blank database, import just the table structures,
then drop the ID fields on the original database, and import just the data -
would this re-order all the AutoNumber fields? How can I import the table
data without the structures?

Could I use this code instead of Auto Number? Or is this needless overhead?

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select max(Entity_ID) as imax from
tblEntity")
rst.MoveFirst
Me!Entity_ID = rst!imax + 1
rst.Close
Set db = Nothing
Set rst = nothing

End Sub

[Trevor Best]

On Tue, 13 Jan 2004 06:17:24 GMT in comp.databases.ms-access, "deko"
<dj****@hotmail.com> wrote:

Your pk fields aren't used for Account Numbers, Invoice Numbers, etc.,
right? So it's just a unique number, and small - let it be.
The ID numbers are visible to the user and can be used for searching, but
sequence is not really important. I don't expect the Entity table to exceed
100,000 records, and a 4 or 5 digit Entity_ID is just fine. But I'd like
the "Help" entry to be Entity_ID 1 - it would be nice to purge the test data
and insert the Help entry as the first record.

Compacting usually resets to the highest number in the table + 1 so
compact after deleting test data.
Perhaps I could create a blank database, import just the table structures,
then drop the ID fields on the original database, and import just the data -
would this re-order all the AutoNumber fields? How can I import the table
data without the structures?
Using the import wizard, import into an existing table, or you can
link the tables from the other database and run a series of append
queries.
Could I use this code instead of Auto Number? Or is this needless overhead?

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select max(Entity_ID) as imax from
tblEntity")
rst.MoveFirst
Me!Entity_ID = rst!imax + 1
rst.Close
Set db = Nothing
Set rst = nothing

End Sub

It's an overhead, whether it's needless is a matter of opinion, the
pros can outweigh the cons. This way you'll get no gaps (until someone
deletes a record). There's many ways of rolling your own autonumber,
from the simple DMax()+1 to the more complex of having a table full of
table names and autonumber values, the latter checking the target
table in case it already exists and adding another 1, etc, which would
mean you could reset to 1 and re-use any gaps left by deletion.

However I wouldn't do this as early as Form_BeforeInsert() as you'll
duplicate the number as soon as two people enter a record at the same
time. Perhaps in Form_BeforeUpdate() and check to see if on a new
record or that the ID is null first.

--
A)bort, R)etry, I)nfluence with large hammer.

[deko]

> Using the import wizard, import into an existing table, or you can

link the tables from the other database and run a series of append
queries.

I think that's the ticket...

* create new.mdb
* import all objects, definition only
* link to tables in orig.mdb
* run a series of append queries

I'm going to try to gin this up in vba (everything after create new.mdb)

Should I use the CreateWorkspace method? What are Workspaces, anyway?
Can I use DoCmd.TransferDatabase and loop through each Form, Query, Report,
Module and Table? How to get list of objects from orig.mdb?
I think I can figure out how to link all the tables, and setting up the
append queries shouldn't be too difficult.

Here's some pseudo code as a first draft - suggestions welcome!

'[standard module in new.mdb]
Public Sub ImportLinkAppend()

' import all the objects from orig.mdb
For Each Table In tbldefs '<<== how to programatically get list of
tables in orig.mdb?
DoCmd.TransferDatabase acImportworkspace, "Microsoft Access",
"C:\orig.mdb", acTable, varOrigTableName, varNewTableName, True
Next

' For Each Form In ...
'
' For Each Query In ...
'
' For Each Report In ...
'
' For Each Module In ...
' link to the tables in orig.mdb
Set tdf = db.CreateTableDef(varTbl)
strLink = "Access 11;DATABASE=C:\orig.mdb"
tdf.Connect = strLink
tdf.SourceTableName = "varTbl"
db.TableDefs.Append tdf '<<== will this cause problems since the table
names will be the same?

' run append queries
DoCmd.RunSQL "INSERT INTO varNewTableName SELECT varOldTableName.* FROM
varOldTableName"

End Sub

[deko]

Here's another hack at it.....

Private Sub ImportObjects()
Dim wrkJet As DAO.Workspace
Dim con As DAO.Container
Dim db As DAO.Database
Dim doc As DAO.Document
Dim varName, varContainer As Variant
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
For Each varContainer In Array("Tables", "Forms", "Reports", "Modules",
"Queries")
Set con = db.Containers(varContainer)
For Each doc In con.Documents
Select Case doc.Name ???
Case "Tables"
DoCmd.TransferDatabase acImport, "Microsoft Access",
"C:\orig.mdb", acTable, varName, varName, True
Case "Queries"
Case etc...
End Select
Next
Next
End Sub

++++++++++++++++++++

If I do succeed in importing all the objects this way, will the table
relationships be preserved? If I go to File >> Get External Data >> Import,
and select orig.mdb, the "Import Objects" window appears with a check box
for Importing Relationships - how do I specify this in code? The assumption
here is that I can emulate the functionality of that Import Objects window
with code... what about hidden and system objects???

[deko]

When I run code to inventory my database, I get some "Queries" that look
like this:

~sq_ffrmTxImport

I also get all the normal looking queries, like:

qry100

Does the "~" mean it's some kind of temp object?

Below is complete code of how I get inventory

Option Compare Database
Option Explicit
Private Sub AddInventory(strContainer As String)
Dim con As DAO.Container
Dim db1, db2 As DAO.Database
Dim doc As DAO.Document
Dim rst As DAO.Recordset
Dim intI As Integer
Dim strType As String
Dim varRetval As Variant
Dim wrkJet As DAO.Workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db2 = wrkJet.OpenDatabase("C:\orig.mdb", True)
Set db1 = CurrentDb
Set rst = db1.OpenRecordset("tblInventory")
Set con = db2.Containers(strContainer)
For Each doc In con.Documents
If Not isTemp(doc.Name) Then
If strContainer = "Tables" Then
strType = IIf(isTable(doc.Name), "Tables", "Queries")
Else
strType = strContainer
End If
With rst
.AddNew
!Container = strType
!Owner = doc.Owner
!Name = doc.Name
!DateCreated = doc.DateCreated
!LastUpdated = doc.LastUpdated
.Update
End With
End If
Next doc
End Sub
Private Sub CreateInventory()
If (CreateTable()) Then
Call AddInventory("Tables")
Call AddInventory("Forms")
Call AddInventory("Reports")
Call AddInventory("Scripts")
Call AddInventory("Modules")
Call AddInventory("Relationships")
Else
MsgBox "Unable to create tblInventory."
End If
End Sub
Private Function CreateTable() As Boolean
On Error GoTo HandleErr
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()
db.Execute "Drop Table tblInventory"
strSql = "CREATE TABLE tblInventory (Name Text (255), " & _
"Container Text (50), DateCreated DateTime, " & _
"LastUpdated DateTime, Owner Text (50), " & _
"ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSql
db.TableDefs.Refresh
CreateTable = True
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case 3376, 3011 ' Table or Object not found
Resume Next
Case Else
CreateTable = False
End Select
Resume Exit_Here
End Function
Private Function isTable(ByVal strName As String)
On Error Resume Next
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim wrkJet As DAO.Workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
Set tdf = db.TableDefs(strName)
isTable = (Err = 0)
On Error GoTo 0
End Function
Private Function isTemp(ByVal strName As String)
isTemp = Left(strName, 7) = "~TMPCLP"
End Function
Private Sub cmdCreateInventory_Click()
On Error Resume Next
Me!frmInventoryDatasheet.Form.RecordSource = ""
Call CreateInventory
Me!frmInventoryDatasheet.Form.RecordSource = "tblInventory"
Me!lblObjCount.Caption = DCount("Name", "tblInventory") & " Objects"
End Sub
Private Sub Form_Open(Cancel As Integer)
Me!frmInventoryDatasheet.Form.RecordSource = ""
End Sub

[Michael \(michka\) Kaplan [MS]]

They are temp queries, designed to improve performance when forms/reports
are based on SQL statements.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"deko" <dj****@hotmail.com> wrote in message
news:fl***************@newssvr29.news.prodigy.com. ..

When I run code to inventory my database, I get some "Queries" that look
like this:

~sq_ffrmTxImport

I also get all the normal looking queries, like:

qry100

Does the "~" mean it's some kind of temp object?

Below is complete code of how I get inventory

Option Compare Database
Option Explicit
Private Sub AddInventory(strContainer As String)
Dim con As DAO.Container
Dim db1, db2 As DAO.Database
Dim doc As DAO.Document
Dim rst As DAO.Recordset
Dim intI As Integer
Dim strType As String
Dim varRetval As Variant
Dim wrkJet As DAO.Workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db2 = wrkJet.OpenDatabase("C:\orig.mdb", True)
Set db1 = CurrentDb
Set rst = db1.OpenRecordset("tblInventory")
Set con = db2.Containers(strContainer)
For Each doc In con.Documents
If Not isTemp(doc.Name) Then
If strContainer = "Tables" Then
strType = IIf(isTable(doc.Name), "Tables", "Queries")
Else
strType = strContainer
End If
With rst
.AddNew
!Container = strType
!Owner = doc.Owner
!Name = doc.Name
!DateCreated = doc.DateCreated
!LastUpdated = doc.LastUpdated
.Update
End With
End If
Next doc
End Sub
Private Sub CreateInventory()
If (CreateTable()) Then
Call AddInventory("Tables")
Call AddInventory("Forms")
Call AddInventory("Reports")
Call AddInventory("Scripts")
Call AddInventory("Modules")
Call AddInventory("Relationships")
Else
MsgBox "Unable to create tblInventory."
End If
End Sub
Private Function CreateTable() As Boolean
On Error GoTo HandleErr
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb()
db.Execute "Drop Table tblInventory"
strSql = "CREATE TABLE tblInventory (Name Text (255), " & _
"Container Text (50), DateCreated DateTime, " & _
"LastUpdated DateTime, Owner Text (50), " & _
"ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSql
db.TableDefs.Refresh
CreateTable = True
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case 3376, 3011 ' Table or Object not found
Resume Next
Case Else
CreateTable = False
End Select
Resume Exit_Here
End Function
Private Function isTable(ByVal strName As String)
On Error Resume Next
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim wrkJet As DAO.Workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
Set tdf = db.TableDefs(strName)
isTable = (Err = 0)
On Error GoTo 0
End Function
Private Function isTemp(ByVal strName As String)
isTemp = Left(strName, 7) = "~TMPCLP"
End Function
Private Sub cmdCreateInventory_Click()
On Error Resume Next
Me!frmInventoryDatasheet.Form.RecordSource = ""
Call CreateInventory
Me!frmInventoryDatasheet.Form.RecordSource = "tblInventory"
Me!lblObjCount.Caption = DCount("Name", "tblInventory") & " Objects"
End Sub
Private Sub Form_Open(Cancel As Integer)
Me!frmInventoryDatasheet.Form.RecordSource = ""
End Sub

[deko]

> They are temp queries, designed to improve performance when forms/reports

are based on SQL statements.

10-4 ... thx for the clarification

As for AutoNumber re-sequencing, the plan is:

* create new.mdb
* import all objects, definition only
* link to tables in orig.mdb
* run a series of append queries

I'm not sure using code for the object imports is such a great idea... still
working on that (could just do it manually) - but this code should pull the
data from orig.mdb after I've imported the table definitions (into new.mdb)
and deleted the AutoNumber columns from the tables in orig.mdb. This should
re-sequence the AutoNumber IDs, removing any breaks in sequence. Again, any
suggestion for improvement welcome...
Private Sub GetData()

Dim db, dbs As DAO.Database
Dim obj As Object
Dim strPath As String
Dim wrkJet As DAO.Workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
Set dbs = CurrentDb
strPath = "C:\orig.mdb"
For Each obj In db.TableDefs
If Left(obj.Name, 2) <> "MS" And Left(obj.Name, 1) <> "~" Then
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath,
acTable, obj.Name, obj.Name & "_orig"
'Debug.Print obj.Name & " connected"
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO " & obj.Name & " SELECT " & obj.Name
& "_orig.* FROM " & obj.Name & "_orig")
DoCmd.SetWarnings True
dbs.Execute "DROP TABLE " & obj.Name
End If
Next

End Sub

[Trevor Best]

On Tue, 13 Jan 2004 17:44:06 GMT in comp.databases.ms-access, "deko"
<dj****@hotmail.com> wrote:

If Left(obj.Name, 2) <> "MS" And Left(obj.Name, 1) <> "~" Then
You may be wiser to your naming conventions than I but I think you
should exclude "MSys*" rather than just "MS*"
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO " & obj.Name & " SELECT " & obj.Name
& "_orig.* FROM " & obj.Name & "_orig")
DoCmd.SetWarnings True
dbs.Execute "DROP TABLE " & obj.Name

While it may be nice to have the percentage complete provided by the
DoCmd, it also requires you turn warnings off as you have in the code
above. You must make sure you turn it on again if any error occurs
that prevents the next line running. Also it will suppress some lesser
(but no means less important) errors. It is also the slowest method of
running a query, better to use

dbs.Execute "insert into...", dbFailOnError

to catch any errors that do occur, although copying to the same
structure of table, none should occur but then if it was always that
easy then people wouldn't pay me to do it :-)

--
A)bort, R)etry, I)nfluence with large hammer.

[deko]

Hi Trevor,

Thanks for the tip.

Here's the latest - so close but yet so far...

The problem is trying to loop through each field in the linked table, and
then dropping any field that is an AutoNumber field and ends in "ID". How
can I do this?

Private Sub cmdGetData_Click()
Dim db, dbs As DAO.Database
Dim obj As Object
Dim fld As DAO.Field '<<== should this be something else?
Dim idx As DAO.Index
Dim strPath As String
Dim wrkJet As DAO.Workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
Set dbs = CurrentDb
strPath = "C:\orig.mdb"
For Each obj In db.TableDefs
If Left(obj.Name, 3) = "tbl" Then
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath,
acTable, obj.Name, obj.Name & "_orig"
For Each fld In db.obj '<<== error here: "Object Doesn't support
this property or method"
If Right(fld.Name, 2) = "ID" And fld.Type = dbAutoIncrField
Then
Debug.Print obj.Name & " has AutoNum ID"
db.Execute "DROP " & fld.Name
End If
Next
dbs.Execute ("INSERT INTO " & obj.Name & " SELECT " & obj.Name &
"_orig.* FROM " & obj.Name & "_orig"), dbFailOnError
dbs.Execute "DROP TABLE " & obj.Name
End If
Next
End Sub

[deko]

This seems to be working. developing...

Question: why does 17 means dbAutoIncrField ????
Private Sub cmdGetData_Click()
Dim db, dbs As DAO.Database
Dim obj As Object
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strPath As String
Dim varOrig As Variant
Dim wrkJet As DAO.Workspace
Set dbs = CurrentDb
dbs.TableDefs.Refresh
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
strPath = "C:\orig.mdb"
For Each obj In db.TableDefs
If Left(obj.Name, 3) = "tbl" Then
varOrig = obj.Name & "_orig"
Set tdf = db.TableDefs(obj.Name)
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath,
acTable, obj.Name, varOrig
For Each fld In tdf.Fields
If Right(fld.Name, 2) <> "ID" And
fld.Properties("Attributes") <> 17 Then '17 means dbAutoIncrField ????????
'Debug.Print obj.Name & " has AutoNum ID " & "= " &
fld.Name
Debug.Print "inserting data from " & obj.Name & "." &
fld.Name
dbs.Execute ("INSERT INTO " & obj.Name & " SELECT " &
varOrig & "." & fld.Name & " FROM " & varOrig)
End If
Next
dbs.Execute "DROP TABLE " & varOrig
End If
Next
End Sub

[deko]

Need to drop the autonumber column on the linked table before running the
SQL - is this possible?

There isn't a DROP COLUMN function yet, but you can do this...
SELECT ... -- select all columns but the one you want to remove
INTO TABLE new_table
FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

Here's the latest

Private Sub ReSequenceAutoNumber()
Dim db, dbs As DAO.Database
Dim obj As Object
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strPath As String
Dim varOrig As Variant
Dim wrkJet As DAO.Workspace
Set dbs = CurrentDb
dbs.TableDefs.Refresh
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\orig.mdb", True)
strPath = "C:\orig.mdb"
For Each obj In db.TableDefs
If Left(obj.Name, 3) = "tbl" Then
varOrig = obj.Name & "_orig"
Set tdf = db.TableDefs(obj.Name)
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath,
acTable, obj.Name, varOrig
For Each fld In tdf.Fields 'this does not work properly
If fld.Properties("Attributes") <> 17 Then 'not sure why,
but 17 seems to indicate AutoNumber field
dbs.Execute ("INSERT INTO " & obj.Name & " SELECT " &
varOrig & ".* FROM " & varOrig)
End If
Next
dbs.Execute "DROP TABLE " & varOrig
End If
Next
End Sub