Mark said:
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!
There 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.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
