Connect multiple access databases (access2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote