Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Northampton, England

    Unanswered: Create table code

    I am using the following code to create a table, add a primary key and index. The problem is the code chokes with an error message. The error message is ' Invalid field definition in 'MyTestField' in definition or index or relationship. The code I am using is:-

    Dim WS As Workspace
    Dim DB As DAO.Database
    Dim TD As DAO.TableDef
    Dim FLD As DAO.Field

    'Get default Workspace
    Set WS = DBEngine.Workspaces(0)
    Set DB = CurrentDb
    Set TD = DB.CreateTableDef("myTestTable")

    'Create fields and data types. These are all the data types
    'AutoNumber is handled differently

    With TD
    .Fields.Append .CreateField("Field1", dbText, 255)
    .Fields.Append .CreateField("Field2", dbDouble, 2)
    .Fields.Append .CreateField("Field3", dbInteger, 3)
    .Fields.Append .CreateField("Field4", dbLong)

    Set FLD = .CreateField("fldID", dbLong)
    FLD.Attributes = fldAttributes Or dbAutoIncrField
    .Fields.Append FLD

    .Fields.Append .CreateField("Field5", dbMemo)
    .Fields.Append .CreateField("Field6", dbBoolean)
    .Fields.Append .CreateField("Field7", dbCurrency)
    End With

    'Create a default value
    Set FLD = TD.CreateField("MyTestField", dbText, 255)
    FLD.DefaultValue = "default Value"

    'Create an index and primary key
    Set idx = TD.CreateIndex("PrimaryKey")
    Set FLD = idx.CreateField("FldID", dbLong)
    idx.Fields.Append FLD
    idx.Primary = True
    TD.Indexes.Append idx

    Chokes on this bit of code

    'Create an index
    Set idx = TD.CreateIndex("MyTestField")
    Set FLD = idx.CreateField("MyTestField")
    idx.Fields.Append FLD
    TD.Indexes.Append idx

    DB.TableDefs.Append TD

    Can anyone see the error?

  2. #2
    Join Date
    Jul 2003
    It looks like you forgot the

    .Fields.Append FLD

    afer you created MyTestField
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    Can't be bothered hacking through your code The below does just fine for me though:
        With TDef
                    Set idxNew = .CreateIndex("Index_1")
                    idxNew.Fields.Append .CreateField("MyFieldName")
                    idxNew.IgnoreNulls = False
                    .Indexes.Append idxNew
        End With
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2002
    Northampton, England
    Spot on RedNeckGeek. Forgot to append it. Pootle, I will give your code a try, it looks much more compact than mine. One again, many thanks.

Posting Permissions

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