[b] HELP!! Connecting to access tables over the Internet [/b]

[TSIsolutions]

Good Afternoon,

I've been going through posts all morning trying to figure out the best course of actions.
I've developed an access application for a retail chain. I need to figure out the best way for the stores' Parent Company to view (preferable in real-time) the stores' activities (total sales, items sold ect..)
I've looked at quite a few possible options: VPN, ADP, Terminal Services.

Ultimately I would like to be able to do 2 things:

1. Generate a Summary Report that combine the sales of all the stores together.
2. I would also like to be able to transfer data to each store's db so that when inventory is physically sent to the stores, it's information (description, qty, ect..) is already in the system.

I Also don't want "The Boss" to wait an hour for the report to be generated or the data to be transfered.

I really need you help on this one,

Thanks.

 

[jsteph]

TSI,
In my opinion, Access is not going to do what you want efficiently. While it's a reasonable choice for a Point-of-Sale system where you need to keep operating even if the data-circuit is down, it isn't efficient at all for real-time querying from the parent location, much less real-time querying of ALL locations for a single summary.

I have done similar systems and they always involve SQL-Server, with Access as the front-end and realtime backend. Each record update/or insert to the POS Access table9s) is immediately followed by an ansynchronous write to sql-server, and upon that thread's completion, the acknowledgment is flagged in the local Access table(s). If the circuit goes down or the server is unavailable for any reason, the un-flagged updates and inserts are written back to the server upon reconnection. The ansynchrouns-ness of the parallel update allows the POS system to roll along without delay.

In my opinion, this is really the only way you're going to get reasonable POS performance in conjunction with reasonable headquarters querying ability, unless of course you have the money to have redundant data-circuits, in which case you could lose Access' backend completely.

The crux is to bullet-proof the update/acknowledgment logic so that you have data at the headquarters server that is--for all intents and purposes--an exact copy of what's on the local POS machine.
-Jim

 

[lamarw]

The above is the best long term solution. But in the interim you can assemble an .ASP web page that will build the report for you and allow the web user to view the contents.