X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Split Database Very Slow, Access 2010    
post Oct 6 2019, 04:02 AM

Posts: 154
Joined: 8-March 16
From: New Zealand

Hello All
I have a DB that has a split BE that many users connect to but for some unknown reason it has become very slow to load.
It takes up to 2 min to load on some pc's, and down in the bottom left corner it just has a notice that it is calculating.
Once it has all loaded it then becomes very fast but it seems to be the initial loading that causes the problems.
I have discovered that if I first open the BE and then open the FE while the BE is still open the database is very fast, almost instantaneous.
The BE is only about 16,000KB, and the FE is about 20,000KB
Does anyone have any ideas that could help me with this problem?
Go to the top of the page
post Oct 6 2019, 04:25 AM

Posts: 1,510
Joined: 2-April 09
From: somewhere out there...

you create a permanent link to the BE when you open your FE.
create a Public Function in a Module and on that function
open any Linked table.

Call the function on Autoexec macro.
Public Function fnkLink()
    Static rs As DAO.Recordset
    set rs = Currentdb.OpenRecordset("LinkedTableNameHere", dbOpenDynaset, dbOpenReadOnly)
End Function

Never stop learning, because life never stops teaching.
Go to the top of the page
post Oct 6 2019, 04:33 AM

Posts: 154
Joined: 8-March 16
From: New Zealand

I have seen something similar to that, and tried it see below code
Sub OpenAllDatabases(pfInit As Boolean)
  ' Open a handle to all databases and keep it open during the entire time the application runs.
  ' Params  : pfInit   TRUE to initialize (call when application starts)
  '                    FALSE to close (call when application ends)
  ' Source  : Total Visual SourceBook

  Dim x As Integer
  Dim strName As String
  Dim strMsg As String

  ' Maximum number of back end databases to link
  Const cintMaxDatabases As Integer = 2

  ' List of databases kept in a static array so we can close them later
  Static dbsOpen() As DAO.Database

  If pfInit Then
    ReDim dbsOpen(1 To cintMaxDatabases)
    For x = 1 To cintMaxDatabases
      ' Specify your back end databases
      Select Case x
        Case 1:
          strName = "\\dhost\dbase\FinanceBE\AssetFinanceBE.accdb"
        Case 2:
          strName = "\\dhost\dbase\GVResidentsBE\GVResidentsBE.accdb"
      End Select
      strMsg = ""

      On Error Resume Next
      Set dbsOpen(x) = OpenDatabase(strName)
      If Err.Number > 0 Then
        strMsg = "Trouble opening database: " & strName & vbCrLf & _
                 "Make sure the drive is available." & vbCrLf & _
                 "Error: " & Err.Description & " (" & Err.Number & ")"
      End If

      On Error GoTo 0
      If strMsg <> "" Then
        MsgBox strMsg
        Exit For
      End If
    Next x
    On Error Resume Next
    For x = 1 To cintMaxDatabases
    Next x
  End If
End Sub
Is this doing the same thing, I had it set to run on opening of first form, but it still didn't really help
Go to the top of the page
post Oct 6 2019, 05:36 AM

UtterAccess VIP
Posts: 1,825
Joined: 4-June 18
From: Somerset, UK

As arnel stated, you should create a persistent connection to a SMALL table in the backend i.e. a table that loads quickly.
You can either do what arnel suggested or open a hidden form which uses an appropriate table as its record source.
The form should be kept open in the background whilst your FE is open.

I haven't studied the code you supplied but I'd suggest its more complicated than you need and offers no additional benefits

Colin (Mendip Data Systems)
Website, email
Go to the top of the page
post Oct 6 2019, 06:37 AM

Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

In addition to the advice already provided, there are several articles at FMS related to MsAccess, good practices and performance.
Here is one regarding improving performance with linked tables.

It appears you have some code from FMS -- I note ' Source : Total Visual SourceBook in your code sample.
This post has been edited by orange999: Oct 6 2019, 06:40 AM

Good luck with your project!
Go to the top of the page
post Oct 6 2019, 08:16 AM

UA Admin
Posts: 36,069
Joined: 20-June 02
From: Newcastle, WA

What happens when this Access Relational Database Application opens? Do you run start-up code -- other than that persistent connection routine? Are you opening a form bound to a table or a query? Are you loading ALL of the records from that recordsource as the first form opens? Or do you open the first form with a filtered recordsource to prevent loading all records?

The fact that it has slowed down over time suggests there is a problem with the number of records being retrieved or manipulated at start up. If the persistent connection doesn't resolve the problem, the next thing to look into, therefore, is that initial start up process.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Oct 7 2019, 05:26 AM

Posts: 154
Joined: 8-March 16
From: New Zealand

Thanks all of you for your comments and time.
I have tried the code above but it doesn't make any difference.
But I am taking note of George's comments.
The opening form is an unbound form but it has tabs on it and these tabs have filtered record sets as sub forms.
But the thing that baffles me is that it is very fast as long as the back end as also open in another session of access on the same PC.
This would point to The persistent connection issue, but there are always bound forms open in the FE so I can't understand this behavior.
Is there a way of only loading or binding the sub form once the tab is selected?
This may minimize the impact on loading, as the opening form has multiple subforms on different tabs.
Go to the top of the page
post Oct 7 2019, 07:03 AM

Posts: 1,510
Joined: 2-April 09
From: somewhere out there...

save all Recordsource of your tabs to a variable, except the first tab, since
this will get the immediate focus when the form opens.
make the subforms Unbound, except again for the subform of the first tab.
on the afterupate event of the tabControl, load the recordset to the appropriate
Option Compare Database
Option Explicit

'* recordsource of page 2 (sample)
const recordsource2 as string = "select * from table1"
const recordsource3 as string = "select * from table2"
'* will be set to true if we already loaded the recordsource of appropriate subform
dim bolLoaded2 as Boolean
dim bolLoaded2 as boolean

private sub TabCtl_afterupdate()
    select case [tabCtl].Value
    case 2
        If not bolLoaded2 then
            bolLoaded2 = True
            me.[subform2].form.recordsource = recordsource2
        End If
    case 3
        If not bolLoaded3 then
            bolLoaded3 = True
            me.[subform3].form.recordsource = recordsource3
        End If
    end select
end sub

Never stop learning, because life never stops teaching.
Go to the top of the page
post Oct 7 2019, 07:04 AM

UtterAccess VIP
Posts: 6,975
Joined: 30-June 11

Is there a way of only loading or binding the sub form once the tab is selected?

See: https://www.databasejournal.com/features/ms...Dynamically.htm

Be sure to use a persistent connection though (every database should have this by default). I personally recommend doing so with a bogus table/form and opening the form in hidden view as I've had issue with using the code approach.

Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://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 ...(you are responsible for your choices and actions)
Go to the top of the page
post Today, 04:37 AM

Posts: 154
Joined: 8-March 16
From: New Zealand

Hello All and thankyou for your comments.
I tried all the above suggestions and none of it worked.
But I thought I should share now that I have found the problem.
I have discovered that the issue is with office 365 updates.
The company I am working with uses probably around 50 pc's with this version of office installed, but there were a few with the older builds on them, and I realized that these machines worked fast.
Also I had noticed that the slow pc's had all slowed down around the same time. At the time I put this down to a DB development issue but couldn't place my finger on it.
So I have rolled back some of the machines to an earlier version using two command lines, which must be run in admin mode.
cd %programfiles%\Common Files\Microsoft Shared\ClickToRun
officec2rclient.exe /update user updatetoversion=16.0.10730.20416

After this access was immediately fast, almost instantaneous to respond.
So although this is not the preferred fix and I would like to know what exactly is the problem, it does fix the issue.
I hope this will be resolved in later office updates, or maybe it is part of the plan to help users eventually wind down access, but I hope not.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th November 2019 - 10:21 AM