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 http://support.microsoft.com/kb/275085

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
tagFromErrorHandling:
Next i

MyDB.Close

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

Exit Sub

tagError:
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
Else
    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