Re: Renumbering records
- From: "David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx>
- Date: Tue, 31 Jul 2007 14:25:17 -0500
Kenny Anderson <Kenny Anderson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:E0B9A846-8E5F-491F-AA9F-646A761BD708@xxxxxxxxxxxxx:
"David W. Fenton" wrote:
[]
1. pre-allocate blocks of the sequence to each replica.
2. use a two-column sequence instead, with one column indicating
the source replica, and the other the next sequential value.
3. assign temporary values in the replicas and then assign the
permanent values in a single administrative replica.
[]
I, too, am apparently using autonumber *incorrectly* but I don't
understand the options you've provided to remedy the situation. A
few posts later in this thread you also say that the DesignMaster
should not be used for data storage and regular synchronization?
I am doing that, too, but it appears my best solution is eliminate
replication. In fact, I don't really want true replication, but
MS gave me no choice. I tried copying my DB into the Briefcase on
my laptop and it did this by default. I didn't notice until I
started getting erratic results in my autonumber field.
You can avoid briefcase replication if you uninstall the Access
Briefcase Replication component. Just rerun Access setup and uncheck
that component. Then the next time you copy an Access database to
the briefcase, it won't be replicated.
Here are some links explaining how to unreplicate:
* Access 2002 instructions (should work for A2003, too)
http://support.microsoft.com/kb/290052/
* Access 2000 instructions
http://support.microsoft.com/kb/208394/
* Access 97 instructions
http://support.microsoft.com/kb/153526/
The A97 article also has a download link for the Jet 3.5
unreplication wizard:
http://support.microsoft.com/kb/168398/
There are two other tools that also work for Jet 4 databases:
* Michael Kaplan's TSI Un-Replicator Add-in
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7
* Graham Search's Unreplicate Utility
http://www.pacificdb.com.au/MVP/Code/UnReplicate.htm
The latter has code, and you could perhaps learn something by
reviewing the code, but I've never actually used the latter (I've
hardly ever unreplicated anything).
Here is my situation:
I am maintaining a database of products that we receive for
review. Since 1977, these have been maintained with an
identification number that begins with sequential component. I am
using the autonumber feature to automate this process, which
worked perfectly until I copied to my briefcase. I do not need a
primary key, though I could easily assign this to the autonumber
field. Is there a way to automatically generate sequential
numbers other than the autonumber feature? Again, I'm sorry, but
I didn't understand the options provided above.
A class module that I use for this is posted after my signature. The
rule used there is to increment, but you could create any kind of
sequence you wanted. However, I would caution against trying to pack
multiple sequences into a single field -- that's denormalized and
cause problems, particularly if the parts have independent meanings.
In that case, you should use a composite key with multiple fields.
As to primary keys, you should unquestionably be using primary keys.
If you're not, then you don't really have a database!
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Option Compare Database
Option Explicit
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Based on code From Access 97 Developer's Handbook
' by Litwin, Getz and Gilbert. (Sybex)
' Copyright 1997. All Rights Reserved.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Some Modified by Jeremy Wallace, AlphaBet City Dataworks
' www.ABCDataworks.com
' 2003 03 25
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Converted to Class by David W. Fenton, David Fenton Associates
' http://dfenton.com/DFA/
' 2003 07 18
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private dbAutoNumber As DAO.Database
' Number of times to retry in case of locking conflicts
Const adhcMaxRetries = 5
'Error constants
Const adhcErrRI = 3000
Const adhcLockErrCantUpdate2 = 3260
Const adhcLockErrTableInUse = 3262
Private Function adhGetNextAutoNumber(ByVal strTableName As String,
Optional ByVal ysnCommit As Boolean = True) As Long ' Returns the
next custom autonumber value for a particular ' table. autonumbers
are stored in the database adhcAutoNumDb ' in tables with _ID
appended for which they supply autonumbers. ' Returns -1 if a valid
autonumber value cannot be retrieved ' due to locking problems.
'
' From Access 97 Developer's Handbook
' by Litwin, Getz and Gilbert. (Sybex)
' Copyright 1997. All Rights Reserved.
'
' In:
' strTableName: the name of the table for which the autonumber
' is to be retrieved.
' Out:
' Return value: A Long containing the new autonumber value,
' or -1 if the routine couldn't provide a autonumber value.
' Example:
' lngNewID = adhGetNextAutoNumber(strTableName)
' ALTERED BY DWF, 2003/07/18
' ysnCommit
' TRUE : get next value and commit that value to seed table
' FALSE : get next value but don't write it to seed table
' -- for testing purposes only
On Error GoTo adhGetNextAutoNumber_Err
Dim rstAutoNum As DAO.Recordset
Dim lngNextAutoNum As Long
Dim lngWait As Long
Dim lngX As Long
Dim intLockCount As Integer
DoCmd.Hourglass True
intLockCount = 0
' Open a recordset on the appropriate table in the
' autonumbers database denying all reads to others
' while it is open
Set rstAutoNum = dbAutoNumber.OpenRecordset(strTableName, _
dbOpenTable, dbDenyRead)
' Increment and return the autonumber value
rstAutoNum.MoveFirst
lngNextAutoNum = rstAutoNum!SeedID + 1
If ysnCommit Then
rstAutoNum.Edit
rstAutoNum!SeedID = lngNextAutoNum
rstAutoNum.Update
End If
adhGetNextAutoNumber = lngNextAutoNum
adhGetNextAutoNumber_Exit:
If Not (rstAutoNum Is Nothing) Then
rstAutoNum.Close
Set rstAutoNum = Nothing
End If
DoCmd.Hourglass False
Exit Function
adhGetNextAutoNumber_Err:
' Table locked by another user
If Err = adhcErrRI Or Err = adhcLockErrCantUpdate2 Or _
Err = adhcLockErrTableInUse Then
intLockCount = intLockCount + 1
' Tried too many times, give up
If intLockCount > adhcMaxRetries Then
adhGetNextAutoNumber = -1
Resume adhGetNextAutoNumber_Exit
Else
' Calculate the wait time based on
' the number of retries and a random number
lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
' Waste time, but let Windows
' multitask during this dead time
For lngX = 1 To lngWait
'DoEncounters
Next lngX
Resume
End If
' Unexpected error
Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbCritical, "adhGetNextAutoNumber"
adhGetNextAutoNumber = -1
Resume adhGetNextAutoNumber_Exit
End If
End Function
Private Sub Class_Initialize()
Set dbAutoNumber = DBEngine.OpenDatabase([insert your MDB here])
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Table structure of tblSeed:
SeedID
Long Integer, Primary Key Index
SingleRecordInsurance
Integer
Default Value = 1
Validation Rule = 1
.
- References:
- Re: Renumbering records
- From: Kenny Anderson
- Re: Renumbering records
- Prev by Date: Re: Renumbering records
- Previous by thread: Re: Renumbering records
- Index(es):
Relevant Pages
|
