Speeding up Front End DB while still developing

[silentwolf]

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]

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]

Are you backing up and compacting the FE regularly?

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

 

[silentwolf]

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]

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]

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

 

[CJ_London]

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]

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]

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]

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
what is that?? oops

 

[strive4peace]

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.

 

[CJ_London]

Re decompile see this link

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

Worth doing on a regular basis during development

also make sure you are optimising how your app works

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

 

[silentwolf]

@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]

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]

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)

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

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.

 

[silentwolf]

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]

@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.

Setting Database default paths

It really wasn't a big deal. I don't expect to be "spoon fed" with this stuff. Sometimes you just have to get in there and sort it! It was the modules that got me. I didn't realise until I got into the code that they were required. Not to worry though. It's a good piece of code.

www.access-programmers.co.uk

 

[The_Doc_Man]

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]

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]

@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.