maybe I'm exhausted
...I thought I had gotten this code to work; but I must have been disillusioned;
it certainly does not now
(although it doesn't throw any errors, it does not create the index)
any suggestions would be greatly appreciated
with much thanks in advance,
mark
Code:
' ADD INDEX to the table
'AddIndexToTable(ByVal TblName As String, IndexName As String, IsPrimary As Boolean, IsUnique As Boolean, ParamArray FldNames()) As Boolean
vTableName = "tbeInstallationNotes_EOS" 'table is LINKED, but not at the time it is being updated; it is the same name locally and remote
vIndexName = "InstallationNote_ID"
'is Primary = true
'is Unique = true
vFldName1 = "InstallationNote_ID"
result = AddIndexToTable(vTableName, "InstallationNote_ID", True, True, "InstallationNote_ID")
_____________
Code:
Function AddIndexToTable(ByVal TblName As String, IndexName As String, IsPrimary As Boolean, _
IsUnique As Boolean, ParamArray FldNames()) As Boolean
Dim Idx As Index
Dim Td As TableDef
Dim DbPath As Variant
Dim db As Database
Dim FldNum As Integer
On Error Resume Next
'get back end path of linked table
DbPath = DLookup("Database", "MSysObjects", "Name='" & TblName & "' And Type=6")
If IsNull(DbPath) Then
Set db = CurrentDb 'if local table
Else
Set db = OpenDatabase(DbPath) 'if linked table
If Err <> 0 Then
'failed to open back end database
Exit Function
End If
'in case back end has different table name than front end
TblName = DLookup("ForeignName", "MSysObjects", "Name='" & TblName & "' And Type=6")
End If
'get table
Set Td = db.TableDefs(TblName)
If Err <> 0 Then
'failed to get table
GoTo Done
End If
With Td
On Error Resume Next
Set Idx = .Indexes(IndexName) 'test for existence
If Err = 0 Then GoTo Done
If Err > 0 Then 'create index
On Error Resume Next
Set Idx = .CreateIndex(IndexName)
With Idx
For FldNum = 0 To UBound(FldNames)
.Fields.Append .CreateField(FldNames(FldNum))
Next
.IgnoreNulls = True
.Primary = IsPrimary
.Unique = IsUnique
End With
.Indexes.Append Idx
End If
End With
If Err = 0 Then AddIndexToTable = True
Done:
End Function