Drew Wutka
DWUTKA at marlow.com
Wed Jun 11 17:11:22 CDT 2003
Oh, BRING EM ON!!!! <grin>
First of all, Access is a superb database system. Actually, JET is, Access
is a superb RAD. JET is a client side database. SQL Server is a Server
side database. There are distinct differences. A client side database
shifts the processing to the local computers (where you usually have lots of
available processing time), server side relies on that one machine to do all
of the processing. The advantages of a server side db, are that you can set
triggers (data driven events....since only one machine is reading/writing to
the database, it is that much easier to have that machine allow for 'traps'
when certain things are read or written too. Also, server side databases
send mostly data through the network lines, so network traffic is lower,
usually, with a server side db. Finally, a server side database keeps
transaction logs, so when a failure occurs, it can bring the database back
up, by going through it's transaction logs. Disadvantages of a server side
database are complexity, and price. If Joe Schmoe wants to create a
database to track the time he spends on the phone, he is going to find it a
little over his head to set that up with a SQL Server...not too mention the
access rights he would need in order to do anything in the first place.
Also, the pricing on a Server side database is three fold. You have the
software, the licenses per user/connection, then the hardware to support it
all. On a client side database, it is run off of a file server, which is
supporting all of your non-database files too, so it isn't a dedicated
machine, not in respect to a database.
The advantages of a client side database are that it's cheaper, easier to
develop, more portable (you can copy an .mdb to CD, bring it home, and open
it...try doing that with a SQL server....<Grin>) and you don't need a
machine dedicated just to that db. Disadvantages, no triggers, no
transaction logs, and more importantly, you are dealing with a file size
limitation (in Access 97 1 gig).
Now, when deciding what to use for database development, do three things.
First, ignore costs initially, money shouldn't get in the way of deciding
the system requirements. Second, determine how much data is going to be
stored/added into the system. If your database isn't going to exceed an
Access db's limit, that is a big plus for Access. You can certainly dump a
lot of data into an access database, but the maintenance level needs to be
looked at. Dumping 500 megs a day into a database is going to cause a
nightmare with Access. Even on a Server side db, you'll run out of space
pretty fast, but you can compensate with more drive space. Third, determine
the reliability of the system. Access is extremely reliable. However, if
you are talking about a system where 1 lost bit of information is going to
cost millions, then SQL would be the better choice. Do not look at
reliability in respect to downtime, because both can be down just as long.
The only reliability issue between a client side and server side db, is then
the moment the power goes down, you are going to lose everything going into
either. However, depending on the timing with a client side, you may lose
some information sent right before, where as the transaction logs on a
server side will bring you back up to the moment of failure.
Those two factors should determine what you go with(size and reliability).
Nothing else. Money shouldn't be involved because SQL is more expensive,
however, if it is necessary, then it is a necessary expense. Access is
cheaper, but building an application in Access, for something that needs to
run in SQL is just wasting money.
Network traffic isn't an issue either. Access can be made to run as a
pseudo server db. Simply put the db on a web server, and interact through
the IIS server. Web based FE's are lighter and very flexible.
Feel free to send this to your IT fellows....I'll even give you my number if
they want to call and talk...I'm an IT fellow too...and nothing aggravates
me more then an unfounded accusation. (The two big problems that cause the
'Access sucks' myth in IT Departments is that IT guys want bigger and faster
machines (so the slight reliability advantage comes up.... and IT
Departments want to control the stuff within their realm (they are going to
get calls on it, so they need to support it), and since Access is really
controlled by the develops (and users if developers don't lock their stuff
down), it leaves the IT department in a VERY open position.).
Drew
-----Original Message-----
From: Jeanine Scott [mailto:jscott at mchsi.com]
Sent: Wednesday, June 11, 2003 1:59 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access Data Retrieval
I don't know how much traffic. All they will tell me is that the network
usage "spikes" and I don't know what questions to ask to find out more. What
I'm concerned with is when we move to SQL that it will still be a problem
because "access sucks" - their viewpoint - not mine.
I'm working as a consultant for a Fortune 500 company which has it's own
extremely large internal IT department as well as consultants from other
companies and it's like pulling teeth to get any kind of information. I have
a lot of experience in designing relational databases and believe it or not
(considering my questions to this list) a fair amount of experience with vba
(thanks in a large part to this list!) but very little experience on how
data transfers work or the infrastructure of the network and such. So, any
information you all can give me is extremely helpful!
Jeanine
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Drew Wutka
Sent: Wednesday, June 11, 2003 1:19 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Access Data Retrieval
First, the VBer's are wrong. You are actually going to pull the index for
the Autonumber field across the network. It is then going to pull the chunk
it needs for actual tables. How much traffic are you actually seeing?
Drew
-----Original Message-----
From: Jeanine Scott [mailto:jscott at mchsi.com]
Sent: Wednesday, June 11, 2003 12:36 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Access Data Retrieval
I need something explained to me - this just doesn't make any sense to me
but I've had quite a few hard core vb developer's swear that the following
scenario is how Access retrieves data.
Scenario:
FE on client
BE on Server
tblLoans has 100,000 records with 3 fields
tblLoans
UniqueID - AutoNumber
LoanNo (indexed)
LastName (indexed)
You bind a form to a specified recordset such as
"Select * from tblLoans where UniqueID = 1"
or
"Select * from tblLoans where LoanNo = 101"
What I have been told is that when Access goes to the server it brings ALL
the records 100,000 records back to the client and then applies criteria to
find out which record you actually asked for. So, in effect you are
returning 100,000 rows of data back to the client even though you may have
only asked for 1.
Is this correct? If it is why is this the case and how in the world can I
ever justify using Access as a development tool to a hard core VB'r?? If
it isn't correct how can I explain in the exact scenario above the fact that
the LAN tests show mega tons (ok -technical term there...) of data being
passed back to the client when this form is called?
I'm understanding that SQL will work the same way?
Thank you so much!
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com