Mike said:
Hello
OS: Windows XP
App: Access 2003 SP3
Can anyone point me to a document that lists the advantages of having
a SQL back-end to an Access database. I am particulary looking for
significant performance increases.
The primary advantages of a server back end are security, scalability,
higher numbers of concurrent users, less prone to corruption, and ability to
be available 24/7. Performance gains are not among the list. In fact you
could see some areas perform slower than with a Jet back end.
There certainly is anecdotal evidence of performance increases, but often
that is due to the fact that the server that a new SQL Server instance is
set up on is simply a faster, more powerful PC. Also many apps go through
some refactoring (design improvements) at the same time the back end is
changed to a server and those changes can result in dramatic performance
increases.
Performance (regardless of back end) is 90% about your design. Proper
indexing, proper table structures, proper query structure, and most
important a design that simply retrieves as little data as possible for any
given task.
