Printable View
I am guessing that my slow Access front end to an Access back end is just naturally slow. I'm thinking that if I rewrite the front end in VB.net that I will get a significant performance improvement in load and function speed. Am I correct in my reasoning?
Thanks in advance.
No, probably not. I have never written a front end in Access, but that would be using VBA, which is not an inherently slow language. While you may well see some performance increase by changing to .NET, the issue is far more likely to be the design of the program, or the queries, or the design of the database, or some mix of those. Optimizing code performance can save seconds, improving the algorithm can save minutes.
What would be an example of what bad queries look like and how to do them right? The tables are somewhat normalized.Quote:
Originally Posted by Shaggy Hiker
I was thinking that using VS and VB.net to do the front end user interface would make it much faster than Access accdbe(sic) handling the form and screen elements.
Thanks.
In stead of us guessing what is making your Access app slow, why don't you explain what's slow about your app. Just saying it's slow isn't enough information. What does your app do? Have you narrowed down which functions have the most effect on speed. I mean sometimes just doing a simple Compact and Repair will solve the problem. If you've narrowed it down to an area of code, post that code and maybe they will be an .Net solution.
Compact and Repair done frequently. Basically, the app is a database of records which are indexes to PDF files. Click the app icon, a login screen is presented, user logs in with password, then... loading the main form which loads a few tables as record source and presents the first record with that record's details... is the first obviously slow loading step. If that loading step were anywhere as quick as opening an Excel spreadsheet with data - I would be happy with it. This is a good place to start as the user experience at the outset sets their mood. The tbl_Methods doesn't have more than 100 records. That's why I have blamed the load time on using an Access front end, and thus the question about VB.net maybe as a fully Windows application could loading and displaying the main record list faster. If the time is being prolonged by loading the table from the Access back end elsewhere on the WAN - then maybe I should follow the path of a slow/bad query. In Access the frm_Main is bound to the tbl_Methods.Quote:
Originally Posted by wes4dbt
I don't use parameterized queries but the ones where I build the SQL using VBA and 'controls' on the form to supply the parameters for the queries.
I'm just now beginning to understand what is meant by parameterized query. I thought I was making my app fast by composing the queries in VBA to be SQL style statements that the Access back end would understand and work with - and not saving all of these queries in the front end, expecting that took extra step(s) to implement vs my VBA SQL.
what is in the Forntend and what is in the Backend ?
usually only the Tables are in the Backend and you create Linked Tables
in the Frontend to interact with your created forms
but only a 100 Records in the Table shouldn't slow the App down, there is
something else going on with your programm
If the app resides on the network and is slow at startup then my first guess would be the network is the choke point. Would be easy to test, move the app to a local machine and see if it starts faster. The question is what is slow? Does it takes 5 seconds to start or 30? With the small amount of records you have, it really should pop up quickly. I have Access apps with 1,000's of records that pop up in a couple of seconds but they're not on a network.
There is no example of a good query design. It depends on the data and the layout, which means that every situation is a bit different. Generally, there should be indexes on any field that is used in a WHERE clause, but with only hundreds of records, it really shouldn't matter whether there are indexes or not. Therefore, I' be leaning towards the network being the issue, as well. Using Access across a network can be problematic at times, though I know plenty of examples where it is not, so even that isn't a sure thing.
Once your networked and you go past a certain size and number of users Access performance degrades, you may have other issue but if your looking to make changes for the longer term and your intending your application to run for a reasonable number of users simultaneously and your thinking about rewriting it then i would look at the Database first and move that to SQL Server Express.
Saying that if you just want to find ways of making speed improvement right now with your current application then i would be looking specifically here
Firstly can you post the query your using to load the records here?Quote:
loading the main form which loads a few tables as record source and presents the first record with that record's details... is the first obviously slow loading step
and secondly are you using Primary/ Foreign Keys & Indexes on your tables ?