When is MS Access not the right choice? 1

[susanhawk]

I'm a Visual Basic/Access developer in a very large metropolitan hospital. Aside from the obvious security/HIPPA legal issues, what are the criteria by which MS Access databases should be changed over to SQL Server or Oracle, OR should not be the database of choice at development time?
Areas of concern include but not limited to: Disk size, record processing volume, number of users, development time, etc.
Any suggestions or known guidlines would be appreciated.

 

[FancyPrairie]

A lot of people think of Access as a database. But it's really a front-end to databases (whether it be SQL, Oracle, etc.). And it's a great front-end. It has a lot of features built into it (i.e. filtering, form view vs datasheet view, etc.) These features make it easier for users to view and change their data quickly.

Converting to SQL or Oracle is not difficult. The easiest way to do it is to just link to the tables in the SQL/Oracle databases. You could also connect to the tables programmatically. You could also create projects to access these databases

 

[susanhawk]

I agree that in the business world MS Access is insufficient and not secure enough to serve as a database. However, wherever information specific to patients is not being stored, Access is commonly used here for the purpose of speedy development, and lower costs than dedicated servers. What I'm looking for are some guidlines for when Access is not a reasonable choice based on size, # of users etc. as stated in my original post.
What do you think?

 

[KenReay]

Hi

To answer your question in the context of "purpose of speedy development, and lower costs than dedicated servers"

I would say the limiting factors are:

WAN - Since Jet is not Client server performance over slow links is unacceptable

Number of users - this is more difficult, but for viewing only, say 50 max, for Updating say 10 max - I am sure you will get people saying they have 100+ users etc, I have seen this myself, but I think the naumber I give above are 'safe' Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cruford]

I am running a Access 2000 DB with about 35 users using the mdw built in security. They have a front in on thier local PC that has linked tables to a back end on the server. It averages about 70 MB (roughly 50000 records), I import customer data from a SQL server on a daily basis adding about 300 records a day not including what the reps add. This has been running fine for the last three months. Hope that gives you an idea.

 

[grnzbra]

I used to work on an Access 97 database which, every morning, downloaded five large files from a stock clearing house. By November of each year, one of the files approached the 1 gig database size limit. The others would have driven the database over that limit. The solution was used was to have each table in its own database. The two problems were the slowness of the system due to the heavy volume of data trasmitted over the network and limitations on the number of users. The user limitation might have been overcome by using unbound forms and batch updates, but I think the speed problem, ultimately, would have required a diferent back end. We were looking at trying to convert to Access 2000 and then use the upsize wizard to convert to sql server. The conversion to 2000 went OK, but most of the queries wouldn't convert.

 

[susanhawk]

My thanks to all for responding. I had a feeling I would hear a number of diverse responses. I have been called upon to write up a criteria sheet to be followed regarding database application development using MS Access or some alternative, and at what point should existing Access databases be converted.
I will continue to check for additional voices of experience to take into consideration in this criteria sheet.

 

[MichaelRed]

Hmmmmmmmmm,

I do not think the number of users, securtity, or database size would disqualify MS A. as a reasonable choice. Some options do need to be considered, as all of the above can affect the aplication.

A large considerstion, and -at least for me- the single most important would be the selection of the database engine to invoke. Jet (the 'native' MS. A. engine) can easily become a bottleneck in the application, so if there is ANY potential for large recordsets or lots of users, this should be avoided. On the other hand, Ms. A. (ver 2K+) always includes the option of using the 'MSDE' engine - which is just an SQL server w/o some of the administrative tools, so development (and use) should not be limited in this manner. The use of an actual (full?) SQL Server at any point along this path is a simple stem, and requires NO changes to the application, although the availability of T-SQL and other tools may generate some interest in tuning the database a bit more.

With respect to Security, the Ms. A. Built in security is pretty good. It is possible to purchase tools which will 'crack' the .MDW username and password - but this does not limit the use of other security schemes, most of which are also subject to their own potential hacks, so again, for me this is not a limitation, just another choice.

Once the db engine and secutity selections have been made, the number of users and size of the database are more or less dictated by these choices - not the 'application development environment' - except in the sense that all application development is primarily set by the skill and patience of the development team.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Xunil]

I think this is subjective and academic up to a certain point.

Security is an ongoing argument I've heard done to death and it all boils down to reasonable protection against realistic threats. If you can accept that no system is 100% secure and operate on a basis of addressing your expected and likely security threats you're off to a flying start.

One thing about Access and multiple users; the more users the faster the network backbone you need to support it. This may seem like an obvious statement, but you'd be amazed how bandwidth resource hungry Access can become. On a large database with lots of users the sheer volume of information flying around can slow down and gridlock a network, especially where the database is not just being queried to pull information out, but is being written to and updated via record input userforms and similar.

I'd agree that 50 viewers is about the practical max. The number of updaters would largely depend on the hardware the database is running on and what the users are manipulating, but you could slow down to around 10 or so on a big system and possibly a little more than double that on a smaller system.

Its all relative and therefore quite subjective.

 

[DonQuichote]

A lot depends on how you design the front end. Linked tables and bound forms and controls create much network traffic and can create locking problems.

If you program neatly, you can unload the backend for quite some amount and get more users safely attached to it.

However, every type of backend needs its own programming attention. All backends are compatible if you only use standard SQL, but that is always the slowest way. Every database has its own ways of high-performance programming style (parameterqueries, stored procedures, table-type recordsets with indices, etc.)

If you are familiar with object oriented programming, it is always good to keep your data access limited to a few (collection) classes and have the rest of your application use them for data transportation. This way, only the data transportation classes need to be modified when you change the backend type.

Best regards

 

[susanhawk]

So many useful suggestions and points to consider! The vast majority of the apps. where I work are in '97, so I've not used the MSDE engine. Thanks for that information. I have not done any OOP - something I should be learning, and would also be interested in handling recordsets through transaction server.
Thanks again to all of you.