Increase performance of the database in Access 2010

Hi. I need to make some steps to decrease run time:

1) Time needed to save form
2) Time needed to go from the form design mode to the view mode
3) Time during to run make-table queries

One ways is to do it to create executable file, accde format.
What is the difference between Accdb and accde file type?

Do I constantly need to create executable type of databases to avoid run time issue in Access 2010?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<One ways is to do it to create executable file, accde format.>>

You cannot create and executable file with Access.  An Access DB is more like a document that is read rather then a program.

<<What is the difference between Accdb and accde file type?>>

  a DE has the source code stripped out.  That's the only difference.  As a result, no code can be changed and nothing with code attached can be modified.

<<1) Time needed to save form
2) Time needed to go from the form design mode to the view mode
3) Time during to run make-table queries

 Your covering a wide range there, but in general:

1. Turn off name autocorrect
2. Turn off subdatasheets on all tables.
3. If a JET Backend, keep a constant connection to the BE open.
4. Make sure your prorperly indexed.
5. Make sure your app is compiled (for a DE, it must be since only compiled code is in the DB).

As for #2, that won't decrease unless the tables are local (on the PC).  Move a copy of the BE to the PC and then re-link to it for development work.

Beyond all that for performance, it would go to how the app is developed. i.e. not using Domain Functions in queries, using Dlookup multiple times in place of opening a recordset, etc.

maximyshkaAuthor Commented:
Hi Jim.  Thanks for replies.  Regarding split between front and back end. I'm not doing anything special  (simple queries and front end interface).  
One major issue: a slow network, therefore having tables linked over slow network will not help in such case.
No tables have auto correct or datasheet.

Only approach to speed up is to create "accde" file.  This is strange. Any comments on this one?
Dale FyeCommented:
Is your network a LAN or WAN?
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Only approach to speed up is to create "accde" file.  This is strange. Any comments on this one? >>

 If your accdb is compiled, then there is no difference between the accdb and the accde for all practical purposes.

Jeffrey CoachmanMIS LiasonCommented:
To be clear "Speed" issues in a database is a very broad topic, without being intimately familiar with your entire database, all we can give is suggestions...

<One ways is to do it to create executable file, accde format.>
I have never heard of this as a "Fix" that will magically "speed" up a slow database...

What creating an .accde files does is compile the code and create a version of the database where you cannot make design changes
So perhaps you need to compile your code more often.

Also do not discount the value of running the compact/repair utility on a regular basis.

1) Time needed to save form
2) Time needed to go from the form design mode to the view mode
...It this for only one form or all forms?
If it is one form then the form itself may be corrupt, ...and may need to be rebuilt.
If it is All forms then see here: you never explicitly stated if the DB was split or not...

Other basic stuff is to Create a new database and copy all of the objects into it.
Decompiling may also be needed.

Monolithic applications are known to be unstable when opened simultaneously by multiple users especially over a wireless network.  They are slower to open and more prone to corruption.  The recommended solution is to split the database into FE and BE.  The FE contains everything except tables and the BE contains ONLY tables.  The FE links to the BE and you work with the linked tables as if they were local.  Then, the BE is moved to a shared network folder and EVERY user is given his OWN copy of the FE which is stored on his C: drive.  That minimizes some overhead since the FE is local and doesn't have to be dragged across the LAN.  The connection from the FE to the BE exists only for instants of time where Access is actually reading or writing data.  That is what makes this configuration more stable.  If one use has a problem, he doesn't lock out every one else or corrupt the data.

To speed up the application, I'm going to restate some of the suggestions made by other posters and add some of my own.
1. Compact the BE regularly.
2. Compact the FE regularly (compact on close is not generally recommended).
3. Make sure each table has a primary key and what ever indexes it needs to support searches and joins.  Don't go overboard.  Every index requires maintenance when a record is added or changed so there is a happy medium that will speed up the searches but not slow down the updates.
4. Make sure you have created relationships and enforced Referential Integrity to give the database engine some help.
5. Only select the columns you need in queries.  Don't just Select * because you're lazy.
6. Add criteria to all queries to limit the number of rows returned.  No user needs to work with a form bound to a table of 100,000 rows.

Although distributing as .accde/.accdr is recommended, it won't make any speed difference.  If you create an .accde you MUST, MUST, MUST remember to keep your .accdb safe because you will always need to go back to it to make any changes.  After you make the changes, you will recreate the .accde and distribute the new copy to the users.
maximyshkaAuthor Commented:
Thanks a lot for all your advices.  The main issue that I have worked with Access 2010 before on my previous work.  Everything was fine, so I have reasons to believe that the main issue of the speed is a slow network.

Creating ACCDE file is just a finding a way to go around the problem.

Jeff: I have only only one form, a simple interface.

Additional thing to ask: I used the Access 2010 feature of embedded macro for convenience to run simple queries. I think it is like new feature of Access 2010.
Have anyone heard about errors associated with using embedded macros.
(I did not use VBA code to run simple queries.)
Jeffrey CoachmanMIS LiasonCommented:
It is still not clear if the DB is split or not...
Is the DB Split? Yes or No?

You never replied to Jim on his 5 suggestions...?

You keep saying that your network is "slow", ...please define "slow" in terms of hardware, OS, topology and real chronological terms.

fyed asked if this was a LAN or WAN
You never replied directly here either...

Are you running the FE on your local machine, or is in on the network?
see the link I posted.
You never stated if you tried creating a new database, or tried decompiling...

In other words you keep explaining your system setup, but you rarely state if you have tried all of our suggestions?

In short, you keep saying that it is your Network, with out quantifying this.

(I doubt that embedded macros have anything to do with this...)
maximyshkaAuthor Commented:
Sorry, let me reply:

DB were not split. As I indicated I'm afraid to put back end on slow network.
I'm running Front End both ways on Local Machine and part of network which is not shared drive for testing purposes.

I created database: also, used compact and repair feature. Not sure what you mean about decompiling. I don't have any vba code.  Only embedded macros with simple queries
If the file is self-contained and stored on your c: drive - the network is uninvolved.
If the file is self-contained and stored on a shared drive - the network IS involved.  
If only you are using the application, WHY are you using it from a network drive?  Move it to your local drive.  Then you will know how much the network speed is impacting the application.  If the database is as slow on your local drive as it is on the network, then the app is the problem.

Don't trivialize the impact of macros.  You can make as big a muddle with a macro as you can with VBA.

Make table queries cause substantial bloat.  If you use them in your application, you should be compacting at least once per day.  Keeping the database free of bloat makes it smaller and faster to load.

One more suggestion.  Access seems to want to talk to the default printer frequently and if you are disconnected from the network where the printer lives or you are unplugged from a local printer, go into Windows and change your default printer to be a pdf writer.  That will keep Access from waiting until the device times out before it recognizes that it is not there.

Please go through ALL the suggestions and questions and try to answer each of them.  I have no idea what you have tried at this point.
Dale FyeCommented:
If you are using make-table queries and storing these tables in your front end, you might want to consider using temporary tables created in an external database and linked to your main application.  I've got an article about doing that here.  It explains some of the advantages and disadvantages of temporary tables.
maximyshkaAuthor Commented:
Thanks to all experts:

Your answers were that creating "accde" executable type file will not reduce the run time.

Other answers were about reducing run time by changing modifying tables (using indexes and work on make table queries. All these answer relevant and I will look into them.

BTW: I did "split" on database.  I put main tables on back end and left only queries, form interface and temp tables in front-end database.  This suggestion was listed in many answers.

The issue which I reported in step 1, 2 was not fixed:

1) Time needed to save form
2) Time needed to go from the form design mode to the view mode

I need between 1-2 min in order to save form, go between design to live mode.  Usually, this should take seconds, no matter how big the form is.  As I previously advised my form contains only buttons associated with simple queries. I'm not sure why saving form or going between different modes takes time.

Description of the problem: When I press "Save" or going between modes I see on status bar a note "Form Not responded".  My personal opinion that this somehow connected to locking.  Although, I'm not sure about that.  

I used "Form Operations" -> "Open Form" to navigate between main menu form and other forms for different segments of the population

Do you have any idea how can I improve performance on the form only?  It is very strange when saving form or going between modes takes around 2 min.

BTW: "fyed" thanks a lot for your article.  Very helpful

Regarding other answers: Queries which reflect 40000 records will take more amount of time then queries which take 10-200 records. No matter how simple queries are. This is the reason why I use make table queries.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The issue which I reported in step 1, 2 was not fixed:>>

 There is no "fix" for this.   I mentioned in my this in my first comment:

"As for #2, that won't decrease unless the tables are local (on the PC).  Move a copy of the BE to the PC and then re-link to it for development work."

 This specific problem is a reflection of the network used and shows why you want to split a DB for performance.   The goal always is to only have data going over the network.

 When your developing and moving in and out of design view, Access has to do a number of things.  On a busy network, that can be a very slow process.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Just a note that you can exclude my posts for points consideration.
Your issue has been thoroughly discussed by the other experts.

maximyshkaAuthor Commented:
Thanks to all experts.  Jim, you are right.  The issue was with network.  Follow your suggestions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.