Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I need to alter the db described in my earlier <post#=559,230>post 559,230</post#> 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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

    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>post 509,603</post:> for the code needed to get the computer name.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: alternative to autonumber key - multiple dbs (

    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.
    Jerry

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

    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>post 559,230</post#>).

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

    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!

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: alternative to autonumber key - multiple dbs (2000/XP)

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

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •