Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    ? Best Approach for updating Data (2002)

    Hi,

    I'm in the process of building a database for our marketing staff. They will be using their laptops on the road to input data on the sales calls they are making. When they come back to the office they will put their laptops back onto the networks to save the day's data back to the server. What is the best approach for doing this? I had thought about doing and append query to linked tables on the serve but wanted to avoid the error message that comes up with the database opens and it can't find the linked table.

    Thanks,
    Leesha

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

    Re: ? Best Approach for updating Data (2002)

    Access will not complain if there is a linked table whose source is not available until you actually try to use it, for example by executing a query involving the linked table, open a form based on it or open a recordset based on it. So unless you do one of these things automatically when opening the database, you should be OK.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: ? Best Approach for updating Data (2002)

    Do you tables involve autonumbers?

    If the sales staff create records in their "local" tables using autonumbers, then append these records to the main tables, these autonumbers will be reassigned. This would matter if the sales staff had also put data into related tables that used the autonumber as a foreign key.

    You might want to investigate replication, which is a strategy designed for this situation.
    WendellB has a tutorial on his website, To replicate, or not to replicate, that is the question!
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ? Best Approach for updating Data (2002)

    John mentioned tables with an Autonumber fields. You don't have to go the replication way.

    An alternative way is to define an additional field for the "source" laptops autonumber, this field would be a long integer field. I have used this technique for a ** Possible SPAM post - please alert a Moderator (2)** group here in Australia. An additional field is required for the laptop name.

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

    Re: ? Best Approach for updating Data (2002)

    As you can probably tell from our tutorial, I'm not a big fan of replication, but your type of application is one area where it is pretty useful. My decision criteria on whether or not to use replication would hinge on whether marketing staff need to see data entered by others when they are not connected to the network. If they do then I would look very seriously at using replication. I have a gut feeling this may become the source of many threads if you do! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Wendell

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Best Approach for updating Data (2002)

    Hi,

    I wasn't sure which post to respond to so picked the last one and will address my questions here. After reading the information on replication, I'm not sure that is my answer but am interested on whether sychronization can be done without the replication process. I as this because there are two main tables where the changes will be made. One contains activities based on daily activities and the other is the table that holds the referral source demographic data. Presently there are two individuals that will be carrying the database on their laptops and the data from these tables needs to be appended/replicated to the server each time they return to the office. (Yes there is an ID for each table that is an autonumber). It is feasible that the user will be making changes to the table that contains referral source demographic data and I need to have that data updated to the information on the server without duplicating the referral source itself. So, if referral source A has a change of address, only the address would change not the rest of the information pertaining to the referral source. Is this done via replication or is there another way to do it?

    I'm confused on John and Pat's idea using the autonumber. It makes sense in that there won't be duplicate entries, but how do I control for autonumbers taht are same from different computers? I've never heard of a foreign key and will need to look into that. If I understand Pat correctly, the additional key that identifies the laptop would be used in conjunction with the autonumber to prevent duplicate entries but to also allow for duplicate autonumbers between two laptops. I "think" I've learned enough on this site to set that up. But, it still leaves me with the question of how will data be updated/synchronized on already existing data.

    And Hans! As always thank you! I didn't realize that the linked table error I get only happens if a form is loading with linked tables. Sure enough, the form that loads in many of my databases is oftentimes bound to a linked table - thus the error. I just thought the database automatically looked to update the links when it opens and if it can't find the source the error comes up! I'll now rethink/rework how I set up my form that loads with the database!

    Thanks to all. I'm looking forward to the next area of learning!

    Leesha

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

    Re: ? Best Approach for updating Data (2002)

    There is a briefcase synchronization process that will let you synchronize files between a laptop and a single workstation, but it only works that way, and not with multiple systems, i.e. not with multiple laptops and a server. Unfortunately, as long as you want to be able to change data on the workstation and then update it on the server, replication is probably the best choice. As to the autonumber issue, one solution is to change the type to random, so that the probability of duplication gets very small. The other approach is to use what is called a GUID, (which is how replication works) though they are sort of cumbersome to work with. I would recommend the random approach unless you expect to have millions of records in the future.
    Wendell

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: ? Best Approach for updating Data (2002)

    I was not suggesting you use autonumbers. Rather I was saying that if your tables do use them, there might be problems when you merge the data back together.

    "I've never heard of a foreign key " If you have two related tables, say an orders table and an orderitems table. Each order will have an order number as its key field. Each record in the order items table will include the order number of the order it is part of. This is called a foreign key., because it is the key field of another table. I am sure you have used them, even if you don't call them that.
    Regards
    John



Posting Permissions

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