Slow performance on linked tables in Access

I was updating a database designed by a client to improve performance. The table has the Subdatasheet Name property set to [Auto] which was one of the issues.

I had a lot of tables to update and used this code from the microsoft site

Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer

On Error GoTo tagError

Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0

For i = 0 To MyDB.TableDefs.Count - 1
    If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
        If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
             MyDB.TableDefs(i).Properties(propName).Value = propVal
             intCount = intCount + 1
        End If
    End If
Next i


If intCount > 0 Then
    MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
End If

Exit Sub

If Err.Number = 3270 Then
    Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
    MyProperty.Type = propType
    MyProperty.Value = propVal
    MyDB.TableDefs(i).Properties.Append MyProperty
    intCount = intCount + 1
    Resume tagFromErrorHandling
    MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets routine."
End If
End Sub
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply