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.n ews.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_BeforeInse rt(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordse t("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_BeforeInse rt(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordse t("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_BeforeInse rt() as you'll
duplicate the number as soon as two people enter a record at the same
time. Perhaps in Form_BeforeUpda te() 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.TransferD atabase 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 ImportLinkAppen d()

' import all the objects from orig.mdb
For Each Table In tbldefs '<<== how to programatically get list of
tables in orig.mdb?
DoCmd.TransferD atabase acImportworkspa ce, "Microsoft Access",
"C:\orig.md b", acTable, varOrigTableNam e, 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.CreateTableD ef(varTbl)
strLink = "Access 11;DATABASE=C:\ orig.mdb"
tdf.Connect = strLink
tdf.SourceTable Name = "varTbl"
db.TableDefs.Ap pend 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.OpenData base("C:\orig.m db", True)
For Each varContainer In Array("Tables", "Forms", "Reports", "Modules",
"Queries")
Set con = db.Containers(v arContainer)
For Each doc In con.Documents
Select Case doc.Name ???
Case "Tables"
DoCmd.TransferD atabase acImport, "Microsoft Access",
"C:\orig.md b", 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_ffrmTxImpor t

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(st rContainer 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.OpenData base("C:\orig.m db", True)
Set db1 = CurrentDb
Set rst = db1.OpenRecords et("tblInventor y")
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("T ables")
Call AddInventory("F orms")
Call AddInventory("R eports")
Call AddInventory("S cripts")
Call AddInventory("M odules")
Call AddInventory("R elationships")
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, " & _
"LastUpdate d DateTime, Owner Text (50), " & _
"ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSql
db.TableDefs.Re fresh
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.OpenData base("C:\orig.m db", True)
Set tdf = db.TableDefs(st rName)
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 cmdCreateInvent ory_Click()
On Error Resume Next
Me!frmInventory Datasheet.Form. RecordSource = ""
Call CreateInventory
Me!frmInventory Datasheet.Form. RecordSource = "tblInvento ry"
Me!lblObjCount. Caption = DCount("Name", "tblInvento ry") & " Objects"
End Sub
Private Sub Form_Open(Cance l As Integer)
Me!frmInventory Datasheet.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******** *******@newssvr 29.news.prodigy .com...

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

~sq_ffrmTxImpor t

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(st rContainer 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.OpenData base("C:\orig.m db", True)
Set db1 = CurrentDb
Set rst = db1.OpenRecords et("tblInventor y")
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("T ables")
Call AddInventory("F orms")
Call AddInventory("R eports")
Call AddInventory("S cripts")
Call AddInventory("M odules")
Call AddInventory("R elationships")
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, " & _
"LastUpdate d DateTime, Owner Text (50), " & _
"ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSql
db.TableDefs.Re fresh
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.OpenData base("C:\orig.m db", True)
Set tdf = db.TableDefs(st rName)
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 cmdCreateInvent ory_Click()
On Error Resume Next
Me!frmInventory Datasheet.Form. RecordSource = ""
Call CreateInventory
Me!frmInventory Datasheet.Form. RecordSource = "tblInvento ry"
Me!lblObjCount. Caption = DCount("Name", "tblInvento ry") & " Objects"
End Sub
Private Sub Form_Open(Cance l As Integer)
Me!frmInventory Datasheet.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.OpenData base("C:\orig.m db", True)
Set dbs = CurrentDb
strPath = "C:\orig.md b"
For Each obj In db.TableDefs
If Left(obj.Name, 2) <> "MS" And Left(obj.Name, 1) <> "~" Then
DoCmd.TransferD atabase acLink, "Microsoft Access", strPath,
acTable, obj.Name, obj.Name & "_orig"
'Debug.Print obj.Name & " connected"
DoCmd.SetWarnin gs False
DoCmd.RunSQL ("INSERT INTO " & obj.Name & " SELECT " & obj.Name
& "_orig.* FROM " & obj.Name & "_orig")
DoCmd.SetWarnin gs 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.SetWarnin gs False
DoCmd.RunSQL ("INSERT INTO " & obj.Name & " SELECT " & obj.Name
& "_orig.* FROM " & obj.Name & "_orig")
DoCmd.SetWarnin gs 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.