reset autonumber

[Mohan]

Hi

I am using a table to import data from text files. the text filess are
daily files with about 60K rows per day. So after one month, I clean out the
data and I want to reset the auto number

I am using the follwoing code to reset the auto number:

===========

Public Sub ResetAutoNumSeed(sTableName As String, sFieldName As String, num
As Long)

On Error GoTo ErrHandler


Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTableName)
Set col = tbl.Columns(sFieldName)
col.Properties("Seed") = num

CleanUp:

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in resetAutoNumSeed( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

==============
But the problem is its working if the table is in the sam mdb file as the
code. It won't work if the table is a linked table. How can I reset the
auto number of a linked table?

One option I thought of: Put the same code in the db where the table
resides and call this from my fron-end code access file. But I don't know how
to do this..

Can anyone help!!

Thanks
Mohan

 

[Hunter57]

Hi,

You could delete the table and then create it again by modifying your
current proceedure. This would automatically reset the autonumbers.

For more help see:
http://allenbrowne.com/func-ADOX.html

Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

 

[Hunter57]

Hi Mohan,

Sorry, I forgot to mention. All you have to do is add a connection string
to your Back End Database to run your proceedure from the front end.

Pat Wood

 

[Jamie Collins]

You could delete the table and then create it again by modifying your
current proceedure. This would automatically reset the autonumbers.

Note that the disk space is not recovered until the file is compacted,
therefore this approach is to be avoided in Jet.

Autonumbers are not recommended to generate sequence. A *permanent*
table of integers is a standard way of relying on a sequence.

Jamie.

--

 

[Hunter57]

Hi Mohan,

Here is an example of a Sub based on your code that you should be able to
use from the front end to work with the back end.

Public Sub ResetAutoNumSeed(sTableName As String, sFieldName As String, num
As Long)

On Error GoTo ErrHandler

Dim cat As ADOX.Catalog ' Took out "New" to use later
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
' Enter the full path to your backend with filename here
.Properties("Data Source") = "C:\myDBpath\myDBName"
.Open
End With

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn

' The rest is your code

Set tbl = cat.Tables(sTableName)
Set col = tbl.Columns(sFieldName)
col.Properties("Seed") = num

CleanUp:

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in resetAutoNumSeed( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

Be sure to back up your backend before you do this.

After running this you can compact and repair the backend DB if needed to
reduce bloat.

Please let us know if you have found a satisfactory solution.

Best Regards,
Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com