Access to SQL Server Scalability 2

[paulocc]

Hi,

I am pretty new to VB/Access/SQL Server but have used other development frameworks before.

Basically, I am a bit confused as to the capabilities of Access and SQL Server. I know that both are tightly coupled with VB. I have been told by some that Access is sufficient for single-user applications only whereas SQL Server is for multi-user. Others have commented that Access can be used for multi-user applications.

I need to prototype and application and want to use VB and Access but am concerned as to the scalability of such an approach. Does a VB app with Access DB convert easily to SQL Server?

Any help would be really appreciated.

Regards,
Paul.

 

[thornmastr]

Paul,

For your reference, Access using its own database engine called JET is more than capable of handling a multi-user environment. You do need to be aware of some constraints if you decide to go with ACCESS/JET. The maximum number of users that can be attached to any one database at a time is 255.

JET is tightly bound to your development skill. The more you know, the greqter your understanding of relational database structure, the better your performance will be.

You must normalize your tables to the 3N level. You do not have to go beyond that, but 1N and 2N are not enough.

You need a very good understanding of Structured Query Language.

You need to program as if you were using SQL Server. Your goal is the minimum transfer of information from your database to your user, and from your user to your database. If you receive a ten thousand row recordset when four is all you need, then your users suffer the time lag.

If you are in a networked environment, be aware that ACCESS will roll over and play dead if confronted with a dead or dying NIC. Make sure your users have top of the line, i.e, 3-Com, NIC’s.

Now you have all the ground rules. If you are developing for a large group of users doing heavy updating, you would be far better off using an ACCESSS front end tied to a SQL SERVER backend. Remember, if your user does not want to pay for SQL SERVER, you can use MSDE. You can also go with some of the free engines such as MYSQL and OCELOT.

Unless you have lots of time and your client has lots of money, you are better off using ACCESS as your front end rather than VB. The time/cost ration between ACCESS to VB6 is 1 to 3. you will spend 3 hours doing in VB what it would take you 1 hour to do in ACCESS. Do not construe my remarks to show an overwhelming preference for ACCESS over VB. I use both. However, I have learned that in most cases most users really do not need the power or the complexity of VB. My preference is to use VB for processes that are run during off hours and or that require no user interactions.

However, you can define your back end database using ACCESS and link to it using VB.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[paulocc]

Robert,

Firstly, thanks for your expert and detailed help. Excellent.

The limit on the number of users is not a problem as I'll be nowhere near 255 users.

I am a bit worried about the issue with dying network cards as the environment I will be deploying to includes some rather old machines. Does Access act likewise with network connectivity problems? When you say die, what do you mean? Does it kill all users?

As regards MSDE, is the database schema compatible between Access and MSDE, likewise MSDE and SQL Server?

One of my concerns is with how access handles record locking and transcations. This info I'm finding hard to come by.

The reason I'm opting for VB is because there is some complicated data representation screens to be developed which I'm not too sure if access will handle. Basically, a client owns a theatre and wants a booking application allowing block booking, repeat bookungs etc. One of the criteria is for a graphical representation of the theatre with seats colour-coded to represent availability etc. I don't think that access would handle this.

Again, Thanks for your expert help and guidance. I've spent hours looking for info on the web and your single email message taught me more in one read.

Regards,
Paul O' Connor

 

[thornmastr]

Paul,

The real annoyance of a bad NIC is database corruption. If the Nic fails during an update query you risk massive corruption of the backend (SQL SERVER will protect itself against this, JET will not.) if, while a recordset is being returned to you and the NIC dies, the user will sit forever. He can’t gracefully exit because he’s waiting for the signal from the NIC, so it just hangs until the user reboots which is going to corrupt his front end almost always. Old machines are computers; they work. Old NICS are bad. Plan on replacing any old or broken NICS. If you have any doubt, replace the damned thing
Access corruption can be a nightmare. Quite often it takes a while to realize you are corrupted. Sometimes the repair functionality of Access is not enough to recover. There are a number of procedures which will get you through corruption, but periodic back ups of your bak end are erssential.

MSDE is SQL SERVER 2000 throttled back to 5 concurrent users which is no big deal. How often does SQL SERVER deal with more than 5 concurrent requests? Not too often, so that should not even affect you.

Since MSDE comes without any type of Enterprise manager, I use the ADP functionality of A2K and above to connect to the MSDE database and use it to manage the database. That is the only thing I use ACCESS Projects for. I like DAO so obviously I use ODBC.

Record locking can be specified at the row level. It’s default is page locking. One of the nice features of Access is it will handle most locking problems for you. At times, it can’t, and then it has its own series of canned errors and alternative actions for the user to take. In almost all instances, you can trap for specific errors.

Like with ORACLE you cannot spawn transaction processing from within a form. You can, when executing a SQL update, specify the “failonerror” construct which is equivalent to a roll back on failure. But if you are attempting a blocked transaction update issued from within a form; form processing takes precedence over transaction processing.

I will certainly grant you building an ocx is a VB benefit. You really can’t build an ocx from within Access, but you can certainly reference an ocx from Access and then plop the control into an Access form. The choice of course is yours rto make.

I think I got all your questions. If not, post back.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[paulocc]

Again, thanks for the expert help.
I have one last query which I should have included in my original post.

Am I right in saying that VB has native access to an Access DB but you need ODBC when connecting with SQL Server. I assume that ODBC is also required for connections to MSDE.

 

[thornmastr]

Paul,

An access database backend ( the tables) is easily accessed (pardon the pun) by VB. The usual most accepted connection is via ADO using the connect string; nothing to it.

I am reasonably certain you can use ADO to connect to a SQL SERVER database via a DSNless connection. However, I must qualify this by saying……

I have just begun using ADO and only in its most simple form. I am used to and most comfortable with DAO, so, therefore, I know that I am going to connect to SQL SERVER, MDAC,ORACLE, MYSQL, OCELOT using ODBC.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Quehay]

Robert thanks for sharing all your insights! Paul: If you want to use the Access container (query tool, reports, forms in its normal fashion) you need to connect through ODBC rather than ADO. You can do bound forms with an ADO recordcontrol (I believe--I've only done unbound forms with ADO), but Access needs to work through Jet to run the query GUI and reports (ODBC connections run through Jet). Unless you've got more than 5 concurrent users who are updating the same rows you should be fine with Access (and won't have to deal with Client/Server issues).

Do back up your backend at least several times a day. With a split FE/BE setup it's just a simple file copy that can be scripted from within Access. Any network hiccup or user crash can corrupt your mdb beyond repair. The reasons that people malign Access boil down to:

1. Risk of corruption and loss of data
2. Clumsy handling of competing updates
3. Network drain because of file-sharing mode of Access Jet

These can be overcome/covered in most cases where < 5 concurrent users. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[thornmastr]

Jeff,

Thanks for the insight into ADO. As I said, I am DAO/ODBC oriented.

There are a few things specific to your comments about Access I disagree with.

Other than flaky NICS, network problems using JET are caused by programmer stupidity and lack of knowledge and quite often an extreme misplaced faith in network reliability.

I have worked on but did not write an Access application using only Jet across a network supporting 100 concurrent users. It suffers from neither slowness nor network failures.

I have written using Access/Jet only a database supporting more than 50 concurrent users without any serious problems.

Access front end with SQL Server backend is a combination that can be hardly rivaled in any situation other than data warehousing/mining.

Since I obviously cannot prove my assertions, take them as only my $0.02 worth.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com