make table queries

[daz321]

I have a make table query and would like to know if i can add a new field to act as a key (sequential numbers would do)?

 

[jdegeorge]

Don't know how to do this, but here's a link to an article on the MVP Access site that shows you how to add an autonumber field to an existing table:

http://www.mvps.org/access/tables/tbl0016.htm

Hope this helps.

Jim "Get it right the first time, that's the main thing..."

 

[raskew]

Here's an example that you can modify to meet your needs. It creates a table and adds an autonumber key field:

Function TableMake(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
'Dim prp As Property


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 = &quot;CREATE TABLE &quot; & tName & &quot;(ObjectID counter, Type TEXT (55), &quot; _
       & &quot;FileName TEXT (55), Selected YESNO);&quot;
db.Execute strSQL

Set td = db.TableDefs(tName)

' Add autonumber field
 Set fld = td.Fields(&quot;ObjectID&quot;)
 fld.Attributes = dbAutoIncrField
 db.TableDefs.Append td
 db.TableDefs.Refresh


' Make ObjectID the key field
db.Execute &quot;CREATE INDEX NewIndex ON &quot; & tName & &quot; (ObjectID) WITH PRIMARY;&quot;

End Function