Improve company productivity with a Business Account.Sign Up


Slow Application

Posted on 2001-06-22
Medium Priority
Last Modified: 2010-05-02
VB/Access program

I?ve developed 17 forms, 72 public sub procedures in one module, 1 class module, 1 data environment, 12 data reports.

During development phase (local drive), there?s no much delay in accessing data from the database and opening/closing one form. During testing phase (network drive), there are delay in accessing data and opening/closing one form does overlapped one another, Main form is in the processing of opening, Password form closing image can be seen.

Password form
e     Start-up form.
e     Do a checking whether the user has ?user? or ?supervisor? access rights.
e     Disable the visible properties of 20 menu items for user access rights.
->Open Main form

Main form (Menu editor)
->35 menu items in the menu editor.
->?User? can access only 20 menu items.
->?Supervisor? can access all menu items.  
->Every click on any menu item will open a query form for user to enter a item code.

Query form
->User need to enter an item code and open another form depends on which menu item is clicked.

My Questions:

1)In Password form, when I clicked ?OK? button, it will open Main form but the processing speed is unbearable. How to speed up my application?
2)Will a connection with the ODBC speed up my VB application, as compared to using connection string?
3)From MSDN, there are many techniques like keeping forms hidden but loaded, Preload data, Simplify your start-up form.
4)How to code and display a progress bar running to keep track of what has been processed.

Question by:yenling
LVL 56

Expert Comment

by:Ryan Chong
ID: 6217780

1. What technology you use in your application, ADO, DAO?

2. ODBC will speed down your VB application!

4. PBar.Max = Max

For i = 1 to myQuery.RecordCount
   PBar.Value = PBar.Value + 1
   ''' Do Something here ..

Next i

Accepted Solution

Valliappan AN earned 320 total points
ID: 6217878
Some tips:

1. You could use a Splash screen, in the project startup and in the background, you could load the startup for, so that shows no delay to user, when app. starts.

2. When opening recordsets, always, get the data, that which you want, and not all data. i.e. Use "Select * FROM", in less instances, if data is large.

  Retrieve the necessary fields only if possible.
  Open records asynchronously, so that the records retrieval will come up fastly.

3. Use mouse pointers when there is a delay in process, to show to users. As you have mentioned, you could use progress bars, say, when a recordset is loading, or a long process is activating.

4. Try to use direct SQL statements for INSERT, UPDATE, DELETE etc. as much as possible, instead of recordsets, which are slower.

5. Try to replace the Data controls, and use Recordsets if possible. This is my opinion, about this.

Have a look at this link. Hope, you definitely have a lot of tips to learn on how to improve performance in VB:



Expert Comment

ID: 6218457
valli_an makes great points.  Especially when it comes to the SQL statements and pulling recordsets that include only the records and fields that you need.

Instead of "Select * From Table1", try

Select field1, field2, field3 From Table1 Where field3 = YourCriteria

This takes much less memory for your application.


Also - look at all of your publicly declared variables and constants.

For example, if in the declaration section of your forms, you do the following:

Public iVar1 as Integer
Public iVar2 as Integer
Public iVariable
Public sVar1 as String
Public sVar2(99) as String

This takes up additional memory because your application must allocate the memory before the form is actually displayed.

Consider instead, modifying your code to use more variables within the procedures.

If you feel that you need variables to be Public - consider sending variables as parameters between procedures and functions.  This will improve the start-up performance of your application.

Also, an additional benefit is that it will help your code to be more portable between applications.

By this, I mean that you can more easily re-use your code because you will pass parameters for all of the information necessary to perform the tasks of the procedure or function.  You can simply cut and paste your code to any application and know that if you pass the indicated parameters, your code will work properly.

Another point to make about the Public variables I noted above - declaring public arrays when they aren't required to be public can really drag the memory requirements of startup - you have to allocate enough memory for all elements of the array.

An array variable such as Public sVar2(99) as string must allocate enough memory for 99 string variables.  That can add up quickly if you have many public arrays declared.  Especially if you only use them in a couple of places.  They will remain allocated in memory for the life of the application.


Related to your 72 Public prodeures in your module - I would look at the necessity of each of them being public.  If they are all necessary, consider creating a seperate dll just for that module.  Then the application won't have to load all of them at start-up.  They will only be loaded when called by your application, and then the memory will be released when the call to the dll returns.


Splash Screens - They are very good if you must load forms that require a lot of resources or a longer time to load.

Adding a progress bar to one will also help the patience of the user.

Valli_an's link is also a good one.  You should definately read through that.

Good Luck!
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Expert Comment

ID: 6218561
Ok, my two cents... since the application was designed poorly you may never get the results you're looking for.  But to speed it up.  Change all of your bs controls, like labels and frames into control arrays.  The control array acts like a single control in memory, hence the savings.  Also, any code you place into a form is validated when the form opens.  But code that you put into module(s) is only validated when it is accessed, saving you the form load time.

other than that, make doubly sure that you are cleaning up all of the objects you create, once they are no longer needed.

Good Luck!

Author Comment

ID: 6223118
I?ve already set up the ODBC connection from Data Sources (ODBC) in the Control Panel.What is the next step?

My startup is a login screen.I'm using a Jet *.mdb using ADO and the Jet 4.0 OLE DB provider and create a recordset against the database itself.Does it slow down my system?

Author Comment

ID: 6223174
What is the command line to load a form when startup form is being activated?


Expert Comment

by:Valliappan AN
ID: 6223187
Can you put the OK_Click (or whatever) code on your password form, and the Form_Load and Form_Activate events code of your next form you are loading. The problem, could be in these locations, if I am right.

Also, the problems could be that, you load a big module in memory, with lot of functions, so it decreases the speed.

Hope this helps,

Author Comment

ID: 6243825
Related to your 72 Public prodeures in your module - I would look at the necessity of each of them being
public.  If they are all necessary, consider creating a seperate dll just for that module.  Then the
application won't have to load all of them at start-up.  They will only be loaded when called by your
application, and then the memory will be released when the call to the dll returns.

>>How to create dll for that module?

Author Comment

ID: 6262136
1)Any tutorial for using direct SQL statements for INSERT, UPDATE, DELETE etc?Pls show me examples for each statement in complete code.

INSERT [INTO] TableA (field1,field2,field3,field4) VALUES ('1','3','A','1254');"

--->This is my sql statement but I don't know how to use it.(I'm used to used recordset)

2)How to create dll for that module for public procedures?


Author Comment

ID: 6262187
Regarding question 1,how to pass defined-string variables into the insert statement?

Like:Dim a,b,c,d as string

INSERT INTO TableA (field1,field2,field3,field4) VALUES (a,b,c,d)"

error:no value given for required parameters....

pls help!


Expert Comment

by:Valliappan AN
ID: 6262811
Regarding your question on passing parameters to INSERT query, if I had understood properly, It is like this.

Assume a,b,c,d are variables containing values, say field1, field2 are numeric and field3,field4 are string values.

Conn1.Execute "INSERT INTO TableA (field1, field2, field3, field4) VALUES (" & a & "," & _
                    b & "," & _
                    "'" & Replace(c,"'","''") & "'," & _
                    "'" & Replace(d,"'","''") & "')"

n.b. A Replace command is issued to c,d to avoid errors, if there is a single quote in the value of the variables c,d, by escaping single quotes in string value to double single quotes.

Hope this helps.

Author Comment

ID: 6275629
How to create dll for that module for public procedures?

Expert Comment

by:Valliappan AN
ID: 6275708
Have a look at:
(Create a .dll File with VB and ASP)
(ActiveX DLL)

LVL 49

Expert Comment

ID: 7139682
Hi yenling,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

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

yenling, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer

Expert Comment

ID: 7174003
Comment from expert accepted as expert

E-E Moderator

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

606 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