Slow Access Split Database on network issue (1 Viewer)

[Privateer]

Thanks, Pat for the tip on SSMA, I just downloaded it and will try it soon. I was wondering if you could elaborate on the FK double index you mentioned. When creating a table, Access will create a duplicate OK index on my FKs, (they all end with ID), and I am happy to let it do that. If you are saying this will cause a double index, which sounds bad, how do I fix this? I do use a lot of DAO record sets and I thank you for the reminder about the see changes option. Really liked the "Makes my conversions trivial instead of traumatic." expression, will remember that one and what it stands for.
As I understand it, our remote people use Citrix for speed and security. Within their Citrix world, there is a personal C:\ drive for each person and a network share to the internal network where they can get a copy of the latest version of the FE and paste it to their local Citrix drive. The network share also gives them access to the BE that everyone else is using, and they just have to link to that. Hope that helps with the other conversations.

 

[Pat Hartman]

how do I fix this?

Turn off the automatic update. This is a crutch for people who don't know enough to build their own indexes as is the auto create of the index for FK's except that you can't turn that "helper" off.

Go through all your tables. If you see a visible index on a FK, delete it.

Here's some code that will capture ALL indexes and load them into a table so you can view them. Make the table ahead of time. Use the .AddNew code to help you to define it. This code is run from a form and the form has a textbox that contains the name of the database that the code will operate on.

Sub Create_tblTableIndexes()

    Dim db As DAO.Database
    Dim ThisDB As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim idxLoop As DAO.Index
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet1 As DAO.Recordset
    Dim Position As Integer
    Dim CountIndexes As Integer
    Dim strDatabase As String

    'strDatabase = "C:\hartman\ImportDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    
    CountIndexes = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
'''    Set QD1 = ThisDB.QueryDefs!QdeltblTableIndexes
'''        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblTableIndexes
    Set TempSet1 = TD1.OpenRecordset

    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
            
'' add error checking for 3024 - not found in collection
'' add error checking for 3110 - no permission to read msysmodules2

        On Error GoTo ErrorHandler
        For Each idxLoop In tblLoop.Indexes
            CountIndexes = CountIndexes + 1
            Forms!frmPrintDoc!txtIndexCount = CountIndexes
            Forms!frmPrintDoc!txtIndexName = tblLoop.Name
            Forms!frmPrintDoc.Repaint
        
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
            Else
                Position = 1
                For Each fldLoop In idxLoop.Fields
                    TempSet1.AddNew
                        TempSet1!IndexName = idxLoop.Name
                        TempSet1!TableName = tblLoop.Name
                        TempSet1!Unique = idxLoop.Unique
                        TempSet1!PrimaryKey = idxLoop.Primary
                        TempSet1!OrdinalPosition = Position
                        TempSet1!FieldName = fldLoop.Name
                    TempSet1.Update
                    Position = Position + 1
                Next fldLoop
            End If
        Next idxLoop
    Next tblLoop

    db.Close
Exit Sub

ErrorHandler:

Select Case Err.Number
    Case 3110
        MsgBox "Open " & strDatabase & " and change the admin security to allow read for MSysModules", vbOKOnly
    Case 3043, 3055
        MsgBox "Please select a valid database. Error #" & Err.Number, vbOKOnly
    Case 91   ' db was not opened so it cannot be closed.
        Exit Sub
    Case 3044
        Resume Next
    Case Else
        MsgBox Err.Number & "-" & Err.Description
End Select
Exit Sub
Resume Next
End Sub

 

[CJ_London]

you can remove all the values in this box - file>options>object designers - that will prevent indexes being created unnecessarily

 

[Pat Hartman]

that will prevent indexes being created unnecessarily

Correct. But it will not remove duplicates that already exist.