Slow Application

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.

Who is Participating?
Valliappan ANSenior Tech ConsultantCommented:
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:


Ryan ChongCommented:

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
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!
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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!
yenlingAuthor Commented:
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?
yenlingAuthor Commented:
What is the command line to load a form when startup form is being activated?

Valliappan ANSenior Tech ConsultantCommented:
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,
yenlingAuthor Commented:
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?
yenlingAuthor Commented:
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?

yenlingAuthor Commented:
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!

Valliappan ANSenior Tech ConsultantCommented:
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.
yenlingAuthor Commented:
How to create dll for that module for public procedures?
Valliappan ANSenior Tech ConsultantCommented:
Have a look at:
(Create a .dll File with VB and ASP)
(ActiveX DLL)

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
Comment from expert accepted as expert

E-E Moderator
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.

All Courses

From novice to tech pro — start learning today.