[AccessD] Access Data Retrieval

Jeanine Scott jscott at mchsi.com
Thu Jun 12 08:42:02 CDT 2003


Thank you all for the information!! I will definitely print it out and
absorb it!

Drew - thanks for your offer - that's so nice of you!!!

John, we do have the approval to go to SQL. I've known we were going to go
to SQL  - with web based being the ultimate goal since the beginning of the
project. I've tried to design the app for SQL as much as I was able but have
to confess that we do use quite a few VBA functions. I'm not positive we'll
be able to create stored procedures but I'm really pushing for that. We'll
probably have to take two routes. With the forms and some of the code we'll
probably have to use the linked tables approach. With a lot of the behind
the scenes processing - of which there is a ton! - we'll use direct SQL.
Calls.

Again - thanks a lot!!

Jeanine

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Wednesday, June 11, 2003 8:16 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access Data Retrieval

Stuart,

<<As Drew said,  Jet pulls the Index, not the data across the network.
It then decides which records to retreive and pulls just those.
Still with 100,000 records, just that one Index will show as
noticeably traffic on the network. >>

  It doesn't even pull the entire index.  Probably 5-10 pages at most.  As I
said in my post, Jeanine can easily check what's going on with SHOWPLAN and
ISAMSTATS.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stuart
McLachlan
Sent: Wednesday, June 11, 2003 6:22 PM
To: Jeanine Scott; accessd at databaseadvisors.com
Subject: RE: [AccessD] Access Data Retrieval


As Drew said,  Jet pulls the Index, not the data across the network.
It then decides which records to retreive and pulls just those.
Still with 100,000 records, just that one Index will show as
noticeably traffic on the network.   A straigth VB app using an
Access BE will do exactly the same.

If you move to SQL Server, the engine on the BE will do all the work
and ONLY pass back the required data. Again it doesn't matter whether
the FE is Access or VB - they will both get their data in the same
way and generate the same amount of network traffic

On 11 Jun 2003 at 13:58, Jeanine Scott wrote:

> 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


--
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.



_______________________________________________
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