Question Web Usage and Access 2003

[opostal]

I am looking for advice regarding using several access databases from a web page. The history is that I have put together about a half dozen databases that make our life very nice here at our small office. Additionally, I built a small web page for intranet usage locally as well. As it turns out, we have a couple of other small offices that would greatly benefit from using our databases. The problem I have is that the databases are created for sorting a variety of parameters and turning up a record. This record has a couple of fields that indicate where the file is located. The files are large autocad type files. The database that has all the links itself is a little over 10 mb in size at the moment. Additionally, it runs a couple of queries (delete temp table data, copy paste records, trim function etc.) upon startup to make the search quick and convenient.

Long story short the other offices are tied to this office but the bandwidth between them is pretty horrible. That will be very unlikely to change not to mention long in coming should the change be approved. Locally, all is well. Once you leave the intranet and geographic area of my office, opening the database is terrible as you have to transmit the 10mb or so to your computer just to run the database.

What could I do to convert the database to be used from afar so that it would not take an eternity to open or search. Currently, the database has over 20000 records and grows a couple each day or two. I have been looking at data access pages but so far nothing has stuck out as an obvious answer.

Thanks in advance.

 

[Simon_MT]

As you will have issues with a hardware solution, a Terminal Server is out of the question. If you have large autocad files you will still have speed issues on the these files. Access 2003 on the web is not a major issue although I would use a scripting language like ASP or .Net.

20,000 records will not be an issue although I would always qualify the information - search criteria rather that showing all records.

Simon

 

[pkstormy]

If you're trying to maximize the potential of the mdb with as little band-width as possible, I might suggest the following:

1. Create a split front-end/back-end. Put the back-end on SQL Server, link in the SQL Server tables to the front-end, and put the front-end in MSAccess on the each user's desktop (although this isn't ideal for maintaining coding changes since you'd need to 'push' that to each user's frontend.) - With the backend on SQL Server though, you have a direct connection from the user's desktop to the Server itself where SQL Server resides (thus, avoiding any 'networking' type bottlenecks).

2. Design your forms for the front-end in an unbound fashion. That is, write functions to get the data from the table (ie. Function retDataToForm(CustomerID)) and write that data to the form or write it from the form to the table (via a save button which might call a function such as: Function writeDataToTable(CustomerID).) - this will allow your users to work in what seems like a lightening fast application since they are only making a connection to the tables at a 1 time deal versus a constant connection as you would with a bound form. Remember though that any forms (or subforms) which are 'bound' to a recordsource, will take time write due to the constant connection and load slower, perform slower, and cause more problems with hiccups. It takes a bit longer to write the code for unbound froms but the speed trade-off will be tremendous. I was returning 5+ million records with this type of setup and it worked very well where user's with slow/unsteady connections could work very quickly on the data.

3. If you go the web-route (ASP), you won't have the same kind of flexibility with the form designs since there are some limitations with what you can do.

4. On you unbound form design, also limit any comboboxes, listboxes, and keep the sourceobject for subforms blank until needed (ie. then you popuplate the sourceobject.) Limit subforms since these often-times need to be bound. Instead make unbound popup type forms. Keep in mind that the rowsource for a combobox/listbox is 'bound' to a table (although you could write code to populate a string variable and then use that to populate the rowsource.) Some rarely popup type forms, you may need to make bound since it may not pay to take the time writing these as unbound if they are not open often.

Basically, the least amount of connection time you can limit on the form to the data, the more successful the application will be, especially when you can keep any data forms and any fields from being bound to a table in some way. Using SQL Server as a backend and having the frontend on the user's desktop will eliminate the need to connect to any network drive.