none
Create new table with primary field with VBA RRS feed

  • Question

  • I am using VBA to create a table, but I have problem to set one of the fields as primary field.

    Below is my code of creating a new table but without setting  any fields to be  primary field:

    Question: How can I set the MyFirstField as primary field.

    Kindly advise, thank you.

    Private Sub CreateNewTable()

    Dim dbMYDB As DAO.Database
    Dim tdf01 As DAO.TableDef
    Dim Fld001 As DAO.Field, Fld002 As DAO.Field

    Set dbMYDB = OpenDatabase("MyDatabase.accdb")
    Set tdf01 = dbMYDB.CreateTableDef("MyNewTable")
    Set Fld001 = tdf01.CreateField()
    Set Fld002 = tdf01.CreateField()

    With Fld001
      .Name = "MyFirstField"
      .Type = dbText
    End With
    With Fld002
      .Name = "MySecondField"
      .Type = dbText
    End With

    tdf01.Fields.Append Fld001
    tdf01.Fields.Append Fld002
    dbMYDB.TableDefs.Append tdf01

    dbMYDB.TableDefs.Refresh
    Application.RefreshDatabaseWindow

    End Sub


    TS Lim


    • Edited by TS Lim Thursday, April 26, 2012 3:43 AM Tyop error
    Thursday, April 26, 2012 3:40 AM

Answers

  • Hi TS Lim

    It's much easier to create tables using DDL (data definition language) instead of VBA and DAO.

    In your case the table could be created this way:

    Private Sub CreateNewTable()
      CurrentDB.Execute "CREATE TABLE MyNewTable(ID COUNTER(1, 1) PRIMARY KEY, " & _
                        "MyFirstField VARCHAR(255), " & _
                        "MySecondField VARCHAR(255))", dbFailOnError
      CurrentDB.TableDefs.Refresh
    End Sub

    TS Lim wrote:

    I am using VBA to create a table, but I have problem to set one of the
    fields as primary field.

    Below is my code of creating a new table but without setting any fields
    to be primary field:

    Question: How can I set the MyFirstField as primary field.

    Kindly advise, thank you.

    Private Sub CreateNewTable()

    • Edited by Henry Habermacher Thursday, April 26, 2012 4:09 AM
    • Marked as answer by TS Lim Thursday, April 26, 2012 6:14 AM
    Thursday, April 26, 2012 4:04 AM
  • Hi,

    however, if you're using DAO object model to create a table, you should create an index for this table and set its .Primary property to True:

    Public Sub CreateNewTable()
    Dim dbMYDB As DAO.Database
    Dim tdf01 As DAO.TableDef
    Dim Fld001 As DAO.Field, Fld002 As DAO.Field
    Dim idx As DAO.Index
    Set dbMYDB = OpenDatabase("MyDatabase.accdb")
    Set tdf01 = dbMYDB.CreateTableDef("MyNewTable")
    Set Fld001 = tdf01.CreateField()
    Set Fld002 = tdf01.CreateField()
    With Fld001
      .Name = "MyFirstField"
      .Type = dbText
    End With
    With Fld002
      .Name = "MySecondField"
      .Type = dbText
    End With
    With tdf01
        Set idx = .CreateIndex("PrimaryKey")
        idx.Fields.Append idx.CreateField("MyFirstField")
        idx.Primary = True
        .Indexes.Append idx
    End With
    tdf01.Fields.Append Fld001
    tdf01.Fields.Append Fld002
    dbMYDB.TableDefs.Append tdf01
    dbMYDB.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    End Sub


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Marked as answer by TS Lim Thursday, April 26, 2012 12:39 PM
    Thursday, April 26, 2012 7:08 AM

All replies

  • Hi TS Lim

    It's much easier to create tables using DDL (data definition language) instead of VBA and DAO.

    In your case the table could be created this way:

    Private Sub CreateNewTable()
      CurrentDB.Execute "CREATE TABLE MyNewTable(ID COUNTER(1, 1) PRIMARY KEY, " & _
                        "MyFirstField VARCHAR(255), " & _
                        "MySecondField VARCHAR(255))", dbFailOnError
      CurrentDB.TableDefs.Refresh
    End Sub

    TS Lim wrote:

    I am using VBA to create a table, but I have problem to set one of the
    fields as primary field.

    Below is my code of creating a new table but without setting any fields
    to be primary field:

    Question: How can I set the MyFirstField as primary field.

    Kindly advise, thank you.

    Private Sub CreateNewTable()

    • Edited by Henry Habermacher Thursday, April 26, 2012 4:09 AM
    • Marked as answer by TS Lim Thursday, April 26, 2012 6:14 AM
    Thursday, April 26, 2012 4:04 AM
  • Hi Henry,

    Thank you for the fast and better solution for creating table.


    TS Lim

    Thursday, April 26, 2012 6:14 AM
  • Hi,

    however, if you're using DAO object model to create a table, you should create an index for this table and set its .Primary property to True:

    Public Sub CreateNewTable()
    Dim dbMYDB As DAO.Database
    Dim tdf01 As DAO.TableDef
    Dim Fld001 As DAO.Field, Fld002 As DAO.Field
    Dim idx As DAO.Index
    Set dbMYDB = OpenDatabase("MyDatabase.accdb")
    Set tdf01 = dbMYDB.CreateTableDef("MyNewTable")
    Set Fld001 = tdf01.CreateField()
    Set Fld002 = tdf01.CreateField()
    With Fld001
      .Name = "MyFirstField"
      .Type = dbText
    End With
    With Fld002
      .Name = "MySecondField"
      .Type = dbText
    End With
    With tdf01
        Set idx = .CreateIndex("PrimaryKey")
        idx.Fields.Append idx.CreateField("MyFirstField")
        idx.Primary = True
        .Indexes.Append idx
    End With
    tdf01.Fields.Append Fld001
    tdf01.Fields.Append Fld002
    dbMYDB.TableDefs.Append tdf01
    dbMYDB.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    End Sub


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Marked as answer by TS Lim Thursday, April 26, 2012 12:39 PM
    Thursday, April 26, 2012 7:08 AM
  • Hi Andrey,

    Thank you for your answer, I have now two solutions for creating tables with Primary key, one from you and one from Henry.


    TS Lim

    Thursday, April 26, 2012 12:39 PM