Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    Philadelphia, PA
    Posts
    3

    Unanswered: Table definitions

    How do I get the column name, data type and size of a table in VB code?

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    easy MS Access DAO way or ADO way?

    MS Access way:

    number of Fields:
    currentdb.TableDefs("myTable").Fields.Count

    the name of the first field
    currentdb.TableDefs("myTable").Fields(0).name

    default value
    currentdb.TableDefs("myTable").Fields(0).DefaultVa lue

    field type:
    currentdb.TableDefs("myTable").Fields(3).Type

    validation rule
    currentdb.TableDefs("myTable").Fields(3).Validatio nRule

    validation text
    currentdb.TableDefs("myTable").Fields(3).Validatio nText


    if you want ADO way, let me know....



    jiri

  3. #3
    Join Date
    Jan 2003
    Location
    Philadelphia, PA
    Posts
    3

    Thumbs up

    Thanks a lot. This is exactly what I need.

    Wayne

  4. #4
    Join Date
    Dec 2003
    Posts
    7
    Originally posted by playernovis
    easy MS Access DAO way or ADO way?
    ......
    the name of the first field
    currentdb.TableDefs("myTable").Fields(0).name
    .......
    field type:
    currentdb.TableDefs("myTable").Fields(3).Type
    ......
    if you want ADO way, let me know....

    jiri
    Hello

    If need change name and type field in base, but in external base.

    currentdb.TableDefs("myTable").Fields(0).name = "NewFieldName" is ok
    but
    currentdb.TableDefs("myTable").Fields(0).Type = dbLong not work

    please, write me alternate options in ADO

    Gregorek

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by Gregorek
    Hello

    If need change name and type field in base, but in external base.

    currentdb.TableDefs("myTable").Fields(0).name = "NewFieldName" is ok
    but
    currentdb.TableDefs("myTable").Fields(0).Type = dbLong not work

    please, write me alternate options in ADO

    Gregorek
    You can also use ALTERTable , ALTER Field

  6. #6
    Join Date
    Dec 2003
    Posts
    7
    Originally posted by hammbakka
    You can also use ALTERTable , ALTER Field
    DoCmd.RunSQL ("ALTER TABLE " & myTable.Name & " ALTER COLUMN " & fld.Name & " INT;")
    is ok, but if I set other db (C:\...\Northwind.mdb).

    Dim myBase As DAO.Database
    Dim myTable As DAO.TableDef
    Dim myField As DAO.Field

    Set myBase = OpenDatabase("Northwind.mdb")

    'my field type in my table is dbByte type
    For Each myField In myBase.TableDefs(TableName).Fields
    DoCmd.RunSQL ("ALTER TABLE " & TableName & " ALTER COLUMN " & myField.Name & " INT;")
    Next myField

    if I run this in table on the base - all is ok,
    if I run this in externally base - this sub not work

    I run other procedure:
    For Each myField In myBase.TableDefs(TableName).Fields
    If myField.Name = TableName Then myField.Type = 4 'or myField.Type = dbLong
    Next myField

    this procedure turn message: Invalid operation.

    Please, help me.

    Gregorek

  7. #7
    Join Date
    Dec 2003
    Posts
    7
    I find answer:

    If use external base answer is:

    myBase.Execute "ALTER TABLE " & myTable & " ALTER COLUMN " & myField & " INT;"

    not

    DoCmd.RunSQL ("ALTER TABLE " & myTable & " ALTER COLUMN " & myField & " INT;")

    thanks for all
    Gregorek

Posting Permissions

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