Microsoft KB Archive/243304

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

Article ID: 243304

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q243304



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
  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)  Using the ODBC Administrator, create a System DSN named Pubs. Choose Pubs as the default database for this DSN. In Microsoft Access 97 (or 2000), create a new database named CreateIndexLinked.mdb. Create a table named Table1 with 11 fields. Name the fields "fld1, fld2, ... fld11." Link the SQL Server tbl_Indexed table to the Access database. Choose Cancel in the Select Unique Record Identifier dialog box. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default.</li> On the Project menu, select References and add a reference to the Microsoft DAO 3.51 (or 3.6) Object Library.</li>  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 </li> Step through the project.</li></ol>

Keywords: kbbug kbpending KB243304

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.