Given a Microsoft Access "client application" (.mdb file with only forms,
reports, queries, and business logic - but no data).

1. What *specific benefits* are to be gained by having it connect (link) to
a SQL Server database (as opposed to connecting to another MS Access
database on a file server). I'm wondering particularly about scalability,
performance, locking, etc... but I'd like to hear about any other important
considerations as well.

2. Is this (MS access application to SQL Server db) generally a good thing
or a bad thing? Why?

3. Does that setup (MS access application to SQL Server db) still result in
a basic file server architecture?

Thanks!

I started doing this a few years ago with my database and, although there
was a learning curve, I have no regrets.  My key issues were scalability and
availability and SQL server has proven superior on both counts.  I also like
stored procedures the more I learn about them.  Plus I'm now having to tie
in Oracle database, and SQL Server is definitely the way to go there.
Finally, I am eventually moving my apps from compiled to asp.net and an
Access backend won't cut it there.

Randall Arnold

Show quoteHide quote

Answers in line:

SQL-Server is more secure and you'd want to use it to protect critical
personal information. Supposedly it is more robust, but I haven't had
problems either way. Access/Jet databases are limited in the number of users
they can support. Although the "official" number is listed as 255, the
actual number is significantly less. My own system supports 53 users
comfortably.

The only "bad" thing that I can say about SQL-Server is that if you use the
full version, it is more expensive.

Not necessarily. Access will try to pass as much to the server as it can,
and you can specifically write Pass-Through queries to server based Stored
Procedures.

Mark S wrote:

Show quoteHide quoteThere is a huge difference between the two architectures. If you put an
Access database on a file server then your client machine is still
responsible for all processing, security and transaction management.
Searching and processing requires the client machine to read data
across the network before it can be processed.

In a client-server architecture most of the processing happens on the
server. The server handles cacheing, indexing, security and transaction
management. That means only the data submitted by the client and the
results of any processing from the server have to be sent across the
network. The system can scale to as many users as your server hardware
can support. Transactional integrity and user-level security is
enforced by the server rather than by each client machine. The data is
much more available because it can be backed up, restored, indexed and
reconfigured while it is in use. Other high availability features such
as clustering and mirroring will also help keep your data online.

SQL Server is much more than a database server. Your SQL Server
applications can take advantage of other sophisticated services
provided on the server such as Reporting Services, Integration
Services, Replication, Notification Services, Service Broker and more.

What you should also appreciate is that developing an application for a
client-server or N-tier environment is quite different to developing
for a file server database. To be most effective, all the data access
code and most of the processing should be written for the server-side
rather than the client. For that purpose you should take advantage of
SQL stored procedures and/or server-side components, which require
different techniques and skills from those used for VBA-style
client-side code.

Hope this helps.

Clarification (I should read before I post :-)


SQL Server is designed for client-server architecture.

In a client-server architecture most of the processing happens on the
server. The server handles cacheing, ... etc

The only thing that I would add to what the others have said:
SQL*Server (and all the other high-end databases such as Oracle etc.)
have true, ACID-compliant transactions. The changes within a
transaction are all committed, or all rolled back. Guaraneed. No if's,
but's or maybe's!

MS Jet does have transactions, but they are nothing like the above. The
writes are simply cached in memory, then written to disk as fast as
possible, hoping that nothing goes wrong in the middle. This is nowhere
near as "all or nothing" as the heavy-duty database products.

In most cases this will make no difference. But if I was writing a
banking application for example, I'd be wary of relying on Jet's native
transactions.

HTH,
TC (MVP Access)
http://tc2.atspace.com

Querying for nearest values...
Wish List: String Concatenation Aggregate Operator
find changed columns in a new version of a row
How to count rows ?
BUG or not?