Adding autonumber field to table

[CuriousGeorge]

Is there an easy way to insert an autonumber field into a table? Just so every row gets indexed from 1 to n so that i can use that field to sort my queries?

My first idea was to add an extra field in the import specification but i cant find the datatype 'autonumber' there.

ANy help is appreciated,

Pasting my import routine:

Private Sub Command0_Click()
If MsgBox("This will open the folder for imports. Continue?", vbYesNoCancel) = vbYes Then
Debug.Print s
Shell ("C:\Users\SSS787\exjobb\Database\Readlog_update\readlog.exe " & Me.TextPath & "\*.*")
 
 
Dim i As Integer
Dim tblStr As String
Dim varItem As Variant
Dim specname As String
Dim sFile As String
Dim n As Integer
 
i = 1
tblStr = ""
specname = "Import Specs"
n = 0
 
    With Application.FileDialog(msoFileDialogFilePicker)
 
        With .Filters
            .Clear
            .Add "All Files", "*.*"
        End With
 
             .AllowMultiSelect = True
             .InitialFileName = Me.TextPath
             .InitialView = msoFileDialogViewDetails
                    If .Show Then
                      For Each varItem In .SelectedItems
 
                        For i = 1 To Len(varItem)
                          If IsNumeric(Mid(CStr(varItem), i, 1)) Then
                            tblStr = tblStr & Mid(CStr(varItem), i, 1)
                          End If
                        Next i
                        If Right(CStr(varItem), 4) = ".txt" Then
 
                          DoCmd.TransferText acImportDelim, specname, "TableData", CStr(varItem), True
                            i = i + 1
                              DoCmd.OpenTable "TableData", acViewNormal, acReadOnly
                                  DoCmd.Close
                                    tblStr = ""
 
                        End If
 
 
 
                            sFile = ParseFileName(CStr(varItem))
 
                            CurrentDb.Execute "UPDATE TableData SET BananaField = '" & sFile & "'  WHERE BananaField Is Null;"
 
 
                    Next varItem
 
 
                      MsgBox "Data Transferred Successfully!"
                    DoCmd.Close
                    End If
      End With
  End If
 
End Sub

Cheers

 

[Schippi]

Hi George,
in case you work with access you will find in the design view of the tabe under datatype the AutoNumber. this function automatically numbers your data set.

 

[gemma-the-husky]

but an autonumber won't really maintain a sequence. what happens if you add a record that should interleave between two existing records.

if the order of insertion IS relevant, just add a field for date/time inserted and then you will have real useful data, rather than a meaningless number.

There OUGHT to be some other information in your table to enable you to sort it into any order you like.

 

[CuriousGeorge]

thank u very much