Slow DB (1 Viewer)

[Gismo]

Hi,

My DB is becoming very slow in response when opening and closing forms
I have linked files to other back ends
the data transfer for lookup in other back ends seems to be at a good speed
it only seems to be when opening and closing forms or moving between form

i made a copy of the DB, removed the forms, queries using the linked tables and also removed the linked tables, no difference in the speed of remaining forms
I made all the linked tables local tables, no increase in performance

update queries run at a good speed

when i am in a form and select data from a drop down box which looks up data in a external linked file, the speed seems to be adequate
I ran a compact and repair on the back and front end

any suggestions please?

 

[ypma]

Gismo, as a user not a professional , I would look at the current and load events and eliminate them as a starter .I am sure others will be able to give you more suggestions .

Regards Ypma
After thought check that any indexed are still in place for and client number etc

 

[bob fitz]

Hi,

My DB is becoming very slow in response when opening and closing forms
I have linked files to other back ends
the data transfer for lookup in other back ends seems to be at a good speed
it only seems to be when opening and closing forms or moving between form

i made a copy of the DB, removed the forms, queries using the linked tables and also removed the linked tables, no difference in the speed of remaining forms
I made all the linked tables local tables, no increase in performance

update queries run at a good speed

when i am in a form and select data from a drop down box which looks up data in a external linked file, the speed seems to be adequate
I ran a compact and repair on the back and front end

any suggestions please?

Do the forms, that are slow to open, have sub forms and/or list/combo boxes on them.

 

[Gismo]

Do the forms, that are slow to open, have sub forms and/or list/combo boxes on them.

not all the slow form has sub forms but there are a few and many has combo boxes
my first for to open is a single form with no drop down's and is quite slow to open and close
this form was faster before but became very slow to open and close as development went along

i am not sure if this could be a DB size issue, a DB back end link issue or front end issue

when i open any of the files on the back end and run queries from that, it opens in a flash, so I almost want to say my back end file is not the issue but i could be wrong in assuming this

 

[bob fitz]

I seem to remember having a problem in the past with some forms that were slow to open.

I think they were quicker if the Record Source property of subforms and the Row Source property of list/combos were not assigned until the OnLoad or the On Open event of the Main form ran. Might be worth a try.

 

[Pat Hartman]

When you made the tables local, was the FE on the server or on your local drive?

 

[Gismo]

When you made the tables local, was the FE on the server or on your local drive?

both where on the server, this is where it was created as well

even when I run the DB on remote Desktop, it is slow
on remote desktop, i make a copy of the file, that runs slow as well
but only seems to be slow between forms
when working with data, it seems to be fine

 

[CJ_London]

there are some tips in this link which might apply to you

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

 

[Gismo]

there are some tips in this link which might apply to you

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

thank you, i have already looked at most of the items in the list
my data transfer between fe, back end seems to be just fine

a linked table with approximately 60 000 records opens in a few seconds
it is just the time between forms that is my concern, even going from form view to design view takes a very long time

 

[CJ_London]

a linked table with approximately 60 000 records opens in a few seconds

it might open, but is it fully populated? It won't be fully populated until the recordcount is populated at the bottom of the table.

Your forms/subforms won't appear to be open until the whole recordset is populated. So if your forms are based on tables they will take a similar amount of time - and subforms open before the main form. Also be aware that the where parameter of docmd.openform is actually a filter - so the whole recordset will be loaded, it is just the records specified by the where parameter will be displayed.

I presume your tables are properly indexed?

For fast performance, all my performance critical forms have a default recordsource which returns zero records e.g. something like

SELECT * FROM myTable WHERE False

I pass the where parameter of docmd.openform as an openarg parameter instead

in the form load event I have some code something like

me.recordsource=replace(me.recordsource,"False",me.openargs)

(edit: you could still leave the where parameter as is and use

me.recordsource=replace(me.recordsource,"False",me.filter)

but I prefer to keep the two things separate)

in the form current event, I then update subform recordsources if required with a similar method

with me.subformname.form
    .recordsource =replace(.recordsource,"False","FK=" & me.PK)
end with

You can leave the linkchild/master properties in place so if new subform records are created, the FK will automatically be populated

you can apply the same principle to combo/listboxes which contain large datasets

edit 2: have you tried decompiling your code and then compact/repair - see post #4 of this link

CopyFromRecordset method slow on some pcs

Hi folks, I have written a VBA procedure (in Excel VBA) to open an access database using the DAO opendatabase method, and then loop through a list of SQL queries, using .copyfromrecordset to copy the query results onto separate tabs in the Excel workbook. This works great on my pc, and, to...

www.access-programmers.co.uk

 

[Gismo]

it might open, but is it fully populated? It won't be fully populated until the recordcount is populated at the bottom of the table.

Your forms/subforms won't appear to be open until the whole recordset is populated. So if your forms are based on tables they will take a similar amount of time - and subforms open before the main form. Also be aware that the where parameter of docmd.openform is actually a filter - so the whole recordset will be loaded, it is just the records specified by the where parameter will be displayed.

I presume your tables are properly indexed?

For fast performance, all my performance critical forms have a default recordsource which returns zero records e.g. something like

SELECT * FROM myTable WHERE False

I pass the where parameter of docmd.openform as an openarg parameter instead

in the form load event I have some code something like

me.recordsource=replace(me.recordsource,"False",me.openargs)

(edit: you could still leave the where parameter as is and use

me.recordsource=replace(me.recordsource,"False",me.filter)

but I prefer to keep the two things separate)

in the form current event, I then update subform recordsources if required with a similar method

with me.subformname.form
    .recordsource =replace(.recordsource,"False","FK=" & me.PK)
end with

You can leave the linkchild/master properties in place so if new subform records are created, the FK will automatically be populated

you can apply the same principle to combo/listboxes which contain large datasets

will give it a try

i just created a new form, based on a local table with 200 records
the form takes very long to switch from form view to design view

i also had the table open, just closing the table to a long time

i feel there is something else i am missing here and that it is not my forms, queries or tables

 

[CJ_London]

I edited my post to suggest decompile and compact/repair - something else to try

 

[Gismo]

I edited my post to suggest decompile and compact/repair - something else to try

i tried decompiling but i am nut sure how to do it
i searched on the web for instructions but the command line seems to be incorrect

i have now created a new blank DB and imported al the objects
it seems to be hanging with my module to open outlook

could one module affect the entire DB to be slow?

 

[CJ_London]

i tried decompiling but i am nut sure how to do it

per the link I provided, you will need to change then path to whatever version of access you are using
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"

so you need to find your msaccess.exe file to get the correct path

importing will simply import as is so will not decompile, but will effectively be the same as compact/repair

if you want to decompile manually, you would need to do the following

for each module

1. highlight all the code and copy
2. paste into notepad
3. copy the code now in notepad (this will get rid of all the hidden rubbish)
4. highlight or delete the code in the module
5. paste the code copied from notepad in 3 above

once done, compact/repair

could one module affect the entire DB to be slow?

only if whatever is in the module is being called

 

[ypma]

Gismo: I use a vbs script to decompile and will attach , You will have to edit the script and enter your own database name .

WSHShell.Run Chr(34) & MSAccPath & "MSACCESS.EXE" & Chr(34) & " " & Chr(34) & currentDirectory & "\YourDBname.accdb" & Chr(34) & " /decompile"

Hopfully it will work for you

Regards Ypma

 

[Gismo]

per the link I provided, you will need to change then path to whatever version of access you are using
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"

so you need to find your msaccess.exe file to get the correct path

importing will simply import as is so will not decompile, but will effectively be the same as compact/repair

if you want to decompile manually, you would need to do the following

for each module

1. highlight all the code and copy
2. paste into notepad
3. copy the code now in notepad (this will get rid of all the hidden rubbish)
4. highlight or delete the code in the module
5. paste the code copied from notepad in 3 above

once done, compact/repair


only if whatever is in the module is being called

yes, found access in Office 16
i did the compile and repair
still no luck

 

[Pat Hartman]

both where on the server, this is where it was created as well

This may be the problem. The FE should not be on the server, it should be on the local PC. Each user should have his own personal copy. If you don't know how to do this, we can point to relevant threads if you can't find them.

copy the monolithic db to your local pc. Is it still slow? If the answer is yes, then the problem is with the application design. If the answer is no, the problem is with your LAN.

 

[Gismo]

This may be the problem. The FE should not be on the server, it should be on the local PC. Each user should have his own personal copy. If you don't know how to do this, we can point to relevant threads if you can't find them.

copy the monolithic db to your local pc. Is it still slow? If the answer is yes, then the problem is with the application design. If the answer is no, the problem is with your LAN.

each user does have his own copy of the FE
i only use my copy on the server and has worked perfect up to now, and i work on remote desktop, as if it was local
i never had this problem with lag between forms as i am constantly busy with develompent

the users that has a copy on their local pc is also experiencing this lag

when i copy the FE to my local pc, as before, data selection is super fast, the transition between forms and from form view to form design is very slow

my menu screen, has no record source at all, this form takes very long from design to view

 

[Gismo]

I have now come to a point where almost all the forms are non responsive

 

[Minty]

Do you have a timer event on any forms that might be open?