yenling
asked on
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.
Thanks!
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.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
etc....
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!
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
etc....
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!
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!
-Dave
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!
-Dave
ASKER
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?
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?
ASKER
What is the command line to load a form when startup form is being activated?
Thanks!
Thanks!
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,
Cheers.
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,
Cheers.
ASKER
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?
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?
ASKER
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,fiel
--->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?
INSERT [INTO] TableA (field1,field2,field3,fiel
--->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?
ASKER
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,fiel
error:no value given for required parameters....
pls help!
Like:Dim a,b,c,d as string
INSERT INTO TableA (field1,field2,field3,fiel
error:no value given for required parameters....
pls help!
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.
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.
ASKER
How to create dll for that module for public procedures?
Have a look at:
http://internetcollege.virtualave.net/dllAsp/dllAsp.htm
(Create a .dll File with VB and ASP)
http://www.icrossroads.com/~spider/vbmath.html
(ActiveX DLL)
Cheers
http://internetcollege.virtualave.net/dllAsp/dllAsp.htm
(Create a .dll File with VB and ASP)
http://www.icrossroads.com/~spider/vbmath.html
(ActiveX DLL)
Cheers
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
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
Computer101
E-E Moderator
Computer101
E-E Moderator
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
DoEvents
''' Do Something here ..
myQuery.MoveNext
Next i