I just noticed something about the code that may be contributing to the
problem, but unfortunately I'm not in a position to test my theory before
next Tuesday at the earliest.
There may be an error in the function GenerateIndexSQL. This function should
only be necessary if when you created the linked table, you were prompted to
supply a field or fields that uniquely identified each row (in other words,
if the table to which you were linking didn't have a unique index, and you
were asked to supply one in Access). These indexes are necessary in order to
be able to update the linked table. If you didn't get this prompt, or you're
only using the tables as read-only, just comment out the following code in
sub FixConnections:
If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If
If you did get prompted and you want to be able to update the linked tables,
don't comment out that code, but try the following instead.
In the function GenerateIndexSQL, change the line
Dim fldCurr As Field
to
Dim fldCurr As DAO.Field
and add a declaration
Dim idxCurr As DAO.Index
Then, find the part of the code
If tdfCurr.Indexes.Count > 0 Then
' Loop through all of the fields in the index,
' adding them to the SQL statement
strSQL = "CREATE INDEX __UniqueIndex ON " & TableName & " ("
For Each fldCurr In tdfCurr.Indexes("__uniqueindex").Fields
strSQL = strSQL & fldCurr.Name & ", "
Next
' Remove the trailing comma and space
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
End If
and replace it with
If tdfCurr.Indexes.Count > 0 Then
' Make sure there's an index named _UniqueIndex
On Error Resume Next
Set idxCurr = tdfCurr.Indexes("__uniqueindex")
If Err.Number = 0 Then
' Loop through all of the fields in the index,
' adding them to the SQL statement
strSQL = "CREATE INDEX __UniqueIndex ON " & TableName & " ("
For Each fldCurr In idxCurr.Fields
strSQL = strSQL & fldCurr.Name & ", "
Next
' Remove the trailing comma and space
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
End If
End If
If that doesn't solve the problem, go back to the sub FixConnections, and
change
Err_FixConnections:
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
to
Err_FixConnections:
If Err.Number = 3291 Then
MsgBox "Problem creating the Index using" & vbCrLf & _
typNewTables(intLoop).IndexSQL
vbOKOnly + vbCritical, "Fix Connections"
Else
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
End If
Resume End_FixConnections
and let me know the exact details of the error.
