Re-Seed Autonumber Field in a Remote Access Database

[RDub]

I think I need to be able to mess around the Autonumber Seed values in a
couple of Access databases. Yea I know this is a bad practice, but I just
need to get this done so I can live to fight the real battle another day.

Basically the deal is that I have a pair of standalone databases that are in
different locations. These databases have a lifespan of 4 Days. Yup after
4 days the information contained in these things is useless, and we'll throw
them away and never use of even have to refer to them again. The deal is
that the customer wants to synchronize the data in these things at the end
of each day, so that a record created in one of em would have migrated to
the other at the end of the day. There is not network, no internet, no
nothing, other than a Sneaker Net at the end of the day to connect them.

So my Plan was to start out the databases with one tables Seed value about
1,000,000 ahead of the others. Then at the end of each day link the two
together with a third synchronizer database, and move new records from
hither to yon and vise versa. No problem easy peasy.

However, after running the two append queries Access likes to change the
Autonumber seed to be one higher than the last record that was inserted. I
guess that this isn't that bad as the numbers are still gonna be unique, and
the database(s) doesn't use em for anything else. UNLESS heaven forbid the
customer comes up with a request for a third or fourth database.

So anyway I figured I'll just reset autonumber seed to an appropriate value
after running the Insert queries. This turned out to be more of a problem
than I bargained for. After trying and failing a number of ways to do this
I found something that works, BUT I hate it. I am Automating Access! Yuk
:-( Here is the code I am using.

' ************** Code Follows **************

Private Sub ResetAutoNumberSeed(strDatabaseName As String, _
strTablename As String, _
strColumnName As String, _
Optional lngNewStartValue As Long = 1, _
Optional lngNewIncrement As Long = 1)
' Purpose Re-seed an Access Auto Number field in a Remote database
' Uses Late Binding
'
Dim accDb As Object

On Error GoTo err_ResetAutoNumberSeed
Set accDb = CreateObject("Access.Application")
accDb.OpenCurrentDatabase strDatabaseName
accDb.Visible = False
' Reset the Seed by running a
accDb.DoCmd.RunSQL "Alter Table " & strTablename & " Alter Column " &
strColumnName _
& " Counter(" & lngNewStartValue & "," &
lngNewIncrement & ")"
accDb.CloseCurrentDatabase
err_ResetAutoNumberSeedResume:
Set accDb = Nothing
Exit Sub
err_ResetAutoNumberSeed:
MsgBox "Error Resetting the Autonumber Seed Value!" & vbCrLf & vbCrLf _
& "Error number: " & Err.Number & vbCrLf _
& "Error Descr: " & Err.Description, vbExclamation, "MyApp"
Resume err_ResetAutoNumberSeedResume
End Sub
' ************** Code Ends **************

Can anyone suggest a better way to do this?

Rdub

 

[Albert D. Kallal]

Why not make the primary key an autonumber + location field.

That why, you can merge records from any, and all of the databases, and
NEVER worry about the primary key collisions, because you added a location
part to the primary key, and now don't care.

Really, internal autonumbers are something for computers...and I would NEVER
take ANY programming time to worry about what the actual number is, no more
so then what the memory segment number word is about to use to load a
document into memory. These are internal computer numbers....as a developer
don't worry about them.....

Just create a autonumber field "id", but don't make it the primary key
(allow duplicates). You then simply add a location field, and then combine
both of these to be the primary key. (you can use more then one field as a
PK in ms-access -- just highlight both fields in design view, and whack the
primary key button on the tool bar).

There should be no reason or rhyme or any care in your mind as to what the
actual auto number value is. If you need some sequence numbering for today,
then you add a date field + some custom numbering scheme that you build in
code. You simply can't worry about the autonumber values....

 

[RDub]

Albert

You are right of course.

Problem is that the database has any number of places where it uses the
Autonumber field (which it expects will be unique) to display, print,
duplicate, or delete records. I really don't want to change all of the code
in the database (possibly creating subtle new bugs on the way) to
accommodate one customer for a 4 day event.

Unless I can come up with another alternative I fear I am going to have to
put up with the screen winking and blinking as my Access automation code
runs TWICE (Hopefully the client will not come up with a 3rd or 4th non
networkable location) once for each database.

Every other place on the planet where we have used this application, we
ALWAYS were able to use a network and link the workstations to a Server. I
guess I just never knew how good I had it. :-(

Rdub

 

[Vsn]

Albert,

Would there be a way that this PK field (as you suggest autonum + loc) be
created automaticaly at the moment the user add a value to the record, by
using a Default value? Could this set up be done during table design?

Thx,
Ludovic

 

[Albert D. Kallal]

Albert,

Would there be a way that this PK field (as you suggest autonum + loc) be
created automaticaly at the moment the user add a value to the record, by
using a Default value? Could this set up be done during table design?

Thx,
Ludovic

Yes, that normal how it works. You just set the default "location" to the
location value you want. This way, you never care about the autonumber
because the pk is composed of two values. You can also set the default on a
form via a default for a bound control, or even code.

Note you *can* run code to "set" (re-seed) the next autonumber. (as long as
your moving the number "up", and not down), you can use:

Sub SetNextID()

Dim strTable As String
Dim strSql1 As String
Dim strSql2 As String
Dim ibuf As String
Dim nextvalue As Long


strTable = InputBox("What table to modify")
If strTable = "" Then Exit Sub

strSql1 = "INSERT INTO " & strTable & " (ID) SELECT TOP 1 "
strSql2 = "DELETE ID FROM " & strTable & " WHERE ID = "

ibuf = InputBox("Enter next value (blank enter will exit)")
If ibuf <> "" Then
nextvalue = ibuf - 1
CurrentDb.Execute strSql1 & nextvalue & " AS Expr1 from " & strTable
' now delete this guy
CurrentDb.Execute strSql2 & nextvalue
End If

End Sub

 

[Vsn]

Al bert, thx I will try to utilize your advice.
Ludovic