BUG: CREATE INDEX on Attached Table Fails Silently with More than 10 Fields

When run on an attached ODBC table, the CREATE INDEX statement fails silently when more than 10 fields are specified for the index.

Multiple-field indexes are limited to 10 fields in a Microsoft Access database. Do not attempt to create an index with more than 10 fields.

This problem occurs in version DAO 3.5x and DAO 3.6.

When run on an attached ODBC table, the CREATE INDEX statement creates a pseudo index that allows the table to be updateable. When more than 10 fields are specified for the index, the CREATE INDEX statement fails silently. Note that the attached table is read-only without the pseudo index.

If more than 10 fields are specified when trying to create an index on an Access table, the following error occurs as expected:

Steps to Reproduce Behavior

1. Create a Microsoft SQL Server table and insert a few records by running the following script in ISQL/w:

   use pubs
   go
   create table tbl_Indexed
   (fld1 int,
    fld2 int,
    fld3 int,
    fld4 int,
    fld5 int,
    fld6 int,
    fld7 int,
    fld8 int,
    fld9 int,
    fld10 int,
    fld11 int)
   go
   
   insert into tbl_Indexed
   Values(1,2,3,4,5,6,7,8,9,10,11)
   go
   
   insert into tbl_Indexed
   Values(2,3,4,5,6,7,8,9,10,11,1)
   					

2. Using the ODBC Administrator, create a System DSN named Pubs. Choose Pubs as the default database for this DSN.
3. In Microsoft Access 97 (or 2000), create a new database named CreateIndexLinked.mdb.
4. Create a table named Table1 with 11 fields. Name the fields "fld1, fld2, ... fld11."
5. Link the SQL Server tbl_Indexed table to the Access database. Choose Cancel in the Select Unique Record Identifier dialog box.
6. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default.
7. On the Project menu, select References and add a reference to the Microsoft DAO 3.51 (or 3.6) Object Library.
8. Add the following code to the form's Load Event:NOTE: You need to change the path to point to the Microsoft Access database you created in the step #3.

   Private Sub Form_Load()
   
   Dim dbs As Database
   Dim myrs As Recordset
   
       Set dbs = OpenDatabase("C:\temp\CreateIndexLinked.mdb")
   
   'These lines simply drop the index if it exists.
       On Error Resume Next
       dbs.Execute "Drop Index NewIndex on Table1"
       dbs.Execute "Drop Index NewIndex on dbo_tbl_Indexed"
       On Error GoTo 0
   
   'This Execute tries to create a multiple-field index on the Access table.
   'It will generate the 3277 Run-time error as expected.
   '    dbs.Execute "CREATE INDEX NewIndex ON Table1" _
   '        & "(fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8, fld9, fld10, fld11);"
   
   'This Execute tries to create a multiple-field index with more than 10 fields on the attached SQL Server table.
   'It will fail silently.
       dbs.Execute "CREATE INDEX NewIndex ON dbo_tbl_Indexed " _
           & "(fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8, fld9, fld10, fld11);"
   
   'This Execute tries to create a multiple-field index with 10 fields on the attached SQL Server table.
   'It will succeed.
   '    dbs.Execute "CREATE INDEX NewIndex ON dbo_tbl_Indexed " _
   '        & "(fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8, fld9, fld10);"
   
   Set myrs = dbs.OpenRecordset("dbo_tbl_Indexed", dbOpenDynaset,
   dbForwardOnly, dbOptimistic)
   
   'The AddNew will fail, if the Execute that tries to create a multiple-field
   'index with more than 10 fields on the attached SQL Server table is run.
   
   myrs.AddNew
   myrs(0) = 3
   myrs(1) = 4
   myrs(2) = 5
   myrs(3) = 6
   myrs(4) = 7
   myrs(5) = 8
   myrs(6) = 9
   myrs(7) = 10
   myrs(8) = 11
   myrs(9) = 1
   myrs(10) = 2
   myrs.Update
   
       dbs.Close
   
   End Sub
   					

9. Step through the project.