MS Access, Large amounts of VB, better in a module or form based?

Question

Ok, I have a MS Access DB in which the performance seems to be suffering a good deal. Now I have a large amount of Visual Basic coding assigned to various buttons on the Form, this VB is in the "Event Procedure" part of the "On Click" event for the buttons.

There are numerous controls and If / Else If statements per button, could this be what is slowing down the loading of the Forms? Would I be better off to shove all the VB into a seperate module?

I have run the "Performance" analysis option from the toolbar which comes up with no suggestions for improvement.

Thoughts?

Answer 1

Moving the code to modules will not improve performance. But I doubt your VB code is causing the slowness. I have written Access files with tens of thousands of lines of code, that are barely slower than a new file.

This is because VB is interpreted, ie. the code isn't looked at until it's specifically required, at runtime.



Possible causes for slowness in an Access DB can include:

-Tables with millions of records

-Tables with hundreds of columns

-Hundreds of tables

-Using complex SQL queries (eg. Jet doesn't handle IN clauses too efficiently)

-Accessing the MDB over a network

-Using an external database

Answer 2

With MS Access it doesn't really make a great deal of difference as to where the code is - it's going to be interpreted anyway rather than compiled.



What do you mean by performance 'suffering'. Is it when inserting/updating records, or when reading data - or is it just slow everywhere!



If it's slow everywhere, then it might be an issue with file sharing (I'm guessing that the MDB is on the network) in which case you can separate the Forms and Code from the data - so that you have a 'client' MDB and a 'data' MDB. The client can then be installed on individual machines and use linked tables back to the data...

Answer 3

The real issue with Access is it is NOT a genuine DBMS. It is designed to run very small, simple databases on a desktop PC. It is not optimised for things like multithreading and connection pooling. And it sounds like you are using Access like it was a real production grade SQL database.



To solve your performance issues, I suggest an upgrade. Checkout 'SQL Server Express' on the Microsoft website which, is a free download.

Answer 4

Using modules is good practice of coding but it doesnt increase your performance, time complexity increased will be in terms of fraction of seconds,



1. you check the LAN connection as prev answer or if your server config is not compatible to manage the number of clients then also these issues will happen.



2. if you are running you exe from secondry device other than your own hard drive



3. check the config of your own PC also