Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Update Archive from Table line by line (Access 2007)

    Hi,

    Just out of curiosity, does any one have a solution for this one:

    We have a table that is updated row by row. We are looking to have a seperate table accross a network, on a different machine, that is also updated Row by Row andacts as a seperate table. We need to try and make sure that no "Unique Keys" are the same in either to ensure no duplication of Keys.

    (VBA solutions a preference)

    Is this possible from a Table (IE an active macro that tracks "OnExit" ??) and saves the row to a central archive referenced by both tables?

    Anyway hope thats clear!

    Gratitude in Anticipation

    Graeme

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

    Re: Update Archive from Table line by line (Access 2007)

    If the different PCs are on a network, why create separate tables? You could give each PC a frontend linked to a common backend database in a shared network folder. That way several PCs can add records to the same table. No need to transfer the records from the individual PCs to the central database.

    If you need separate databases, for example because the PCs will not always be connected to the network, you could use an AutoNumber field as key, with its New Values property set to Random instead of the default Sequence. Each new record will be assigned a random autonumber value. The probability that two records created on different computers will be assigned the same autonumber value is very small.

    Or use a Number (Long Integer) field as key with its Default Value set to a formula that generates a number based on the current date and time; if you take fractions of seconds into account, the probability of duplicates is again very small.

    Or use a composite key consisting of two fields: a standard AutoNumber field with New Values set to Sequence, plus another field that identifies the PC.

    Each of these options make it possible to add the records to the central database when the PC is connected to the network using an append query.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Archive from Table line by line (Access 2007)

    Thanks Hans.
    A few things to think about.
    My main concern is we have to physicly check the data input from printed pages so if we have two operators on different machine the pages will get out of order, which is the reason for seperate tables.
    I was thinking in the Small hours of the morning ("insomnia's a nightmare existance"), that we might be able to latch into the process via which Access saves each entry to the table/mdb file (you know it must do because when you have a power Cut you reopen the table and its all there apart from the row you were working on) after exiting the Row, but I don't know how to reference it.

    Anyway, unless anyone has any other thoughts, I'll have to come up with a compromise solution that may disrupt the current workflow (damn operators).

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

    Re: Update Archive from Table line by line (Access 2007)

    If you include the page number in the record, you don't have to worry about data being in the wrong order.

    Access doesn't have any events or hooks at the table level, so if operators enter data directly into a table (or query), there is no way to react to that.
    But forms have an After Update event that you can use to perform some action when a record has been saved, so if the operators use a form, you should be able to do what you want.

Posting Permissions

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