When to use Access ???

M

mscertified

Our development team is evaluating the future use of Access for development
purposes. We have a number of applications written in Access 2.0, Access 97
and Access 2000. Many were written by users or outside agencies but we have
to maintain them. Some members of our team are voiciferously against ANY use
of Access and have downloaded off of Microsoft web pages statements that
Microsoft warns against using it for any production systems. Are there any
official recommendations from Microsoft just what Access is suitable for and
what not?
 
P

PC Datasheet

The first main limitation for Access is that Access can be used for upto 25
to 30 concurrent users comfortably. A well designed database could go
further. MS specifications claims it can be used for up to 256 concurrent
users. The second limitation is that it does not make a good web database.

BTW, I am in business to provide customers with a resource for help with
Access, Excel and Word applications. If you are ever seeking outside help,
please contact me at my email address below.
 
R

Rick Brandt

mscertified said:
Our development team is evaluating the future use of Access for development
purposes. We have a number of applications written in Access 2.0, Access 97
and Access 2000. Many were written by users or outside agencies but we have
to maintain them. Some members of our team are voiciferously against ANY use
of Access and have downloaded off of Microsoft web pages statements that
Microsoft warns against using it for any production systems. Are there any
official recommendations from Microsoft just what Access is suitable for and
what not?
Click to expand...

The first thing you have to realize is that when MS (or anyone else) says
that they are talking about *storing the data in Access/Jet files*. You
can still develop in Access as the front end to a more robust server
database (like SQL Server) and all such concerns are removed.

Even considering the above, also consider the source. MS stands to make
more money if everyone who is using Access/Jet buys SQL Server so their
recommendations are a bit exaggerated in this area.
 
M

mscertified

Are there not serious performance limiltations in using Access as a front-end
to SQL Server as opposed to say using a .NET web or windows application going
against SQL Server. From what I have read .NET contains many new
optimizations for reading and writing data. .NET can also interact with many
different database servers e.g. Oracle whereas Access is restricted to SQL
Server?
 
B

Brian

mscertified said:
Some members of our team are voiciferously against ANY use
of Access
Click to expand...

Presumably the ones with other skills.
and have downloaded off of Microsoft web pages statements that
Microsoft warns against using it for any production systems.
Click to expand...

I've never seen such a statement, but, even supposing such statements are to
be found, there are millions of organisations throughout the world happily
using Access for production systems.
Are there any
official recommendations from Microsoft just what Access is suitable for and
what not?
Click to expand...

Who cares? The thing to do is to make decisions based on a proper
understanding of your organisation's requirements and the relative strengths
and weaknesses of the available technologies. The thing NOT to do is to
make decisions based on the opinions of those with an axe to grind, whether
in your own organisation or at Microsoft.
 
B

Brian

mscertified said:
Are there not serious performance limiltations in using Access as a front-end
to SQL Server as opposed to say using a .NET web or windows application going
against SQL Server.
No

From what I have read .NET contains many new
optimizations for reading and writing data.
Click to expand...

Anything that reads data from and writes it to a SQL Server database does so
using Transact-SQL. SQL Server processes T-SQL exactly the same regardless
of what kind of client submitted it - SQL Server neither knows nor cares.
.NET can also interact with many
different database servers e.g. Oracle whereas Access is restricted to SQL
Server?
Click to expand...

Totally untrue, if there's an ODBC driver for it Access can use it.

As someone who has bounced around between various versions of VB from
version 3 onwards, and Access from version 2 onwards, and has extensively
evaluated .NET as a possible alternative to Access, I can tell you that it
SUCKS. It might be nice when it's finished, in a version or two, but even
then the cost of development for heavily database-oriented applications will
still be several times that of Access (just as it always has been for VB).

You might like to get yourself a good understanding of continuous forms and
linked subforms in Access, and then ask your .NET fans to show you how they
would do the same thing. You might also ask them to show you how to change
the border style of a combo box on a .NET Windows Form.
 
M

mscertified

Hi,
I'm very familiar with Access having worked with it for several years. I
know all about continuous forms and subforms and the like. As far as I am
aware in Access 2000 only SQL server can be accessed as a native database
engine. ODBC interfacing is not a suitable substitiute. We need to be able to
execute stored porocedures.
 
J

John Vinson

As far as I am
aware in Access 2000 only SQL server can be accessed as a native database
engine. ODBC interfacing is not a suitable substitiute. We need to be able to
execute stored porocedures.
Click to expand...

That is emphatically NOT the case. ODBC provides the capability of
"Pass-through queries"; you can construct any valid T-SQL string and
have SQL execute it. This would include not just running stored
procedures, but creating them.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
R

Rick Brandt

mscertified said:
Hi,
I'm very familiar with Access having worked with it for several years. I
know all about continuous forms and subforms and the like. As far as I am
aware in Access 2000 only SQL server can be accessed as a native database
engine. ODBC interfacing is not a suitable substitiute. We need to be able to
execute stored porocedures.
Click to expand...

Well that will come as some surprise to my 70-plus users of Access 2000 who
use it as a client to our IBM I-Series database.

You are likely thinking of an ADP "Access Data Project" which CAN only talk
to SQL Server. As long as you use an MDB you can use any back end that you
can find an ODBC or OLE-DB driver for. And with Pass-Through queries
stored procedures are no problem at all. Use them extensively.
 
V

Van T. Dinh

Well said, Brian.

Since Microsoft sells Access, I can't see how Microsoft warns against using
Access for *any* production systems. There may be cases that Access (or JET
database engine) may not be suitable but there are numerous situations where
Access / JET is the most suitable database application to use. There are
probbly more Access / JET databases than any other databases (check the
number of databases in your org.) and Access didn't become so popular for
nothing.
 
B

Brian

Great, so ask your .NET fans to do something similar. They will cobble
something together, but it will be nowhere near as good and it will take
them MUCH longer.

Executing SP's is no problem with ODBC, I do it all the time.
 
M

MacDermott

Wow! There are already some pretty big names in this thread, and I wouldn't
want anything I say here to be interpreted as questioning anything they've
said.
I do think, though, that there ARE a few of Access's limitations which
deserve mention.

1. Access files are limited to 2 GB in size. While it's possible to work
around this limitation to some extent by storing tables in separate files,
you can't enforce referential integrity across files, and my guess is that
performance could suffer a bit.
If your data tables run to the terabytes, Access (Jet storage) may not
be a good choice. As other posters have mentioned, Access may still be a
good front end.

2. Access files do require regular compacting. This doesn't have to be a
big deal; you could probably even schedule it to run automatically in the
middle of the night. But it does mean that you can't expect your data to be
available 24-7, with absolutely no down time. (This is one of the things
that makes Access databases less than optimal for Web data storage.)

3. Although Access is generally reliable, its record pales in comparison
with client-server databases like SQL Server or Oracle. If you're going to
use an Access database for production, be sure you schedule regular
backups - nightly is a good practice. Remember, too, that Access lacks the
ability to recover from transactions which is built into the larger, more
robust databases.
Don't let this scare you too much - corruption in Access databases,
especially well-designed ones, is still quite rare. Think about corruption
in terms of "is this something we could ever afford to have happen - even
once?"
So if your application is "mission critical", and being able to recover
from last night's backup isn't good enough, you might want to reconsider a
decision to use Access.

I think it should be clear that there are a whole lot of production
applications where none of the three concerns above is a problem. With all
of that said, I still find Access an extremely cost-effective,
user-friendly, rapid-development option. Like the bigger names who have
posted, I have many satisfied customers using it on a daily basis.

HTH
- Turtle
 
Top