alternative to autonumber key – multiple dbs (2000/XP)

Topic

New Reply

I need to alter the db described in my earlier post 559,230 to avoid having to repeat the task decribed (altering the values in an autonumber key whilst mainting integrety with the child table).

Copies of the database need to be made for use by users on remote machines – currently with no network access when out of the office).
Avoiding conflicts in the autonumber key is currently done by ‘seeding’ the autonumber for each copy so the users start on a diferent number.

I have looked (briefly) into replication but I don’t think it is the right solution to my problem (issues include the users need to delete records) so no suggestions about replication please.

Would it be possible to generate a key similar to a GUID (maybe combining the computer name and a time stamp to create a string) to ensure the value is unique. If so, is this fairly straightforward – any examples – or am I likley to run into trouble?

Reply | Quote

Replies

One way to get around this would be to use an AutoNumber field with the New Values property set to Random. Each time a new record is created, a random value (between -2,147,483,648 and +2,147,483,647) is assigned. The probability of the same number being assigned twice is VERY small.

Another way would be to us a Long Integer field whose value is determined by the system time. You could set the Default Value property of the field to

=((Date()-38500)*100000+Timer())*10

The users would have to enter a record in the same 10th of a second to get the same value. I have used this method without problems in databases installed on stand-alone laptops for data entry.

You could concatenate it with the computer name to create a string primary key if you’re still concerned about duplicates; this would have to be done in the BeforeInsert event of a form based on the table, since you can’t use custom functions in the Default Value property of a field. See post 509,603 for the code needed to get the computer name.

Reply | Quote

Thanks everyone for your prompt replies.

Hans, I think your suggestion looks the most promising.
Of the two options you describe, which do you think is best ?

Mark, there are effectively two fields that could form the key (the ExamRef and Date) but I’m using the autonumber to link to a child table – my understanding is composite keys are a pain when child tables are involved – have I got this wrong?

Reply | Quote

Both options should woirk well work. The random autonumber option is the easiest to implement – no formulas, but is has the disadvantage that the default sort order of the records will be completely meaningless. You’ll want an additional Date/Time field with Default Value set to Now() if you are interested in the order in which the records were created.

Reply | Quote

Thanks, this all sounds too easy!

Seriously though, I really thought this was going to be a huge problem but now I’m confident Access is still up to the job.

Can you remember roughly how many users you had? The app I’m working on currently has 6-12 remote users entering around 6 records day. Ultimately this batch of work will amount to around 2000-3000 records, which will be added to an archive of 15,000 records (once I’ve sorted out the issue in post 559,230).

Reply | Quote

Hi Darsha

I will err to other Loungers if this will not work but I have used a probably too cunning a plan.

I have used a function to retrieve the unique PC’s name and placed it in a module

Private Declare Function apiGetComputerName Lib “kernel32” Alias _
“GetComputerNameA” (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSMachineName() As String
‘Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = “”
End If
End Function

I have then created a query that concatanates the ID of tblName into a query called qryNewID. You could make a make table query from this query and use it to append to a main database. This will allow uniqueness of your table. I have attached the database for you to look at.

Reply | Quote

Another alternative is to have a 2-field PK. One field would be a SiteCode, while the other would be the autonumber field. This way, you don’t care if the autonumbers duplicate, since it is the combination of the 2 fields that will always be unique.

Reply | Quote

One set of databases was installed on3 laptops, being used simultaneously, with hundreds of records being added per day. Consolidating the tables was never a problem.

You’ll have 6 records a day for each of 6 to 12 users? Then the probability of two records being entered in the same 1/10th of a second is extremely small!

Reply | Quote

Excellent. I will probably go with a random autonumber key and maybe have a ‘date created’ to sort by.

Reply | Quote