Create a table with an index

[WebStar]

Hi,
I have a function that has to delete some fields in an access database. The problem is that the table is created dinamically from code. If I set the "MainWPID" field to be Indexed(Duplicated OK) from MS Acces everything works ok. But I don't know how to create an indexed field from program.
anybody has an ideea?

The table has these fields:
"MainWPID" - string
"ID" - string
"Value" - string
I want the "MainWPID" field to be indexed.
Thank you!

this is the delete function:

Public Function DeleteData(dbPath As String, UserID As String) As Boolean
    Dim rs As Recordset
    Dim db As Database, strTemp As String
    
    On Error GoTo ErrorHandler
    
    Set db = OpenDatabase(dbPath)
    'exist in database?
    Set rs = db.OpenRecordset(UserID, dbOpenTable)
    strTemp = Main.BASEN(36)
    With rs
        .index = "MainWPID"
        .Seek "=", strTemp
        Do While Not .NoMatch
            .Delete
            .Seek "=", Main.BASEN(36)
        Loop
    End With
    GoTo TheEnd
ErrorHandler:
    GoTo TheEnd
TheEnd:
    db.Close
End Function

 

[TonyJollans]

Hi WebStar,

The easiest way is a bit of DDL:

Docmd.runsql "CREATE INDEX MainWPID ON" & UserID & "(MainWPID)"

where UserID (as per your code) is a string containing the Table name.

Enjoy,
Tony