How can I enable my Access Database to be updated by a remote user?

Question

I am an experienced computer programmer, but have done very little web-related development, so I would appreciate your ideas. Our family runs a tiny, seasonal retail business, managing the orders using an Access Database. We would love to allow our customers to place orders online. The catch is that we package the goods for shipping out of a warehouse that has no internet access, and no possibility of internet access. I would like to design a solution that would 1 – Allow the customers to input orders online which would automatically be entered into our existing Access Database. 2 – Allow us to use the database without an internet connection. One thing that might make a difference is that the ordering and shipping happen at separate times. First, everyone places all orders. When all orders have been received, we ship them all out (ordering is closed out at this point). Therefore, I would be open to an idea that would include making a copy of the database to a local machine just before shipping.

I could use some pointers to start me off in the right direction. Is there a particular language that is ideal for this kind of web development? How do I work around allowing the database to be accessible online but not dependent on an online connection? Thanks in advance!

Answer 1

1. There is no way you can update the database from the web if you have no Internet connection without manually coping the data.
2. Do you have email/text messaging access from a phone? If yes how long does the order description need to be? if you can get it to be under 140 characters you could allow customers to order via a website and send yourself a text message or email (Email could be longer). Then you would have to manually process the text message/email and enter it into the database.

Example: (Webpage form)

Product Id: _____
Amount:     _____
Cost:       _____
etc... 

When the user clicks submit you can use the email/sms gateway and send yourself a text message

something like this:

pid:1234,a:12,c:$300,etc...

each field would have an id separated by : and the value following. then comma separated for each field. Then just mapped the fields and values to your access database and manually enter the orders in.

I know it's not the best solution but with no Internet access your limiting yourself

EDIT:

There is another option but you would need to purchase a GSM modem to accept the SMS/text message, parse it out and enter the order in the access database. What is a GSM Modem

EDIT #2:

Well if you're going to use a hosting company, I would suggest PHP w/ MySQL as the database to keep track of online orders. right before you do the ordering stuff you can run another script to process the pending orders and make a insert statement script/sql for the Access Database which you could carry on a thumb drive or something. After you have processed the pending you would use the upload script/sql (on the thumb drive) to update the Access Database with the daily orders. This could even be done in the morning or something. This would eliminate the need for the manual entry but still require you to run the import of data.

You would need to implement a webpage in PHP w/ a MySQL database for the backend of the website. Create a processing script that will export (in Access Database insert SQL) and mark those records processed or pending processed (If pending-processed you could also add a validation layer to come back and update the records as processed after you have uploaded/inserted them into the Access Database). Export the insert/upload Access Database SQL to a thumb drive and finally upload/import the Access Database for the new orders.

This is a still very cumbersome as you do not have Internet access to the Access Database. I would still suggest even getting a dial-up connection or DSL if offered (as it uses the phone line to connect to the Internet) or a GSM Modem which would use a cell signal to connect to the internet (Think tethering).

There are many tutorials on PHP/MySQL so no worries there, good luck!

Answer 2

Well, either you use some existing web software and ordering system, or you build your own. There are zillion desktop development systems from VB to FoxPro to Access, and the same holds true for web development, you have to go out and pick a language and platform for development. However, just like you can purchase QuickBooks for about $99, it would make no sense to build your own accounting system at a cost of 1 million or more that you can get for $99. The same goes for a web based ordering system, you going to purchase or use an existing shopping Cart. In fact, recommend j-street and a product called CARTGENIE since the desktop portion is built in access, but the back end part is web based.

Since you are talking about a web based solution, then your current software is of really no use here. You could certainly use Access to pull data from the web site (not at your work location that as you mention has no internet).

Any web application will use a database server for the data store. In fact a big portion of web hosting providers allow you to connect to the web site, and you could do so with access. However, in your case, you don't have internet, so it don't matter.

To send this order information to the location without internet. Just download the order information from the web site into an Excel Sheet, or in fact run the report from the web site and print it out, and then Fax it to the location that does not have any internet. I suppose you could also consider placing the data on a jump drive.

I mean since you don't have any internet connection at this work location, it don't matter much if you snail mail a floppy disk or jump drive as Excel or a table in access. For a few orders a day, the labor savings of importing vs. that of a weekly fax sent and data entry done on site will not likely even save the cost of building an export + import system into your software. I guess this really depends on the volume of orders here, but if you do the data entry over time (weekly), then you not have a huge task come the seasonal work time when orders have to be sent out.

At the end of the day, your ordering software, you're pricing, you're adding of products and inventory etc. will NOT occur on the software that runs without internet at the work location.

So, the important business rules of updating pricing, processing of orders now has to occur on the web site, and will not occur in the Access application anymore. If you had internet then you could most certainly have Access connect to the database server on the web site to pull down orders. In your case, just printing out the orders and faxing them to the location that has no internet is likely the most practical solution.

You really can't use your existing ordering system since it can't control inventory, and can't control pricing and can't know what has been purchased like the web site can. So, the web system is where the business rules and data must exist. Everything else becomes just a consume of that web data. So, a printout + fax, or an Excel sheet on a jump drive delivered to that location is what you are looking at.

I suppose you could build some type of import system into your existing Access application that could import the Excel or csv data that the web shopping cart produces. So, you need to find a shopping cart system. Most if no all have some type of ability to export order data to Excel or downlaod sometype of csv file.

If you have a huge price + product list then you could/would look for a shopping cart system that accepts some type of price and product list that can be uploaded. However, if it not a huge product list, them a onetime simple seasonal data entry session again makes so much more sense then spending large amounts of time to look for uploading of price lists when they are not that large and only done 1 or 2 times a year.

It is unlikely you are going to build your own web based shopping cart anymore then it make sense to build an Accounting package when one like QuickBooks can be had for about $99. Check out a few shopping cart systems, and find one that can download the orders file, you then just print that out, or as mentioned consider building some importing routines into your current system.

Answer 3

Have a copy of Ms Access database at client side and create entries into dB. And run a separate internet based application to push all entries into your MS Access database. It will not be complete on-line process but will be a semi-on-line process. But catch is you have to maintain Masters tables in all client-side databases and update them regularly