Make table problem

[wh00t]

I have a make table query that works fine, but I need to upsize the database to SQL which requires a unique index field, I do not know how to create a unique index field using a make table query, can anyone help?

 

[Mile-O]

Shouldn't the Unique Index be defined in your table.

Look at the field it should be and Index it with Indexed: Yes (No Duplicates)

 

[raskew]

Here's a little function that illustrates creating a unique index with code.

Function TableMake2(MyTable As String)
Dim db As DATABASE
Dim tName As String
Dim test As String, strSQL As String
Dim td As TableDef, fld As Field
'
Set db = CurrentDb
'
'Trap for any errors.
On Error Resume Next
'
tName = MyTable
'
'Does table MyTable exist?  If so, delete it;
test = db.TableDefs(tName).Name

If Err <> 3265 Then
   docmd.DeleteObject acTable, tName
End If
'
'Create/recreate table
'
strSQL = "CREATE TABLE " & tName & "(MyCounter counter, grpID long, " _
       & "MyDate date);"
db.Execute strSQL
'
Set td = db.TableDefs(tName)
'
' Add autonumber field
 Set fld = td.Fields("MyCounter")
 fld.Attributes = dbAutoIncrField
 db.TableDefs.Append td
 db.TableDefs.Refresh
'
'
' Make MyCounter the key field
db.Execute "CREATE INDEX NewIndex ON " & tName & " (MyCounter) WITH PRIMARY;"
'
End Function

call it from the debug window with:
? tablemake2("myTestTable")