Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2003
    North Vancouver, Br. Columbia, Canada
    Thanked 0 Times in 0 Posts

    Connect multiple access databases (access2000)

    I cant think of how to do this. Let me explain what im trying to do.
    I will have an access database on a web server. This stores a bunch of things, customers information and couple other things.
    Second, there will be access databases that store information on computers in a store. There is multiple stores.
    I want to be able to search from the webserver to check information on the stores.
    So lets say, each store has a list of products. I want to be able to search the list of products from the webserver and show them which store has that product. or show the user the product list from that store.
    Now the problem im running into is that how do connect all these databases together and how do i distiquished between each store?
    the webserver will have a storeID and a login and password for each store, but the stores dont get that store ID.
    I am building the backend of this, IE tables. Just stuck on connecting them. The webserver will have a php frontend and the store servers will have a VB front end.
    any help would be great, and/or questions.

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Brisbane, Queensland, Australia
    Thanked 1 Time in 1 Post

    Re: Connect multiple access databases (access2000)

    From my experience the most effective approach is to have the stores send their records to the the main server. The basis logic is - the stores have a table with their products and Stock levels. This table has an indicator that is set when the Stock level changes or a product is edited or added. Any changed products are then sent to the server where they update the table on the server. You can do this by periodically running a function that creates a mew mdb and populates it with a table that contains the changed records. This MDB can then be attached to an email that is sent to the server or it can be FTPed. The server has a process that looks for these incoming emails or files which it uses to update its records. These incoming records must identify both the store and the product. It is good if you can coordinate the product IDs so that all stores use the same ID for the same products.
    There is a bit of work in this but I have found this the only effective way of achieving your required outcome.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Connect multiple access databases (access2000)

    Another approach would be to use the Access replication feature. It will let you keep a master database and partial replicas at each store. The real challenge however is the connectivity of each store to the master database - is it a WAN arrangement, or are you working with dial-up connections? If the latter, you would probably want to look at a tool called Replication Manager that handles all of the synchronizing tasks. In any event, what you are looking at is a complex problem - the biggest challenge is to deal with link failures and editing conflicts. If you want to learn a bit more about replication, we have a short tutorial on the subject on our website.

Posting Permissions

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