Salad,
Server databases are useful when
* dealing with VAST amounts of data (I doubt an MDB would be sufficient for
the Taxpayer table in whatever DB the IRS uses to keep income tax data, or
the history of purchases at a huge retailer like Amazon) as both MDB and
ACCDB are size-limited.
* dealing with large numbers of concurrent users (255 or more is the
physical limit of users that can be actively using an MDB, and I believe the
same limit applies to ACCDB, but 100 users is a practical maximum, IMNSHO).
You can handle more than 255 users when you use split front- and back-end
databases, but no more than 255 can be using the shared back-end MDB
database concurrently.
* the data is "mission critical" real-time data where extremely rapid
recovery is a requirement -- server databases typically have built-in
logging and recovery functions and, generally, are not corrupted by random
communications and power outages as a file-server database may be. This
generally is "abbreviated" as "reliability and recoverability". The work
needed to create a similar logging and recovery facility for Jet would be
significant; I've seen a few databases where the implementers chose just a
few critical tables and did that, but in many of those cases, I suspect
using an inexpensive (or "free") server DB would have been a better
investment.
In my personal experience, reliability and recoverability were the most
common requirements mandating a server database.
* if you have users on a WAN, especially if you have many users, because
more (not nearly all, especially if the application is well-implemented with
indexes) data has to be moved from the data store to the user's machine
(where the Jet or ACE database engine actually executes) where appropriate
use of a server DB can result in just the request (e.g., SQL) and result
data being transmitted.
* if you need extensive analytical capabilities for large amounts of data
(Mr. Kempf is correct on this, but fails to mention that to get all the
analytical and reporting features of MS SQL, you need the not-inexpensive
paid version).
* (this is a very common occurrence) your client's or your company has
adopted a server database as their corporate standard, and the IT department
insists it be used, or refuses to provide support for any other database you
choose. I've worked on some databases where this was the case, even where
the contractor doing the work would have preferred to use a different server
DB (e.g., MS SQL Server).
All that said, there are a very large number of database applications where
the MDB or ACCDB database is perfectly adequate -- individual and small
workgroup applications with fewer than 100 users which are on a single
machine or a LAN, which do not have immediate recovery needs (even though
the information may be critical to the business, most call an application
'mission-critical' only if it does have the stringent recovery
requirements). And, unless you are analyzing huge amounts of data, it can
be surprising what you can accomplish with a combination of Access (Jet,
ACCDB) and Excel. Access reporting is certainly the most capable reporting
function I have ever used on a PC, so I'd need a lot of convincing that
wouldn't be sufficient if someone proposed a _requirement_ for MS SQL Server
Reporting Services.
I've been using Access almost daily since 1993, and somewhat over half the
"paying work" I've done has been with Access front-ends to server databases,
all but a few used MDB, Jet local tables, and linked ODBC-compliant tables
on a server. Not only did I _not_ find ADPs to be "easier or better" but
found the ones I dealt with to be clumsy and inefficient because most had
been implemented by "refugees from the VB world" who did not understand
databases, and, thus, did not make even minimal, much less effective, use of
Queries, and such Access-specific features as Subforms.
(To be fair, I have also worked with a few MDB databases that appeared to be
a "classic VB application" implemented in Access, over the years.
Fortunately, in most of those MDB cases, we had authority to redo the most
inefficient parts. I have also had the good fortune to be in a position to
turn down some work on databases with all unbound forms and code accessing
the data, when I knew that customer expectations regarding requirements,
time, and effort were so unrealistic we could not have a successful project
outcome.)
Regards,
Larry Linson, Microsoft Office Access MVP
"Salad" <(E-Mail Removed)> wrote in message
news:3eKdnS8zId-(E-Mail Removed)...
> Access Developer wrote:
>
>> "aaron.kempf [MCITP: DBA]" <(E-Mail Removed)> wrote
>>
>> > there is NOT a version of SQL Server called 2010!
>>
>> I encourage you, and anyone else who's interested, to Google or Bing
>> "Microsoft SQL Server 2010". If you weren't/aren't aware of that product
>> in the pipeline, you are nothing but a pretender to SQL expertise, no
>> matter what outdated certifications you claim. But, it seems reasonable
>> if the release date is further delayed, they'll rename it to "2011".
>>
>> FYI, you haven't a clue to my SQL background. I will say that the ten
>> years experience about which you brag (true or not) is not impressive.
>>
>> Larry Linson, Microsoft Office Access MVP
> Larry, could you, or others provide some info? I have been doing Access
> for some years but I never was with a firm that seemed to need SQL Server,
> the MDB seemed to do a good enough job. But SQL Server is so popular it
> is worth knowing about.
>
> Looking at this link http://www.youtube.com/watch?v=bZX-CwRZE1w the author
> creates an ADP to NorthWind contained on SQL Server.
>
> It looks straight forward and easy to do.
>
> Then there's this link http://www.youtube.com/watch?v=b1Bp1QVcGLw where
> the author creates an ODBC data source. He selects the SystemDSN tab.
>
> What is the preferred method? Or does it matter?
Similar Threads
