Results 1 to 8 of 8
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    A small problem, and I'm not sure there is a solution, but this forum normally puts me right one way or another so here goes.

    I need to be able to ADD a field to a table in code.
    DAO or ADOX it doesn't matter.
    What does matter is that the field added is an AUTONUMBER field.
    Ideallty I would like to do this from a small VB project , but it can run inside Access if needed.

    Is it possible.
    I have tried with DAO and also using ADOX an columns but there appears to be no way to make the column/Field
    an AutoNumber field.

    Any solution gratefully accepted.

    Thanks
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    <post#=147751>post 147751</post#> contains an example of using SQL DDL (data definition language) and of DAO to create an AutoNumber field in code.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    Thanks again Hans - <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Another winner for the Forum.

    You are correct, the SQL method is much better, and once you pointed me to it I found an example in
    the Developers handbook, which also pointed me to the ALTER TABLE and ADD commands.
    Exactly what I needed with the AUTOINCREMENT option,
    and also functional within a DAO queryDef or an ADODB command object.

    I wonder if ADO has an equivalent exposed method as DAO or whether
    it is assumed you would carry out the process with SQL?.
    Andrew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    Hello Andrew,

    I couldn't find anything that looked as if it might have to do with AutoNumber in the Object Browser, but then, I'm not very familiar with ADO(X).

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    You do it with DDL. Here's some code to create an AutoNumber field using ADO.

    Sub MakeIdentityTable()
    'This creates a table that includes an autonumber field.
    'It does so by using SQL, DDL and the IDENTITY property.
    'Assumes that references to Microsoft ADO Extensions
    'for DDL and Security (MSADOX.DLL) and Microsoft
    'ActiveX Data Objects 2.5 (MSADO15.DLL) have been set.

    On Error Resume Next
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim cat As ADOX.Catalog
    Dim strSql As String
    Dim strTbl As String

    Const TABLE_EXISTS = -2147217900
    strTbl = "ztTempRefs"

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set cnn = CurrentProject.Connection
    strSql = "CREATE TABLE " & strTbl & " (RefID " & _
    "Int Identity(1,5), RefName varchar(50), RefPath " _
    & "varchar(255), RefLibraryName varchar(200), RefBuiltIn logical)"

    With cmd
    .CommandType = adCmdText
    .CommandText = strSql
    .ActiveConnection = cnn
    End With

    cmd.Execute
    Proc_exit:
    Set cmd = Nothing
    Set cat = Nothing
    Set cnn = Nothing
    Exit Function
    End Sub
    Charlotte

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    This is really a reply to all those that sent a contribution.
    Thanks again.

    Below are my two solution functions for adding an autonumber field to a table.
    I have used the two methods suggested but NOT DAO (too long).

    TWO Methods to Add an Autonumber field to a specific Table.

    Method 1...........................

    Function AddID1(strT As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String

    On Error Goto AddID1Error:

    Set cnn = CurrentProject.Connection

    strSQL = "ALTER TABLE [" & strT & "] ADD RefID Int Identity(1,5) "
    cnn.Execute strSQL
    set cnn=nothing

    MsgBox "Field RefID Added to table " & strT,vbInformation

    Exit Function

    AddID1Error:
    msgbox "Cannot Add New Field to Table " & strT & vblf & _
    "Error - " & Err.Number & vblf & _
    Err.Description,vbCritical
    Exit Function

    End Function



    Method 2...........................


    Function AddID2(strT As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String

    On Error Goto AddID2Error:

    Set cnn = CurrentProject.Connection

    strSQL = "ALTER TABLE [" & strT & "] ADD RefID AUTOINCREMENT "
    cnn.Execute strSQL
    set cnn=nothing

    MsgBox "Field RefID Added to table " & strT,vbInformation

    Exit Function

    AddID2Error:
    msgbox "Cannot Add New Field to Table " & strT & vblf & _
    "Error - " & Err.Number & vblf & _
    Err.Description,vbCritical
    Exit Function

    End Function

    The solution also works from VB if you fully define the connection string for the cnn object.

    Can you explain however the significance of the Identity(1,5) component.
    I assume this equates to AUTOINCREMENT, but have not met that expression before
    and cannot find any references to it in my library of documents.

    Thanks
    Andrew

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    This is from SQL Server Books Online:

    <img src=/w3timages/blueline.gif width=33% height=2>

    IDENTITY (Property)
    Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

    Note The IDENTITY property is not the same as the SQL-DMO Identity property that exposes the row identity property of a column.

    Syntax
    IDENTITY [ ( seed , increment ) ]

    Arguments
    seed
    Is the value that is used for the very first row loaded into the table.
    increment
    Is the incremental value that is added to the identity value of the previous row that was loaded.

    You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

    <img src=/w3timages/blueline.gif width=33% height=2>

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding an AutoNumber Field/Column to a Table (2000 and ZP)

    Much appreciated.
    I must get a decent book on SQL
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •