Access "Not... to be used in a networked environment"

[solotoo]

I have just been told that we shouldn't be using Access to build databases for sharing information. Instead it should only be used to build single user databases.
Please, give me some ammunition to show what a stupid statement this is!!

 

[daherb]

umm lets see...... I run 3 multiuser network databases at work. In addition i have in my time created 6 websites running on a access backend through ODBC links. Access was initially designed with the idea of using it as an office level database system.

Who told you that access should not be used in a network environment. OK granted SQL is much more powerful as too are a number of other database system but each of those are designed to deal with very large databases.

 

[boblarson]

We use many Access databases/programs that are used in a "network" environment. However, just a reminder that there are some greater risks of corruption if using on a Wide Area Network (WAN) that aren't as prevalent when using it on a Local Area Network (LAN).

I get around the WAN piece by using replication to the other sites and each user has a frontend on their own computer.

 

[Uncle Gizmo]

If you are using a wireless network you can run into problems, otherwise it should work fine.

 

[boblarson]

The problem with running on a WAN (wide area network) is that the connection is prone to short, almost imperceptible, disconnections. If you are running an Access database at one location, but the actual MDB that you are running is at a different site, corruption will occur if the connection gets lost, even so briefly that you don't can't see it happen.

So, for WAN situations, I would highly recommend using replication if you are going to use Access in that situation.

 

[neileg]

It is easy to create an Access database that hauls a great deal of data across the network. Network people don't like this unnecessary traffic, so they have legitimate concern about the use of Access. A well designed application will minimise this problem, but it won't go completely away.

So it's not such a stupid statement after all.

 

[Uncle Gizmo]

>>> hauls a great deal of data across the network. Network people don't like this unnecessary traffic<<<

That is a very good point! I have never really considered the network chappy's point of view before! I have notice that they have been getting very picky about bandwidth, they have stopped me using Skype at work saying that it affected the bandwidth, I nearly told them what I thought. But I did manage to bite my tongue yet again!

 

[Pat Hartman]

If the amount of data transfer that they are worried about, you can replace your Jet back end with an SQL Server (or Oracle or DB2 or whatever your shop uses) back end. You will need to do some work to the front end to take advantage of the new be such as changing all forms to be based on a query that includes criteria that selects the minimum number of rows and columns necessary.

 

[solotoo]

At the moment we have an Excel front end feeding in to an Access back end via SQL (as we can't afford all the Access licences)

 

[neileg]

At the moment we have an Excel front end feeding in to an Access back end via SQL (as we can't afford all the Access licences)

Good luck! You need it.

 

[ghudson]

At the moment we have an Excel front end feeding in to an Access back end via SQL (as we can't afford all the Access licences)

Then buy the Developers Extensions package and freely distribute the Runtime version of Access to the users who need to use your .mdb database. Search the forum for the "Microsoft Office Access 2003 Developer Extensions" if you do not know what that is.

 

[solotoo]

Would love to, have suggested it, however we dont have any choice in the software we can use or buy. We are only a very small cog in the mighty wheel which is the civil service!!

 

[neileg]

Would love to, have suggested it, however we dont have any choice in the software we can use or buy. We are only a very small cog in the mighty wheel which is the civil service!!

I work in local government so I understand. However, we have an enterprise licence for MS Office (including Access) which means any user can have the full suite. It's worth checking to see if you have, too.

 

[reclusivemonkey]

If licensing costs are an issue, I was going to suggest you try one of the free alternatives out there (MySQL springs to mind, and should provide a backend for Access easily enough), but if you have no choice in the software you use, this may not be possible... Also, OpenOffice now includes "Base", a database program which looks similar to Access, but won't have all the frills (I've not had a good chance to look at it yet).

The reason people advise not to use Access in a networked environment is that it has a relatively small number of maximum users compared with many "heavyweight" database applications. Now I don't have any current info, this is for Access 2000, but MDSE (the embedded version of SQL Server in Access) has limitations; 2Gb database files and its fine tuned for about six simultaneous users. According to Microsoft, MDSE is suitable for small workgroups (five or less), but the actual performance is down to the machine specs. Even a low spec server (should) be fine with about 25 users. So they may have a point; it depends on how big your DB will grow and how many simultaneous users you will have.

Heresy in a Access forum, I know.

 

[Peter2222]

split it up!

just a quick response for people not happy with large hauls!

database splitter!

 

[neileg]

just a quick response for people not happy with large hauls!

database splitter!

Not a solution if your design is poor. A bad design when split is still a bad design. The use of form based filters rather than queries with fully specified parameters is an example of the problem.