Speeding up Front End DB while still developing (1 Viewer)

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
Hi,

I was just wondering what I could do to speed the Front End DB a little up when I still work on forms?

Currently I work on the Frontend DB and it is still in the development stage so alot of editing and changing.
Now when I work on a form or have to update fields in that form as the query has changed it becomes rather slow.

Not sure if that is always the case with your Databases but if it is then how can I speed the database a little up can you temporarily disconnect from the Backend
or is there a trick I could use?

First Time Back Ending so sorry for not knowing it.

Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:01
Joined
Oct 29, 2018
Messages
19,166
Hmm, you shouldn't develop using live data. It's recommended to have three versions of your db: dev, test, and live.

You make changes in the dev version, give it to users for testing, and then send it to production after fixing all the broken parts.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:01
Joined
Feb 19, 2002
Messages
36,871
Are you backing up and compacting the FE regularly?

Is the db split? are you compacting the BE regularly?
 

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
Are you backing up and compacting the FE regularly?

Yes I do
Is the db split? are you compacting the BE regularly?
Yes I did split it and work on the front end at present the back end of course is closed.
 

strive4peace

AWF VIP
Local time
Today, 16:01
Joined
Apr 3, 2020
Messages
921
hi @silentwolf

make a copy of the back-end to connect to whilst developing. As @theDBguy said, ypou want to make sure you aren't using live data to develop. Put this on your local drive in a short path. The FE should also be on your local drive.

Aside from that, there are lots of things you can do in the design of the database to make it faster.

Perhaps you can use this tool to document your BE?
download:
http://msaccessgurus.com/tool/Analyzer.htm

here's a video on YouTube about it:

After you run it on the BE, look at the Table Summary and Deep Analysis reports. When you look, right-click and export to PDF. Then share those PDFs with us so we can see how your data is structured. Thanks!
 

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
Hi Chrystal,

many thanks for your help!

I did download the Analyzer can I store that File anywhere?

As I opend the file I got an Error wich then created a Log File...

I stuffed it up :oops:
 

Attachments

  • ErrorLog_Analyzer.txt
    10.2 KB · Views: 50

CJ_London

Super Moderator
Staff member
Local time
Today, 22:01
Joined
Feb 19, 2013
Messages
14,695
If your FE development is resulting in slow performance then consider what might be making it slow- overlarge datasets and poor indexing are common reasons.
 

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
Well It is slow when I am editing forms with underlaying Queries.

I mean as I still deveop there are often things to change as field names as mentioned in previous posts.

When I open a form and want to fix it then the db gets rather slow and does not respond for a little while.

When it is fixed then it works ok I guess..
As I said first time splitter lol so all rather new and not sure what is "normal" and what not so to say.

Yesterday I did a Analizer not sure in the English Version exact name "On database tools" and there was not much of complains about it.
The few things which where not correct I fixed.

Indexing well I do have a few but I am not really over do it, again not really sure what is the best amount of indexing in tables.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:01
Joined
Feb 19, 2013
Messages
14,695
Index all fields that are used in joins and those that are regularly sorted or filtered on. The only exception would be those field ms with a limited number of options such as booleans

if you are making lots of code changes then also consider decompile- recompile ( not the same as compact - repair
 

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
Index all fields that are used in joins and those that are regularly sorted or filtered on. The only exception would be those field ms with a limited number of options such as booleans

Ok I will take a look at my BE and see what the indexes are and change them like you adived :)

Cheers
if you are making lots of code changes then also consider decompile- recompile ( not the same as compact - repair

hmm you got me again :oops:
what is that?? oops
 

strive4peace

AWF VIP
Local time
Today, 16:01
Joined
Apr 3, 2020
Messages
921
hi @silentwolf

thanks for the log that vbWatchdog made (@petertheme). Perhaps you didn't unzip it before running?

Here is alternate code you can run to document your data structure:

http://msaccessgurus.com/VBA/Code/Document_Tables2Excel.htm

This VBA on this page runs in Access and creates an Excel workbook with your data structure. It doesn't document everything that the Analyzer does, but it gets us enough to be able to advise some good changes if you share its results.

Note -- there are 2 links to other code you also have to put into your project so it will work ... one module is for handling properties and the other enumerates data type.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:01
Joined
Feb 19, 2013
Messages
14,695
Re decompile see this link
Worth doing on a regular basis during development

also make sure you are optimising how your app works

 

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
@crystal, many thanks for you help! I will do that tomorrow as it does get a little late over at my side.
sorry for misspelling your name at my first reply to you!

@CJ_London also many thanks and I will look into it also right in the morning to get my up to date with your suggestions.

Cheers :)
 

isladogs

CID VIP
Local time
Today, 22:01
Joined
Jan 14, 2017
Messages
16,388
In addition to the previous suggestions, make sure you have a persistent connection to your BE.
Otherwise, the Access FE will keep having to reconnect to the BE . . . which is inefficient

To create a persistent connection, use a hidden form connected to a small BE table, ideally with one field & one record
At startup, open the form but keep it hidden. Keep the form open until the app closes

You should notice a difference in performance
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:01
Joined
May 21, 2018
Messages
6,437
When I open a form and want to fix it then the db gets rather slow and does not respond for a little while.
I think we are talking a different issue here. If I understand it is slow when in design view. This should not be related to the connection to the backend. When you are designing a lot it is always good to "freshen" the front end.

1. Do a decompile and recompile of the FE (as mentioned already by CJ_London)
This cleans out any "hanging" code in all objects.
2. Create a brand new FE database.
3. Import all objects from the old FE into the new FE.
4. Add back any references
5. Compile the FE and you can compact repair too
6. Might as well compact the BE if possible (back up first)
7. Relink tables.

When I am designing I do this often especially if it is hanging in design view.
 
Last edited:

silentwolf

Member
Local time
Today, 14:01
Joined
Jun 12, 2009
Messages
363
wow so many good suggestions I am very greatful learning heaps of you guys!!

Just need to read up on all your links and getting familiar with it.

@MajP and @CJ_London yes I think that Decompile thing is what I need to look into as I honestly never heard of it shame on me!!

Many thanks to all!!

But now I guess I should hit the bed :) so I can hold my eyes open tomorrow morning well today morning gg

CU
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:01
Joined
May 21, 2018
Messages
6,437
@silentwolf
When you are developing your BE should be local. In this thread I have the code for relinking databases. This lets you switch between a live and design back end. The demo provides 2 databases in the folder and you can switch between them.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:01
Joined
Feb 28, 2001
Messages
23,152
2. Create a brand new FE database.
3. Import all objects from the old FE into the new FE.
4. Add back any references

It should be noted that doing a Compact & Repair does exactly these three steps in that order as a single command. Granted, one should always make a copy of any DB (FE OR BE OR monolithic) before doing the C&R, just in case something goes west on you.

I don't disagree at all that it is a good idea to sometimes do those 3 steps manually so you can monitor the progress and see what, if anything, HAS gone west on you.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:01
Joined
May 21, 2018
Messages
6,437
It should be noted that doing a Compact & Repair does exactly these three steps in that order as a single command.
From plenty of experience that is not completely correct. It may do some similar things, but it is not the same. I have seen many, many times where C&R does not improve the issues and a clean import does.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:01
Joined
Feb 28, 2001
Messages
23,152
@MajP, the point is that if your C&R will fail, you have so much corruption that you MUST do a clean import. I agree with you that there are times when you need to start from a "virgin" DB file. I just don't think it is ALWAYS required, that's all. But then, I believe in measured responses to other problems, too.
 

Users who are viewing this thread

Top Bottom