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

• Microsoft Access versions 2000, 7.0, 97
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

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

RESOLUTION

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.

STATUS

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

MORE INFORMATION

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:

Run-time error 3277
Can't have more than 10 fields in an index.

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.

Additional query words:

Keywords : kbAccess kbDAO350bug kbVBp500 kbVBp600 kbDSupport kbDAO360bug
Version : WINDOWS:2000,5.0,6.0,7.0,97
Platform : WINDOWS
Issue type : kbbug