Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q243304

↑ Back to the top


Symptoms

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

↑ Back to the top


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.

↑ Back to the top


Status

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

↑ Back to the top


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.

↑ Back to the top


Keywords: KB243304, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 243304
Revision : 5
Created on : 2/12/2007
Published on : 2/12/2007
Exists online : False
Views : 278