Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine data from multiple dbs into one (Access 2000 on WinNT4.0)

    I have multiple users each of whom have an access 2000 application. The users perform data entry while not connected to the network, and then make updates to a central db when the application sees a network connection. The data is stored in two tables with a 1 to many relationship with the tables related to an autonumber field. Duplicate records are not allowed. The central db needs the same structure. Here's my problem:

    Though the possibility of duplicates is not allowed on the user's app, each user will have a records with unique data BUT the multiple users will have the same autonumber value in their record's primary key. When the user data is combined in the central db, there is a guarantee separate users applying updates will create a many to many situation in the central db even though their records are unique on their local db's.

    Are there any thoughts or philosophies on how to combine this data and still maintain the data in the central location with the identical table structure? The current method I am using works but defeats the purpose of a relational db; I combine the data into one table without the local primary key and subsequently have redundant data in the table. I know this is wrong, but cannot think of a way to make it right in a relational environment.

    I appreciate your feedback.

    Happy Holidays,

    Dave Mack

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combine data from multiple dbs into one (Access 2000 on WinNT4.0)

    This is a classic case of what replication is all about. It does however come with considerable overhead. One possible solution would be to use a random autonumber rather than an autoincrementing one. If your users are entering lots of records the probability of a duplicate being entered increases over time. If the number of records is small, a manual conflict check might be a possibility. Otherwise you could use a GUID which has an extremely low probability of ever generating a duplicate.

    Do your users need to see the complete set of data at their individual workstations? If so then you should probably think seriously about using replication. It's not a trivial exercise to set up, but the Microsoft documentation is pretty decent. Hope this helps.
    Wendell

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine data from multiple dbs into one (Access 2000 on WinNT4.0)

    Thank you for your reply.

    The users need to see and review only the data recorded on their workstation. The central db is only for my use in producing management reports. Between 10 and 50 records are involved in updating. Another option might be to keep the autonumber and maybe concatenate an employee code prior to updating the central db. That would make things unique in the cental db.

    Replication and "my users" would be a nightmare. I remember when a coworker did replication and we shifted from Access 97 to 2000. That's what scares me about replication.

    These guys are doing data entry only and may run minor reports on only their data. If it weren't for the updates being done while not network connected, this whole thing would be real easy.

    I really appreciate you taking the time to offer multiple solutions. It's nice to know there are forums like this to bounce ideas off of the experienced folks...especially on a Sunday!

    Dave Mack


    <img src=/S/ribbon.gif border=0 alt=ribbon width=15 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combine data from multiple dbs into one (Access 2000 on WinNT4.0)

    Spot on - if you don't need all data at all workstations, avoid replication. We use it in some situations, but it certainly adds a level of complexity - I just finished undoing a replicated DB that a novice had created so that design changes could be introduced across disconnected workstations. Lots of fun to get rid of once you implement it, but they were upgrading from 97 to 2000 and it had to be done. Your idea of taking the autonumber field and concatenating an employee code is a good one - you'll want to convert it to text of course, but it can be indexed to make performance acceptable. Thanks for your nice comments - it is an incredible resource for sure.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combine data from multiple dbs into one (Access 2000 on WinNT4.0)

    The other method is to use a GUID as a key, since that's unique to the machine it was created on. However, they are *not* fun to work with and I avoid them if possible.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Jan 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine data from multiple dbs into one (Access 2000 on WinNT4.0)

    What if you assigned each user a different group of numbers to use as their primary key numbers? UserA could use numbers 1-999, UserB 1000-1999, UserC 2000-2999, etc., to eliminate duplicates?

Posting Permissions

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