UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Speed Help!, Access 2010    
 
   
Brepea
post Sep 20 2017, 04:19 AM
Post#21



Posts: 515
Joined: 11-January 09
From: UK


Ok - so I have ruled out the potential issue of "persistent connection". I added a form to my FE (record source was a table to BE with two fields and two rows of data).

Same issue - one user - it works fine - anymore than 1 and it is sssssssoooooooooooo slow!!! I'm out of ideas.
Go to the top of the page
 
Brepea
post Sep 20 2017, 07:13 AM
Post#22



Posts: 515
Joined: 11-January 09
From: UK


I have a textbox on the home form which has a control source of: =DCount("*","q_ActionByStaffCnt") - which simply counts actions by staffID (found on home form - which the q_ActionByStaffCnt uses as criteria to find results). I have x3 of these textboxes on home form. Could this be causing the lag or slowness - should i be setting these textbox values via VBA on event handlers or are they ok via "Control Source" as they are?

I also have 3 listboxes (visible on load = false) - and they're bound to queries. Is it better to set these to requery on selection made in a listbox via strSQL = "Select * from, etc." or to leave as bound to queries?


Is it okay to have linked excel files in a FE (these excel link files are never used for FE at all - just for me as DEV) - these excel files link to C:Drive\.xlsx, etc. - and the path would most definitely not be on a user's local pc - would this be ok to have in the .accde? I have been deleting these thinking that this caused the delay - but naturally going through my queries the fields in queries show as Expr:1, Expr2, and so on (naturally because i got rid of the excel file linked tables)...


I'm really struggling in working this out...
Go to the top of the page
 
DanielPineault
post Sep 20 2017, 08:37 AM
Post#23


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



QUOTE
I have a textbox on the home form which has a control source of: =DCount("*","q_ActionByStaffCnt") - which simply counts actions by staffID (found on home form - which the q_ActionByStaffCnt uses as criteria to find results). I have x3 of these textboxes on home form. Could this be causing the lag or slowness - should i be setting these textbox values via VBA on event handlers or are they ok via "Control Source" as they are?

I also have 3 listboxes (visible on load = false) - and they're bound to queries. Is it better to set these to requery on selection made in a listbox via strSQL = "Select * from, etc." or to leave as bound to queries?

All of this contributes to the load time. If you don't use them, then get rid of them. If they are used, made visible under certain circumstances, then leave them, but you could make them have no RowSource and only populate it through code in real-time.

QUOTE
Is it okay to have linked excel files in a FE (these excel link files are never used for FE at all - just for me as DEV) - these excel files link to C:Drive\.xlsx, etc. - and the path would most definitely not be on a user's local pc - would this be ok to have in the .accde? I have been deleting these thinking that this caused the delay - but naturally going through my queries the fields in queries show as Expr:1, Expr2, and so on (naturally because i got rid of the excel file linked tables)...

Yes, I always remove such elements. I also name such elements with a specific Prefix so they are easily identifiable for deletion (amongst other things).

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
Brepea
post Sep 20 2017, 11:28 AM
Post#24



Posts: 515
Joined: 11-January 09
From: UK


What i notice on one particular form (which is just a search form [has one list box] - 12 columns - the listbox is unbound, and when user selects one of 9 cboboxes it requeries the listbox to a query; the query looks like this:

First Query to build what i need:
Attached File  query.png ( 438.53K )Number of downloads: 3


Then on the after update event of any of the searchable fields i just created another query looking to first query as follows:
Attached File  query2.png ( 8.03K )Number of downloads: 0


Anything stand out as something that cause these delay issues?
Go to the top of the page
 
Brepea
post Sep 20 2017, 11:54 AM
Post#25



Posts: 515
Joined: 11-January 09
From: UK


I have this module on-open event (which calls the UserNameGet() & MachineNameGet() to establish userName and Machine name - could any of this be causing an issue?

CODE
Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function UserNameGet() As String
' Outputs network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
   On Error GoTo UserNameGet_Error

    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    
    If lngX <> 0 Then
        UserNameGet = Left$(strUserName, lngLen - 1)
    Else
        UserNameGet = ""
    End If
  
   On Error GoTo 0
   Exit Function

UserNameGet_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UserNameGet of Module GetUserAndMachineName"
    
End Function

Function MachineNameGet() As String

'Outputs computername, which is useful to retain
    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
   On Error GoTo MachineNameGet_Error

    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        MachineNameGet = Left$(strCompName, lngLen)
    Else
        MachineNameGet = ""
    End If

   On Error GoTo 0
   Exit Function

MachineNameGet_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MachineNameGet of Module GetUserAndMachineName"
End Function
Go to the top of the page
 
DanielPineault
post Sep 20 2017, 12:17 PM
Post#26


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Very unlikely, this is all common code that most dbs have.

What happens if you create a new simple db (1 table, 1 form) and try that out. Do you get the same slow down?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
Brepea
post Sep 20 2017, 12:48 PM
Post#27



Posts: 515
Joined: 11-January 09
From: UK


No - no slow down.
Go to the top of the page
 
GroverParkGeorge
post Sep 20 2017, 01:26 PM
Post#28


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

My assessment is that the problem has to do with "heavy" forms, i.e. forms with several combo or list boxes and/or subforms. Loading those slows things down across a network in many cases, and it certainly sounds like that might be the problem here. I've seen that more commonly with a SQL Server back end, so it may not be the main problem here.

What I would do next is strip out all of those controls from a test version of the form(s) involved. See if that helps at all. Add them back to the form, one at a time and see if there is a cumulative impact.

If so, then you could try what is sometimes referred to as "lazy loading". That means you open the main form with no row sources in combo and list boxes or recordsources in subforms. Then, when needed populate those controls with the minimum needed recordsets to show what you want. This is also important at the form level.

It's been a sort of "classic Access" approach to load a form bound to a table or a query with the entire recordset in it and then apply a filter to that form, or navigate to one record. While that works fine locally, it can be a bottleneck over a network.

I like to load forms with no records at all. For example: "SELECT PrimaryKey, FieldOne FROM tblTestTable WHERE 1=0" When the user wants to look at a single record, dynamically rewrite the SQL in the query accordingly:
"SELECT PrimaryKey, FieldOne FROM tblTestTable WHERE PrimaryKey= 1234 " Another approach is to filter the query on a combo box in the form's header so it only loads the one record corresponding to the selection in that combo box.

With list boxes, other combo boxes and subforms, you can either use the same technique, or not give them a recordset at all until the main form has selected ONE record.

As noted, I usually see the biggest benefit from Access/remotely hosted SQL Server architecture. Your network, though, might make it worthwhile to pursue this approach.

That's a somewhat high level overview, so let me know if that isn't clear.

--------------------
Go to the top of the page
 
Brepea
post Sep 20 2017, 01:53 PM
Post#29



Posts: 515
Joined: 11-January 09
From: UK


I just find it strange as we're only talking about 100 rows of data on load. It works fine for one user on the network, but as stated...the very second someone else tries to open the main form (so execute autoexec)...it even takes forever to load. I'm very confused, stressed beyond words and not sure what to do. It can't be that the application is corrupt as it is perfect on local copy...i could understand the slowness if there was anything more than 1000 rows...but my goodness, I've not witnessed this in Access.
Go to the top of the page
 
Brepea
post Sep 20 2017, 03:32 PM
Post#30



Posts: 515
Joined: 11-January 09
From: UK


On said form - my search form - when a user single-clicks a row in the listbox you see for about 3-5 seconds "calculating". There is absolutely no 'on-click event nor any on-current event handlers. Also when scrolling with the mouse up and down on listbox - it seems to requery (so the values disappear for a few seconds then appear again - flash)? Almost like some kind of event it taking place...

Anyone experienced this before as this may be causing my delays...? Or is this just a graphics issue ....because for a measly 80 records - this should not be happening - surely not?

Edited: Note that when scrolling you don't see the "calculating..." - it's only when you single-click the listbox?
Go to the top of the page
 
Brepea
post Sep 20 2017, 03:43 PM
Post#31



Posts: 515
Joined: 11-January 09
From: UK


I did a built-in tool analyzer on the main home form and it showed this - anyone know what setting it's talking about?

Attached File  Analyzer.png ( 22.45K )Number of downloads: 0


This is the Control Source of textbox i think it is referring to: =DCount("*","q_ActionsLiveByStaffCnt") - the query did have too many fields which were not required in the results - so took that right down from 15 columns to 2 (id and status columns) - the ID column's criteria in query is doing forms!f_home.form!staffid; it returns 2 rows? Should I rather do the count from the control source: =DCount("*","q_ActionsLiveByStaffCnt", "[StaffID] =" & forms!f_home.form!staffid)?
Go to the top of the page
 
AlbertKallal
post Sep 20 2017, 05:37 PM
Post#32


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Runtime switch not going to effect this performance issue. (so no issue or problem with /runtime switch - it is a good choice and option).

As noted, MOST key here is you stating that the application runs just fine on any workstation with ONE user.

This “classic” slowdown issue 9 out of 10 times is solved by a persistent connection. You “seem” to have a persistent connection, but I would as noted ensure this is the case. As noted, a super quick test is to simply launch the application, open any linked table to the back end, minimize that table, and now run you main form and try the application.

I can’t recall a case in which all workstations run fine with one user, but the second user slows everything down – this type of “behaviour” near always has been fixed by a persistent connection.

I would assume you are distributing a compiled accDE to each workstation? If not, then try a compiled accDE on each workstation.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
DanielPineault
post Sep 20 2017, 07:08 PM
Post#33


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Also, are the PCs all up-to-date? Fully patched?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
GroverParkGeorge
post Sep 20 2017, 08:21 PM
Post#34


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


What anti-virus software does your organization use? Can you disable it for a few moments to see if it might be interfering?

--------------------
Go to the top of the page
 
DanielPineault
post Sep 20 2017, 09:06 PM
Post#35


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



@George - Good idea! That is often overlooked and certainly worth checking. hat_tip.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
Brepea
post Sep 21 2017, 12:36 AM
Post#36



Posts: 515
Joined: 11-January 09
From: UK


Hi thanks all

George: McAfee
Daniel: I've sent an email to the IT dept - re: checking whether service pack 1 & 2 are installed (presume this is the same as 'patch')?
Albert: So is me creating a hidden form via autoExec (which lonks to a table in the backend) not the same here? If not- then are you saying to test this i should open the .accdb which is connected to BE (of course) - then open the literal linked table and minimize this; then ask users to try their FEs?).

I don't get the "calculating..." in status bar...is this because it's looking for a BE - there is no event making it calculate anything.
Go to the top of the page
 
Brepea
post Sep 21 2017, 01:27 AM
Post#37



Posts: 515
Joined: 11-January 09
From: UK


Ok - so i managed to work out the "calculating" issue: this was a result of me having a textbox control (it was bound to some calculation working out three scenarios of the team selection) - i now calculate this on the after-update event for each of three cboboxes. Calculation issues now resolved and there is no scrolling delay neither (well in the local pc environment) - yet to be tested on the network.

Although a minor achievement in terms of me working out this speed issue - i still need to try work out the real issue of slow speed....
Go to the top of the page
 
Brepea
post Sep 21 2017, 02:19 AM
Post#38



Posts: 515
Joined: 11-January 09
From: UK


I can't notice a difference when I first load the liked table...so my autoExec loads a startup form (hidden) - looking to table as well - then the table opens in the background and then the main form loads. No difference in this setup over network connection (1 user only (me)).
Go to the top of the page
 
Brepea
post Sep 21 2017, 07:14 AM
Post#39



Posts: 515
Joined: 11-January 09
From: UK


I just thought of checking the properties on the actual Access.exe file as this is what is shows:

Attached File  Access.png ( 76.33K )Number of downloads: 0


All systems here run on Windows 7 - should the compatibility be changed to Windows 7 instead and is there anything out of the ordinary here?

I have just received notification that service pack 1 is installed...
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 12:29 AM