Solved

VB- ACCESS

Posted on 2001-06-15
11
150 Views
Last Modified: 2010-05-02
I have an app that load 75000 records into a grid.
<Dont ask me why>

The Grid is bound to a Data Control.
VB3.


When i try making the same form in vb6.  using ADO data control.. its all good.. but the form takes like 8 seconds to load up..  
understandably.. as there are alot of records.

but in the VB3 App.. the form just pops up like that are 100 records. really fast.

The SQL statements are the same.  "Select * from MyTable "

I will note that in the VB3 app.. as i scrolled down the list.. there were pauses.. as if it was fetching the data..
Kind of like SQL Server Enterprise manager does.. or access..

Anyone know why this behavior is happening?
0
Comment
Question by:TheAnswerMan
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6195868
You could set the .cachesize property of the data control in order to restrict the number of records locally loaded in memory.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 25 total points
ID: 6196305
Try like this: you'd get it faster.

Set rs = New Recordset
rs.Open "Select * From MyTable",Conn1,adOpenKeySet,adLockOptimistic, adAsyncFetch

'now you can do operations. it will still retrieve records by the time, but appears faster.

'if you need to do something till it retrieves everything do:

Do while rs.StillExecuting
    :
    your code, like DoEvents.
    :
Loop

Thats it. You could also restrict the no. of records retrieved, by using the top command, so that your system won't crash.

Select top 10000 * From myTable

hope this helps you.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6196311
You might link your grid to recordset, by setting the datasource property, of the grid to recordset.

Or, I am not sure, the datacontrol, has such a property, for Async Retrieval.

0
 
LVL 3

Author Comment

by:TheAnswerMan
ID: 6203145
I am not asking about how to optimize my query.

The current VB3 app connects with the same SQL and appears to be 10 times faster.

I say appears.. becasue as I scroll through the VB app.. it appears to fetch the data.. kind of like opening a table in SQL Server Enterprise manager.

Ie: it opens right up.. but to see the last records.. you
have to pull the scrollbar all the way down a few times.


the VB6 App.. appears to fetch all the records.. and THEN put them into the grid.

The VB3 app.. appears to getch a few records... and when i scroll down the list.. the grid is initiall blank.. and then fills up with the data.. as if if fetched it from the server.

There is no code to do this fetching.. just a bound grid.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6203583
We had a similar problem using recordset as datasource for datagrid in VB6, when number of records is more.

So, we used the AsyncFetch option, which is meant to do some fetch (I hope, as per InitialFetchSize), and then allowing for other activities, when it retrieves further, if I am right.This is the part, we changed.(Note the last parameter, which does what you are telling about, in VB3.0, if I am right.)

Set rs = New Recordset
rs.Open "Select * From MyTable",Conn1,adOpenKeySet,adLockOptimistic, adAsyncFetch

For its correct usage, check this link:
http://support.microsoft.com/support/kb/articles/Q224/3/32.asp

The reason of speed difference could be, as I hope, that VB3 used DAO and in VB6, you use ADO, which is still a new technology, while DAO had undergone several changes in VB versions.

Hope this helps.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:TheAnswerMan
ID: 6219495
appreciate the help everyone.. but was asking about why VB3 Code was appearing to fetch the data instantly.. but not really, as when you scrolled.. it appeared to "fetch" the data.

I used the same exact code for the vb6 project.. except ADO control instead.

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7088670
Hi TheAnswerMan,
You've requested to delete this question, but its status has remained as 'Pending Delete' because one or more comments have been added.  Normally, the only way to fully delete such a Question is to post a message to Community Support and ask for assistance.

EE is making a one-time database sweep to purge the Pending Delete Questions automatically.  During this sweep:

    TheAnswerMan -- To allow the deletion to proceed:  Do nothing.
    EXPERTS -- Please DON'T POST a comment except to contest this deletion.

In the future, please refer to http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp#8 for instruction on deleting questions.

DanRollins -- EE database cleanup volunteer
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 7091847
As mentioned earlier, DAO (which you used in VB3) looks to be more faster than ADO, in case of Access, which is testified by many experts here.

Cheers.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7114720
Listening further for TheAnswerMan response.
Moondancer - EE Moderator
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7136179
Moderator, my recommended disposition is:

    Accept valli_an's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7168570
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now