Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12

    Unanswered: How to change a field's index

    First: Pradon in my english
    In my ms-access database that it have a field that accept uniqe value.
    How to change the field to accept all value (none uniqe)
    Help me please.Thank's

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    You may want to check out the "Limit To List" Property.
    Me.Geek = True

  3. #3
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    Oh! I mean that I want to Change index from No duplicates to duplicates allowed by Visaul basic command.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Just so I'm clear, you want to change a field's, in a table, property?
    Me.Geek = True

  5. #5
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    That's alright. I want to change the index of a specific field (or specific collumn) in a table of Microsoft Access database to accept duplicated value (duplicate ok) from vb6 programe
    Last edited by polngpole; 08-11-06 at 18:09.

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by polngpole
    Oh! I mean that I want to Change index from No duplicates to duplicates allowed by Visaul basic command.
    Delete the index with:

    DROP INDEX myIndex ON myTable;

    And create a new index without the unique option with:

    CREATE INDEX myIndex ON myTable (myIndexField);

    If you want to run SQL in VBA then use:

    DOCMD.RUNSQL "mySQLexpression"

    hth
    chris

  7. #7
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    Thank's for all reply(for my newbe). Especially "howey"

  8. #8
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    Thank's for all reply (for my newbe). Especially "howey"

  9. #9
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think to drop an index, one would need to know which.

    I will usually think that if you need to do design changes at runtime, then there might be flaws in the design.

    If you're doing this from VB6, then perhaps a forum dedicated to that might be more relevant?

    The below might perhaps be a start, though there's no errorhandling, both table name and column name must be passed ...
    Code:
    Public Sub ChangeIndex(ByVal v_strTable As String, _
                           ByVal v_strColumn As String)
        
        Dim rs                  As ADODB.Recordset
        Dim cn                  As ADODB.Connection
        Dim lngCount            As Long
        Dim strIdx              As String
        
        ' using a connection to current database
        ' from VB6 you'll need to open a connection
        ' with a valid connection string
        Set cn = CurrentProject.Connection
            
        ' retrieving the indices for the passed
        ' table name
        Set rs = cn.OpenSchema(adSchemaIndexes, _
            Array(Empty, Empty, Empty, Empty, v_strTable))
        
        ' looping indices, finding index based on passed
        ' column name
        With rs
            Do While Not .EOF
                If ((.Fields("COLUMN_NAME").Value = v_strColumn) And _
                    (.Fields("UNIQUE").Value = True)) Then
                    strIdx = .Fields("INDEX_NAME").Value
                End If
                .MoveNext
            Loop
            .Close
        End With
        
        ' if such index is found, drop and recreate
        ' (howey's code)
        If Len(strIdx) Then
            ' drop existing index
            cn.Execute "DROP INDEX " & strIdx & _
                    " ON [" & v_strTable & "]"
            ' create new index
            cn.Execute "CREATE INDEX " & strIdx & _
                    " ON [" & v_strTable & "] (" & v_strColumn & ")"
        End If
       
    End Sub
    Roy-Vidar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •