Slow database - only on network folder

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
I have a quite small database put on my companies network shared drives.
The database is split into two files. The connection is set to IPADRESS\FILEPATH.

I have huge problems that the database is very very slow when users works in it.
I have searched for a long time for an answer to this, but not been able to find any solution.

If i take the database offline, reconnect the two files localy on my computer it is lightning fast. But as soon as i put it back on the network drive it is slow again.

I have contacted out companies IT department and asked them. They say that they have tested the network and it is fast and no bottlenecks in transfeering files on the network. They dont really care about my issues so i cant get them to be very motivated about this.

Is there anything i can do?
Running a test on my computer i get 80mbit download and 8mbit upload speed on the internet.
Dont know how to test write/read speed on a network folder.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Did a test with some speed testing tool.
Got this result, dont know what it means... if it is good or bad.
screenshot: http://prntscr.com/n2k44y

But looks like i have 55/83 Mbps
So it should be quite fast for just running a small database.

But how can the database be so slow when on the network folder but when i place the frontend file and backend file on my local drive and connect them (in access) the database is very fast, more or less instant.
 
Upvote 0
to test the read / write network speed: copy a folder (around 500 MB of mixed size files or at least 1 larger file - this should show higher speed) from your computer and paste it to the shared folder where you keep the DB files. Time the process to calculate avg MB/sec upload (write) speed.
Then rename the newly pasted folder, Cut it and paste it back to your computer to calculate download (read) speed.
Usually over older WIFI adapters the difference between Internet speed and Network speed can be significant.


Then it is a matter of optimizing your forms, queries, code, etc. in order to transfer only the necessary amount of information. Sometimes a complicated query can run 10-20 times slower than a query that produces the same result in another way (I have had a case where re-writing a query reduced a simple report loading time from almost 15 minutes to 15 seconds).
I guess you have a Front end and only keep the backend on the network? This would be the right way to do it IMHO if several people may use it at the same time. Properly indexing your tables may also increase query speed.
Forms bound to backend tables may take quite some time to load the entire recordset and even take considerable amount of time to open in design mode.
One thing that may be slowing down the process significantly is creating/deleting a lock file each time the backend data file is opened for access or closed. Some time can be won there by keeping the connection alive. It just needs some careful coding.
 
Upvote 0
What was this tool and what did you test with it?
I suggest you test the speed directly to/from the folder where your backend is located by actually copying files. It is related not to network connection alone but also to other things like for example HDD.
 
Upvote 0
But how can the database work so fast when i take it away from the network folder?
How can the build of the database make it slow when on a network folder but not when it is on my local machine?

I really want to fix this issue since my co-workers complain about it being so slow.
But i cant find anything making it so slow... and it is even harder when the database is fast localy.

I did the manually testing of messuring the network speed and got pretty much the same result.
The name of the software i used is LAN Speed Test
https://totusoft.com/lanspeed
 
Upvote 0
If the tool does what it says then I would say you have good network connection. So at first glance it should not be causing problems.

But there is no general cure for this type of problem.

If you don't have sensitive data you can upload your files somewhere and provide a link for me or anyone with the time and ability to test your db on different networks and try to find any problems that may be causing extra delays.
 
Upvote 0
Here is a code I found, modified and used for a particular db over VPN with bandwidth limitations.
May need adjustments (or not). Try and see if it helps. What it does is it creates a static array to keep backend files open - basically to keep the connection alive. The catch is that if you get runtime error and reset the environment the array is also reset and the connection is closed - so it has to be reopened.
This goes in a standard code module (you may need to read some of the comments as well).
Code:
Option Compare Database
Option Explicit


'Significantly Improve the Performance of Microsoft Access Databases with Linked Tables
'Provided by: Luke Chung, FMS President


'A Split Microsoft Access Database Architecture Offers Many Advantages
'A split database architecture is best for developing, maintaining, and deploying Microsoft Access applications.
'The front-end database contains all the Access objects except the tables which reside in a separate back-end Access Jet database.
'The front-end database links to the tables in the back-end database, so it can be updated without worrying about changes to the data.
'
'Automate Microsoft Access Application DeploymentsThis is particularly important for multi-user applications where each user
'has a copy of the front-end database on their machine sharing the same centralized data.
'When the application is enhanced (new queries, forms, reports, code, etc.), it is simply distributed to each user.
'Programs like our Total Access Startup can centralize and automate the distribution process.
'If you are not familiar with a split database architecture, read our paper on Splitting Microsoft Access Databases
'to Improve Performance and Simplify Maintainability
'
'Microsoft Access Database with Linked Tables Sometimes Perform Poorly
'When a single database is converted to a split-database design, one sometimes sees significant performance degradation,
'especially over a network. Speed may vary with different portions of the application and number of users.
'Some people settle for this but there may be a simple way to significantly improve performance.
'
'Microsoft Access Lock Files
'When a database is opened, Microsoft Access creates a lock file on disk.
'You may see these are *.LDB or *.LACCDB files. When the database is closed, the lock file is deleted.
'This is not a big deal for a single MS Access database application which would create the lock file when the database is
'opened and maintain it until the database is closed. But in a linked database design, the lock file on the back-end database
'may be created and deleted every time a table is opened and closed.
'When no connections to any tables on the back end database remain open, the lock file is deleted. That takes time.
'Always Keep a Connection Open to the Back End Database While Your Application Runs
'You can significantly improve the performance of your Access database by maintaining an open connection to the back-end database
'throughout the time your front-end database is opened.
'By forcing Access to keep the linked table's database open, Access avoids creating a new lock on the backend database every time
'one of its tables is used. This lets you open tables, forms, and reports much faster.
'Over a network, you'll usually see a substantial improvement with how quickly a form opens when it's based on a linked table.


'The DAO OpenDatabase Method
'To create a persistent connection to the linked database, open a MS Access database variable in VBA using the DAO OpenDatabase method.
'Keep this variable open as long as your application is running.


'Procedure Code
'The procedure below supports multiple backend databases. Edit the section with the list of databases to match your backend database(s):


Function OpenAllDatabases(Optional pfInit As Boolean = True, Optional CheckStatusOnly As Boolean = False) 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
    Dim BElist As String
    ' List of databases kept in a static array so we can close them later
    Static dbsOpen() As DAO.Database
    
CheckIfOpened:
    If CheckStatusOnly Then
        On Error Resume Next
        If UBound(dbsOpen) < 0 Then
            OpenAllDatabases = False
        Else
            Err.Clear
            For X = LBound(dbsOpen) To UBound(dbsOpen)
                strName = dbsOpen(X).Name
            Next X
            If Err.Number > 0 Then
                Err.Clear
                OpenAllDatabases = False
            Else
                OpenAllDatabases = True
            End If
        End If
        Exit Function
    End If
    'Getting a list with remote Back-end databases
    BElist = ListBESources(True)
    If BElist = "" Then Exit Function
    If Right(BElist, 1) = ";" Then BElist = Left(BElist, Len(BElist) - 1)


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


    OpenAllDatabases = True 'IN CASE OF ERROR WILL BECOME FALSE
    


OpenDatabases:
    If pfInit Then
        Debug.Print "Opening all Databases ...",
        ReDim dbsOpen(LBound(Split(BElist, ";")) To UBound(Split(BElist, ";")))
        On Error Resume Next
        For X = LBound(dbsOpen) To UBound(dbsOpen)
            strName = Split(BElist, ";")(X)
            Debug.Print strName
            If strName <> "" Then
                If IsFile(strName) Then
                    Set dbsOpen(X) = OpenDatabase(strName, False, False, "MS Access;PWD=" & "")
                    If Err.Number > 0 Then OpenAllDatabases = False
                Else
                    OpenAllDatabases = False
                End If
            End If
        Next X
    Else
ClosingDatabases:
        Debug.Print "Closing Databases ...",
        On Error Resume Next
        For X = LBound(dbsOpen) To UBound(dbsOpen)
            Err.Clear
            Set dbsOpen(X) = Nothing
            If Err.Number > 0 Then OpenAllDatabases = False
        Next X
    End If
Debug.Print "DONE."
End Function


'Invoking the Procedure
'
'Call this when your application starts:
'---------------------------------------------------------------------------
'    OpenAllDatabases True
'
'When you finish, call this to close the database variables/handles:
'
'    OpenAllDatabases False
'---------------------------------------------------------------------------
'For instance, if you have a form that controls the application and remains open during the entire time the user is using your database, add the code to the OnOpen and OnClose events.
'
'This simple technique yields considerable performance gains.




'---------------------------------------------------------------------------
'HELPER FUNCTIONS
'---------------------------------------------------------------------------
Function ListBESources(Optional RemoteOnly As Boolean = False) As String
''List Back-End Data Sources
Dim collTables As New Collection


On Error Resume Next
    Dim db              As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim strCon          As String
    Dim strBackEnd      As String
    Dim BE


    Set db = CurrentDb


    'Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
        'Ensure the table is a linked table.
        If Len(tdf.Connect) > 0 Then
'        If Left$(tdf.Connect, 10) = ";DATABASE=" Then
            'Get the path/filename of the linked back-end
            strBackEnd = Split(tdf.Connect, ";DATABASE=")(1)
'            strBackEnd = Mid(tdf.Connect, 11)
            'Ensure we have a valid string to add to our collection
            If Len(strBackEnd & "") > 0 Then
                collTables.Add Item:=strBackEnd, Key:=strBackEnd
            End If
        End If
    Next tdf


On Error GoTo 0
    Debug.Print collTables.Count & " Data Source(s) found:"
    For Each BE In collTables
        Select Case RemoteOnly
            Case True
                If b42_StripFolderFromPath(BE, "folder") <> CurrentProject.Path & "\" Then _
                ListBESources = ListBESources & BE & ";"
            Case False
                ListBESources = BE & ";" & ListBESources
        End Select
'        ReturnUserRoster (BE)
'        If PRINT_DEBUG_INFO And (Not isDBRT) Then Debug.Print ListBESources
    Next BE
    Set db = Nothing
End Function






Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
    On Error Resume Next
    IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
    'If PRINT_DEBUG_INFO And (Not isDBRT) Then Debug.Print vbDirectory, GetAttr(fName)
    On Error GoTo 0
End Function




Function b42_StripFolderFromPath(ByVal strFullPath As String, returnPart As String) As String
'Separates the path and filename in a full path
'the returnPart argument specifies the result you want:
'returnPart = "Folder"  : will return the path to the file w/o the filename (w/o a backslash at the end)
'returnPart = "File"    : will return the filename only with the extension
'however the argument will be disregarded if a backslash is not found in the string or is the last symbol


    Dim SlashPos
    SlashPos = InStr(1, StrReverse(strFullPath), "\")
    If SlashPos = 0 Then b42_StripFolderFromPath = strFullPath
    If SlashPos = 1 Then b42_StripFolderFromPath = Left(strFullPath, Len(strFullPath) - 1)
    If SlashPos > 1 And returnPart = "Folder" Then _
        b42_StripFolderFromPath = Left(strFullPath, Len(strFullPath) - SlashPos)
    If SlashPos > 1 And returnPart = "File" Then _
        b42_StripFolderFromPath = Right(strFullPath, SlashPos - 1)
End Function
 
Upvote 0
Thank you, ill gladly try this.
But i am confused on how to use this code, dont understand the instructions.

1. I copy the whole code into a new module in my FrontEnd file.
2. I then need to put my backend path and filename somewhere, i cant find where to put this

filename: PB_BackEnd
path: \\192.168.11.21\Gem_Exp\ent\Personal\Lista\Filer\BACKEND\

Dont i need to add some "on startup" code?
 
Upvote 0
just out of curiousity it sounds like you are connecting to your companies network from outside (i.e., home, or another office maybe). Is that the case?
 
Upvote 0
No i am located at my company.
I use the ipadress to make sure all that open the file is connected to the backend with the correct filepath.
I am connected with network cable at my work.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top