Create Unique Index

[raskew]

Hi -

I'm getting 'wrapped around the axle' trying to programatically create a unique index, sorted descending.

The following code snipped illustrates the problem. As far as I can tell, the "Create Unique Index" statement is straight out of the Help File. It all processes just fine -- no errors. The sub is called with:
call FillList("tblTemp")

However, the index is not created. The created table has just one field. There's no problems with multiple fields.Can anyone spot where I'm going wrong.

Public Sub FillList(pTable As String)
Dim db As database
Dim rs As Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim strHold As String
Dim n As Integer



    On Error Resume Next
    'WARNING: Table pTable will be deleted,
    '         and recreated.  If this is
    '         a problem, choose another,
    '         unique table name.
    CurrentDb.Execute "DROP TABLE " & pTable & ";"
    strSQL = "CREATE TABLE " & pTable & " " _
           & "( Selection Text(20) Not Null );"
    Debug.Print strSQL
    CurrentDb.Execute strSQL
    CurrentDb.TableDefs.Refresh

   Set db = CurrentDb
   [COLOR="Red"]strSQL = "CREATE UNIQUE INDEX SelColor ON " & pTable & " ([Selection]) DESC;"
   'Debug.Print strSQL
   db.Execute strSQL[/COLOR]....

Thanks in advance -- Bob

 

[pbaldy]

In the interests of full disclosure, I've never needed to do this. Based on my reading of the help file, it looks like DESC should be within the parentheses. See if that does anything.

 

[raskew]

Paul -

Thanks for the response. Your suggestion 'did it' in so much as creating the index. Interestingly, once I'd correctly captured the DESC, found that specifying DESC or ASC apparently made no difference. Using either produces the same result.

Guess the sorting needs to be left for a subsequent query.

Thanks again - Bob

Added:

...found that specifying DESC or ASC apparently made no difference. Using either produces the same result.

Guess the sorting needs to be left for a subsequent query.

My error! I retract that.

Bob

 

[raskew]

Paul -

Check-out http://www.access-programmers.co.uk/forums/showthread.php?t=156597 to see where this all was going.

Appreciate your help.

Bob

 

[pbaldy]

No problem, Bob. You're either up late or up early!