[AccessD] Access Data Retrieval

Charlotte Foust cfoust at infostatsystems.com
Fri Jun 13 10:48:27 CDT 2003


Oh, yeah, and try restoring a backup in it.

Charlotte Foust

-----Original Message-----
From: Mark H [mailto:lists at theopg.com] 
Sent: Thursday, June 12, 2003 11:45 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access Data Retrieval


And you can always use MSDE which is free with Office... :o)

Mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: 12 June 2003 19:42
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Access Data Retrieval


Yes, but if you are the SQL developer, you should be able to
backup/restore. Also, if the FE is an ADP, how much easier is it to
reconnect your FE to your BE compared to the linked table manager in
Access (especially if you are linking to more than one db in Access)

D

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Thursday, June 12, 2003 10:48 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access Data Retrieval


Now, Arthur.  Restoring a SQL database is not *quite* as straightforward
as a file copy, and it requires someone who can DO it from SQL Server.

Charlotte Foust

-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com] 
Sent: Thursday, June 12, 2003 4:13 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access Data Retrieval


Your statement contains several specious arguments, but I'll concentrate
on just one:

"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>)"

Nothing to it. I do it almost every day. Back up the SQL database, copy
the .BAK file to a CD, bring it home, restore the database. One tiny
step more complicated than a straight file copy.

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: June 11, 2003 6:11 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Access Data Retrieval


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
_______________________________________________
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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list