Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127

    Database chronically slow when linked tables not accessible.

    I have a database that has a series of linked tables which reside on another local server. The tables are used to append data from the local server which is periodically deployed to the field for data collection.



    Because of this, the linked tables are not always available on the network, but his slows down my local database a tremendous amount.

    I understand that trying to access these tables will naturally result in a time-out, but none of the locally stored tables function very nicely either. Opening a local table with 500 records can take upwards of 30 seconds, and running a compact and repair takes a couple minutes and does not help with the issues.

    Is there a way around this? Is it a common issue?

    Thanks

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Build a table (i.e. hlpBE_Tables) holding the info on the linked tables (LocalTableName,SourceTableName,ConnectionsString) and link them on demand just before attempting to append your data. And BreakLinks after.
    Code:
    Public Function SET_LINKS() As Boolean
    Dim strPath As String, strTable As String, strLocalName As String, rstTables As Recordset
    
    
    
    
    On Error GoTo SET_LINKS_ERROR
    
    
    SET_LINKS = True
    
    
    Set rstTables = CurrentDb.OpenRecordset("hlpBE_Tables", dbOpenDynaset, dbSeeChanges)
    If rstTables.BOF And rstTables.EOF Then
    SET_LINKS = False
    Exit Function
    End If
    
    
    'loop and link
        Do Until rstTables.EOF
            strPath = rstTables("BE_Path")
            strTable = rstTables("BE_Table_Name")
            strLocalName = rstTables("BE_Table_LocalName")
           
            'when linking you may encounter password protected files
            If Not ObjectExists("TABLE", strLocalName) Then
                If LINK_TABLE(strPath, strTable, strLocalName) Then
                    SET_LINKS = True
                Else
                    SET_LINKS = False
                    GoTo SET_LINKS_EXIT
                End If
            End If
            rstTables.MoveNext
        Loop
    SET_LINKS_EXIT:
    Exit Function
        
    SET_LINKS_ERROR:
    SET_LINKS = False
    Resume SET_LINKS_EXIT
    End Function
    Public Function LINK_TABLE(strPath As String, strTable As String, LOCAL_NAME As String) As Boolean
    On Error GoTo LINK_ERROR
    
    
    Dim tdf As TableDef
    
    
    LINK_TABLE = True
    
    
        Set tdf = CurrentDb.CreateTableDef(LOCAL_NAME)
        tdf.Connect = ";DATABASE=" & strPath
        tdf.SourceTableName = strTable
        CurrentDb.TableDefs.Append tdf
      
       
    LINK_EXIT:
    
    
    Exit Function
    LINK_ERROR:
    LINK_TABLE = False
    Resume LINK_EXIT
    
    
    End Function
    Public Sub BREAK_LINKS()
    On Error Resume Next
    Dim strPath As String, strTable As String, strLocalName As String, rstTables As Recordset
    
    
    
    
    On Error GoTo BREAK_LINKS_ERROR
    
    
    
    
    Set rstTables = CurrentDb.OpenRecordset("hlpBE_Tables", dbOpenDynaset, dbSeeChanges)
    If rstTables.BOF And rstTables.EOF Then
    Exit Sub
    End If
    
    
    'loop and delete
        Do Until rstTables.EOF
            strLocalName = rstTables("BE_Table_LocalName")
           
            If ObjectExists("TABLE", strLocalName) Then
                DoCmd.DeleteObject acTable, strLocalName
            End If
            rstTables.MoveNext
        Loop
    BREAK_LINKS_EXIT:
    Set rstTables = Nothing
    Exit Sub
        
    BREAK_LINKS_ERROR:
    Resume BREAK_LINKS_EXIT
    
    
    
    
    End Sub
    Cheers
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    HI Vlad. Thank you. I think implementing that fix is a fair bit above my technical know-how. Is there I place I can watch or read a 'dummies' guide on how to do this, with specifics?

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    How is the append happening? Do you have a button to run a series of append queries? Can you post the code here. I can upload a small sample with the table and the code needed for the dynamic linking. Once you would import those into your db it would be very easy to implement:
    Code:
    If SET_LINKS =True Then 
            'run your existing append code
            '..........
            BREAK_LINKS 'remove the links
    Else
            Msgbox "Please review the list of external tables",vbcritical,"Error while linking external tables")
    End If
    Not aware of a specific place for a "dummy guide" but a search for Access dynamic linking or similar should give you many usefull results.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    And here is the sample containing everything you need to implement the above. I included a sub that populates the new table with the existing linked tables.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    Quote Originally Posted by Gicu View Post
    How is the append happening? Do you have a button to run a series of append queries? Can you post the code here. I can upload a small sample with the table and the code needed for the dynamic linking. Once you would import those into your db it would be very easy to implement:
    Code:
    If SET_LINKS =True Then 
            'run your existing append code
            '..........
            BREAK_LINKS 'remove the links
    Else
            Msgbox "Please review the list of external tables",vbcritical,"Error while linking external tables")
    End If
    Not aware of a specific place for a "dummy guide" but a search for Access dynamic linking or similar should give you many usefull results.

    Cheers,
    Vlad
    Hi Gicu,

    It is a macro that runs a series of append queries, yes. I just converted it to VBA. Here is the code.

    Code:
    Option Compare Database
    
    '------------------------------------------------------------
    ' UpdateDDHFromLEBackend
    '
    '------------------------------------------------------------
    Function UpdateDDHFromLEBackend()
    On Error GoTo UpdateDDHFromLEBackend_Err
    
    
        DoCmd.OpenQuery "qryDeleteHolesForUpdate", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCollar", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateBLEA", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCARB", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCHLO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCLAY", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateDRQZ", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateDSIL", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateGEOT", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateGREY", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateGRPH", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateHYHE", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateLIMO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateLITH", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdatePLEO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateRADI", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSDST", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSILI", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSINT", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSORI", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSTCA", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSURV", acViewNormal, acEdit
        MsgBox "Complete.", vbOKOnly, ""
    
    
    
    
    UpdateDDHFromLEBackend_Exit:
        Exit Function
    
    
    UpdateDDHFromLEBackend_Err:
        MsgBox Error$
        Resume UpdateDDHFromLEBackend_Exit
    
    
    End Function

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So just plug its name in the If statement:
    Code:
    If SET_LINKS =True Then 
            'run your existing append code
            UpdateDDHFromLEBackend
            BREAK_LINKS 'remove the links
    Else
            Msgbox "Please review the list of external tables",vbcritical,"Error while linking external tables")
    End If
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    Vlad. I'm having some issues here. I converted a macro to VBA, selected it via RunCode, and now it doesn't run at all. Here is a screen shot of what I've got.

    The RunCode function name is the macro that I converted with all my queries. All of this runs from a button push. I feel like it's getting kind of messy and error-prone. What do you recommend?
    Attached Thumbnails Attached Thumbnails Macro.JPG  

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why do you say it doesn't run, have you put a break on the first line of the new function and step through the code? Hard to say without seeing the db, are those update queries (if they are they don't need the extra arguments but the function probably needs a DoCmd.Setwarnings False before at the beginning and a DoCmd.Setwarnings True at the end.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    Vlad,

    Sorry. I'm quite unfamiliar with VBA. It was not working because my RunCode command was referencing the wrong user function. Oops.

    Can you explain exactly how to make this code work with the screenshot and code I provided? Sorry.

  11. #11
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, but which code do you want to make it work. Your own update (converted from macros) or the one I suggested to dynamically link the tables just prior to update?

    Can you show me what you tried? Have you used the sample I've uploaded?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    I would like to incorporate what I have with your code. So when I run the parent macro it runs your code, links the tables, then runs my queries, then finishes your code and unlinks the tables.

    Does that sound like what you were talking about?

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, but did you download the sample, import the table and the module into your db, run the sub to populate the table with the linked tables,.....Review this thread as I think you should be be able to to follow all the steps, post back if you get stuck, but realize that no one can give you a perfect answer without seeing your application..

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    Alright, so I've done the steps to populate the table. When I press the button it runs SET_LINKS() and then my Function(). The code in my function is as follows:

    Code:
    Function UpdateDDHFromLEBackend()
    
    On Error GoTo UpdateDDHFromLEBackend_Err
    
    
    If SET_LINKS = True Then
            'run your existing append code
            
        DoCmd.OpenQuery "qryDeleteHolesForUpdate", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCollar", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateBLEA", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCARB", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCHLO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateCLAY", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateDRQZ", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateDSIL", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateGEOT", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateGREY", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateGRPH", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateHYHE", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateLIMO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateLITH", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdatePLEO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateRADI", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSDST", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSILI", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSINT", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSORI", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSTCA", acViewNormal, acEdit
        DoCmd.OpenQuery "qryUpdateSURV", acViewNormal, acEdit
        MsgBox "Complete.", vbOKOnly, ""
            
            BREAK_LINKS 'remove the links
    Else
            MsgBox "Please review the list of external tables", vbCritical, "Error while linking external tables"
    End If
    
    
    UpdateDDHFromLEBackend_Exit:
        Exit Function
    
    
    UpdateDDHFromLEBackend_Err:
        MsgBox Error$
        Resume UpdateDDHFromLEBackend_Exit
    
    
    End Function
    Not sure if that's correct at all. And not sure when or how to run BREAK_LINKS(). Also, when I try to run this I get the error in the attached.
    Attached Thumbnails Attached Thumbnails CompileError.JPG  

  15. #15
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You seem to have the BREAK_LINKS already, I would just move it before the "Complete" message.
    To fix the error please add DAO. in front of the Database, TableDef and QueryDef (so Dim db as DAO.Database,....) and then Debug\Compile.
    Please let me know how it goes.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Speed Slow with Linked Tables
    By mindbender in forum Database Design
    Replies: 7
    Last Post: 09-05-2018, 10:53 AM
  2. Replies: 1
    Last Post: 01-20-2016, 10:58 AM
  3. Replies: 6
    Last Post: 12-06-2015, 04:47 PM
  4. Replies: 3
    Last Post: 06-29-2012, 08:58 AM
  5. linked tables - slow
    By wowiwi in forum Access
    Replies: 5
    Last Post: 10-01-2011, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums