Per Brad:
What are the advantages and disadvantages of using Access rather than an SQL
database? What are the risks, backup issues, data integrity issues, etc in
using Access?
Here's a copy of some notes I made up for a client some years ago:
http://tinyurl.com/g6ba8
And here's one of Trevor Best's posts on the subject.
Trevor has undoubtedly forgotten more than I'll ever
know about MS Access development.
-----------------------------------------------------
Yes, Access will not handle 1000 users, concurrently. You can take two of
the zeros out, that's nearer the mark.
Transaction control: Access can do transactions, commit and rollback,
etc. it locks everything in it's path like a steamroller so plenty of
error trapping and handling required as be prepared to be locked out of
something while others are on line. I have no idea how SQL servers react
on this front. In both cases, increating ODBC timeouts and locking
retries would help, be slower but slower is better than crash.
You pays for no more features in SQL server here:
Access 1
SQL Server 0
Security: (The policeman kind) Access is not invincible, the security is
not to be taken lightly, it's not the easiest thing to work with. And the
file is available in dos/file mangler/exploder, etc.) by comparison, the
mafia protect the SQL server.
Chalk one of for our big database friend
Access 1
SQL Server 1
Security: (The "your data is safe" kind) Well, in Access is not really
safe, database corruptions are your biggest problem, it's not too robust,
abnormal program terminations can corrupt the data (especially in version
2.0 with no service pack installed). And the file is available in
dos/file mangler/exploder, etc.)
SQL Server data is generally safe in this respect, especially on a NT Box, I've
exhausted the scillian
connection so this time it's protected by ninjas.
In terms of value, this is worth more than 1 point but I'll be kind to
Access as some people don't like others slagging it off in this group.
Access 1
SQL Server 2
Performance: You mentioned dialup connections, will the dialer be
accessing the database directly or is the data just exported to an ASCII
file? If the former than Access is a no no. For small sets of data and
few users, Access will beat the pants off a SQL Server, as data grows and
number of users grows then Access performance tails off, SQL server
performance will tail off but to a lesser extent (hardly noticeable
compared to Access) so quite soon SQL server will perform better.
Access is a hot hatchback, SQL server is a diesel locomotive, unladen you
know what a Golf gti is capable of, but pulling 600 tons of steel the
diesel will trundle along, the Gti would spin it's wheels until the
engine blew up.
In low bandwidth / heavy network usage scenarios, Access gobbles
bandwidth.
Access 1
SQL Server 3
Integrity: (I assume Referential Integrity) Access has it and it's a darn
sight easier to implement in Access (in the relationships window it's all
drag/drop/click and very viewable) compared to writing triggers (if the
server supports it).
In terms of having it. it's a draw here, but Access wins with the ease of
use bit.
Access 2
SQL Server 3
Recovery: You can give up your day job, Jongleurs awaits you. In Access,
recovery means last nights backup, in SQL server it means last nights
backup then roll forward all transactions from the transaction log since
the backup (you didn't turn off logging hoping to gain a bit of extra
speed did you?).
Again, if the data was shot at 5:30pm, you loose a days transactions,
this is worth taking away points from Access and giving 10 points to SQL
server but I'm in a good mood today.
Access 2
SQL Server 4
I have logged transactions in Access as they occur in order to replicate
to another db in another site but it's slow when entering line items in a
subform for a store requisition and the speed typist there hates me for
it. To be fair, the logging isn't the only thing that goes on in there,
it checks available qtys and updates reserved qtys as each item is
entered.
If it were down to ease of use / cost then Access walks it, but if (like
many) you are concerned about robustness/recovery then plumping for
Access should be a sackable offence. What would you keep your money in? A
3" thick molybdenum/steel box concreted into the floor or a brown paper
bag?
I have time and time again encouraged our clients to go straight into SQL
server, they whine about cost, I tell 'em how much it costs to lose so
much data and not be able to recover it, they don't believe me then they
find out the hard way, then blame me for installing Access, the Cupid
Stunts.
As I say always, no matter which way you go, never on any account start
with Access and upsize later, if you decide (and only you can decide)
that Access is the tool for storing your data, then stick with it. You
can write some seriously fast data-access code with DAO, .index and .seek
methods are hilariously fast, you don't need a stopwatch on them, your
stopwatch doesn't measure time in such miniscule amounts.
If however the plan is to upsize at a later date, you (or another doing
the upsizing) will curse the programmer responsible for using .index and
..seek as it only works on Access tables and much code will need to be
rewritten, so generic functions will come into play, these are not
optimised for Access and not for SQL server either, code optimised for
SQL server won't run against access tables (utilising stored procedures,
etc.).
If you plump for SQL server in the first place, you can build the app
around it's features (stored procedures in particular) and not worry
about upsizing (cause it's already the right size) and downsizing is
nothing to worry about, no-one in their right mind would dream of it.
If I were in your shoes (hope you're size 8 otherwise they'd be
uncomfortable
then I'd go for a SQL server solution straight away, in
terms of robustness, you gets what you pays for, in terms of performance,
again there's SQL servers of different size and cost, e.g. SQLAnywhere
(Cost comparable to a desktop rdbms, it's actually given away with some
products) will work like Sybase as it's been breathed on by Sybase (it
was Watcoms server) but it sure won't perform like it. Sybase is
generally the most up to date, Oracle usually get the recognition for
advanced features but Sybase was always the first in with things like
triggers and stored procedures. MS-Sql Server used to be licenced from
Sybase but Ms have now taken over it, you can bet your bottom dollar it's
the easiest to use and to connect to with an Access or VB front-end, hell
even I managed it whereas I failed miserably with Personal Oracle 7 and
SQLAnywhere (Well, the MS one had documentation with it, but it just
appeared to me in the ODBC manager).
\|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
To reply my mail, replace the "nospam" in my address with "trevor",
this was put on in defence of the spam robots that roam usenet.
MS Access FAQ now available on my site below.
http://easyweb.easynet.co.uk/~trevor/
RAM DISK is not an installation instruction.
