Dropped WAN Links with Access Database?

[darall]

I have recently developed an Access 2002 database for our Project Management office. The database has a back end with two front end files; one for the administrator and one for the Project Managers.

The PMO asked for network space to put the database where approx 150 users from three locations can access the backend. The result was a ton of push back from our network team. Below is a summary of their concerns. Ultimately they want us to convert the database to SQL; however we don't have a resource, time or the budget to do this. The database was developed as a temporary solution for about 150 resources, however at a max 30 would be in the database at one time and the database is designed so that the PM can only edit records they are responsible for to prevent record conflicts.

The PMO asked for 4 GB of space to make sure we had room for the database and other project files they plan to store in the same location. The backend file is currently 2180KB but should grow over time. Each front end is about 10000KB.

I am trying to find out if anyone has had the types of issues they are talking about:
[UL][li]Access is a very network intensive app that pulls down the complete database for any function. [/li]
[li]Whenever anyone accesses, changes data, or anything thing of that nature it pulls the entire database down locally and then pushes it back to the server. So each person accessing could have 4GB of data between the server and their workstation occurring frequently depending on what they do. As your user base increase and the database size grows this becomes a large risk on a server used by thousands of people.
[/li][li]It might be best to convert this to SQL now - it will be easier to do now. Additionally, this not the type of workload that Access is designed for so I would really not recommend using it for this.
[/li][li]The problem is going to be with that many users the amount of potential network traffic you will generate.
[/li][li]We’ll get you disk space, but if you drop WAN links, I take no responsibility.[/li]
[/UL]

Does anyone have past experience with this that can help me lessen their fears? Has anyone had trouble with dropping WAN links? The databases I have created and used in the past have been for smaller groups, but I have not experienced the issues they are listing.

Thanks for any help,
Denae

 

[lamarw]

I don't think that the database is being downloaded by every user based on your front-end, back-end description. If that were the case then each user would have to merge their changes back into the database on the server. There would be no reason to have record locking, etc. It can be done that way but you have to take certain steps in order to do this and you did not mention them. However, it is true that Access is fairly network intensive but by splitting the database away from the front end you have minimized the amount of network traffic.

A WAN link may not really be that much bandwidth. If the link is a T1 then that represents a pretty good size link but not huge. A fractional lnk is equivalent to 56K or 64K not much faster than most dialup's. If the link were to drop in the middle of a transaction then you may have a little problem. Maybe someone with more knowledge in the area of rollback could help here.

If the ultimate goal is to convert to SQL then from a business standpoint that may be the way to go.

 

[fneily]

The sizes you gave for the backend and frontend, are they the sizes after doing a Compact and Repair? Remember, when you do any deletes, that space isn't reused so your database blows up quite quickly.
Since you're looking to have approx. 150 users, a couple of other options are:
Put up a citrix server (

http://www.citrix.com)

so they can remotely login directly to just one local database.

If the users are just doing simple data entry and data searching, create DAP's (data access page). Beware, IT departments will probably tell you to write the web pages in ASP because they have no experience with DAP'S. I can point them to the microsoft site that explains how to implement DAP's over the internet. I ran into this problem a few times. I actually had to prove to SQL db admins. that Access could work on the internet.

 

[lamarw]

Hi fneily,
I have looked at the MS web pages describing the use of DAP's in the internet environ. I never got it working. Do you have some resources that may help with this?

Lamar

 

[fneily]

Here's one place you can go to and look at some DAP articles:
msdn.microsoft.com\library - Then on the left, expand Office Solution Development, Microsoft Office XP, Access 2002, Technical Articles

Also this site, if you search on data access page, will have some articles;

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm

Just remember, to make a DAP editable, you must include all primary keys on the DAP. People always have trouble with this. Also, DAP's are NOT within Access, they are in the Internet Explorer environment - web pages. So you program them in VBScript or JScript.

Alos, search this forum, and the other Access forums, for DAP (data access page) and you'll find some questions on them.

 

[JoeAtWork]

Your network team's concerns are legitimate. I work with Access databases almost every day. In my opinion Access databases are suitable for local networks only, I would never recommend them over a WAN.

As mentioned, web access would be about the only workable way of continuing to use this database.

It sounds like your "temporary" solution has reached a point where you need to make decisions on how to scale up.