Sometimes you don't get better performance from SQL Server. It is not
the panacea for performance. A badly designed Access database will be
a badly designed SQL one and may even run slower if its really bad.
---------- About comparing Access to SQL Server ------------
SQL Server has great security, great backups and management of large
data files but may not be the cure-all people expect of it Here are
some of the considerations courtesy of "Access Developers Guide to SQL
Server", SAMS Publishing, ISBN 0-672-319944-6
- Users: Access will work well with 15-20 *concurrent* users (meaning
you might have 50 workstations but only 20 users are generally logged
in at any one time)
- Database SIze: Access is limited to 2Gb from A2000, 1Gb for
versions before that. This is a *huge* amount of data, so not many
upsizing decisions are made because of size.
- Performance: There is an assumption that, no matter how slow the
Access database is, upsizing will magically speed things up. Often
just the opposite is true, and upsizing results in even slower
performance. Performance has as much to do with database design and
application design as it does with the platform.
- Transaction Logging: Because the Jet databse engine runsd on each
user's machine, it cannot keep a comprehensive record of all the data
changes made by multiple users. SQL Server on the other hand,
processes all the data changes centrally, so it maintains a complete
up-to-date log of everything that has happened in the Transaction Log.
- Data Integrity Users can corrupt an access database by turning
their system off while in the middle of a critical operation or by
simply opening the database in MS Word, which irretrievably corrupts a
database. On the other hand it is very difficult to corrupt a SQL
Server database.
- Security If security is a big issue its an important reason to
upsize to SQL Server. With it you can take advantage of the more
robust and even more convenient security through its strong
integration with Windows security.
- Administration The SQL Server Enterprise Manager makes it possible
to centrally administer local and remote servers and you can add
linked servers for distributed querying. Although Jet database is
easier to administer, that is because it severely limits your
administrative options.
- Features: There are some Access functions that SQL Server cannot
support and are ignored in upsizing
- Access defined field properties such as input masks, formats,
captions and descriptions are ignored.
- If a function is used in a field validation rule that has no SQL
Server equivalent, the validation is ignored
- Nested queries are not supported in SQL Server
- If a function is used in a field's Default Property, it will not
be upsized.
- The Allow Zero Length property is ignored.
- All Access security is ignored.
- Default Value expressions have to be stripped before upsiing or
the whole field is ignored.
- Lookup fields results in tables are ignored. Only the underlying
ID number is retained.
- If a field in a table has a Yes (No Duplicates) index AND the
Required Property is set to True AND the field contains more than one
Null value, the data from the table cannot be upsized, only the
structure.
---------------- compiled by Brett Collings -----------------
I have an Access 2000 program that is used by about 10
people, out of which maybe 5 use it concurrently. The
Front End is installed on each workstation, linked to a
BE on a server.
The issue is that it runs slower than molasses in this
environment. Whenever I need to do design work on the
Front End, I use a test BE on my local drive and it's
much perkier. SO I asked our Tech Services guy if maybe
he could move the BE to a faster server, or look at the
network conection.
His response was that there is plenty of processing power
and disk storage where the BE sits right now, and that
the problem is Access itself. He says its clunky, it uses
too much computing power to compile or something. He
advised me to convert the back end to SQL (I think he
said), and use Access for the front end. He thought that
would be great.
I'm not convinced, but does anyone understand what the
heck he's talking about? Can I convert or upload the BE
to a more robust format via Access, then use Access for
the front end? If so, should I? Any thoughts on this
will be appreciated!
Cheers,
Brett
