I noticed that indexes with funny names like [{21A84A97-5492-4027-83AA-33C67D68D1EA}] appeared in a SQL Server database that was upsized from Access database by Microsoft Upsizing Wizard. Let's have a quick look at how Microsoft Access creates those indexes.
By default there is the option 'AutoIndex on Create/Import' in Tools -> Options
Therefore, if you selected that option Access automatically will add duplicate indexes when:
- You make a field a primary key – it adds a duplicate index to that field
- You make a relationship (and enforce referential integrity) – it adds a duplicate index to the foreign key (assuming it is an ID field). The index is automatically added on the 'many' table by Access when you create a 'one' to 'many' relationship between tables. The index names are automatically generated by Access from the name of the relationship (stored in MSysRelationships). These indexes with funny names are part of relationships in the Access database but they are not part of relationships in SQL Server database.
We recommend removing duplicate indexes.
The function below allows you to find those indexes in Access database.
'-------------------------------------------------------------------------------------
Public Function IndexAccessInfo() As Boolean
'-------------------------------------------------------------------------------------
On Error GoTo err_IndexAccessInfo
Dim dbCurr As dao.Database
Set dbCurr = CurrentDb()
Dim I As Integer
Dim J As Integer
Dim MyTableDef As dao.TableDef
Dim MyIndex As dao.Index
Dim strSQL As String
strSQL = ""SELECT MSysObjects.Name AS TableName FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Name) Not Like 'msys*'));""
Dim rstMain As dao.Recordset
Set rstMain = dbCurr.OpenRecordset(strSQL)
Do While Not rstMain.EOF
Set MyTableDef = dbCurr.TableDefs(rstMain!TableName)
If MyTableDef.Indexes.Count > 0 Then
For I = 0 To MyTableDef.Indexes.Count - 1
Set MyIndex = MyTableDef.Indexes(MyTableDef.Indexes(I).Name)
For J = 0 To MyIndex.Fields.Count - 1
Debug.Print ""rstMain!TableName="" & rstMain!TableName
Debug.Print "" MyTableDef.Indexes(I).Name="" & MyTableDef.Indexes(I).Name
Debug.Print "" MyIndex.Fields(J).Name="" & MyIndex.Fields(J).Name
Next
Set MyIndex = Nothing
Next
End If
Set MyTableDef = Nothing
rstMain.MoveNext
Loop
Set rstMain = Nothing
End Function
If you decide to keep these indexes they should be renamed. This can be done by scripting the database and replacing with standard index names http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_4_IndexesNaming.aspx"