Alpha Video Training
Results 1 to 4 of 4

Thread: Get columns from Access Table

  1. #1
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,639

    Default Get columns from Access Table

    I need to get the list of columns (fields) in an Access table. Have a good connection string and table, but can't figure out how to get the list into A5.

    With other SQL databases I can use:

    Code:
        SELECT COLUMN_NAME
        FROM information_schema.columns 
        WHERE TABLE_NAME = 'MyTable'
        ORDER BY column_name
    But Access has no information_schema

    Anyone know how to do this? I know Alpha has a function to do it -- it's incorporated into so many genies.
    -Steve


  2. #2
    Member
    Real Name
    Bruce Jonson
    Join Date
    May 2010
    Location
    New Zealand
    Posts
    436

    Default Re: Get columns from Access Table

    Hi Steve,
    As far as being able to do it from a Query, I don't know of a way
    In Access/VBA you usually do something like this and work through the Fields collection

    Code:
    Function FieldNames(TableName as String) 
        Dim Rst As Recordset 
        Dim f As Field 
        Set Rst = CurrentDb.OpenRecordset(TableName)
       
        For Each f In Rst.Fields
             Debug.Print (f.name)
        Next
        Rst.Close
     End Function
    But this is not a lot of use from within A5, Sorry


    Bruce

  3. #3
    Member
    Real Name
    Jim Coltz
    Join Date
    Oct 2005
    Location
    Western Pennsylvania
    Posts
    600

    Default Re: Get columns from Access Table

    Alpha does this for you. Take a look at the sql::tableinfo and sql::schema classes.

    Code:
    sqltable = "jqcalendar"
    sqlconn = "::NAME::Connection1"
    conn.Open(sqlconn)
    dim ti as sql::TableInfo
    ?conn.GetTableInfo(ti,sqltable)
    = .T.
    
    ?ti
    = L AddColumn(Column as SQL::DataTypeInfo)  'Add a Column.
    L AddIndex(Index as SQL::IndexInfo)  'Add a Index.
    V Clear()  'Reset the contents of the TableInfo as if it had just been declared.
    P Clone()  'Create a copy of an object instance.
    L ColumnIsInPrimaryKey(N Index)  'Returns true if the column matching the index requested is part of the table's primary key
    N ColumnNumber(Name as C)  'Get the index of a Column from the name.
    L DeleteColumn(Index as N)  'Delete a Column.
    L DeleteIndex(Index as N)  'Delete a Index.
    N IndexNumber(Name as C)  'Get the index of a Index from the name.
    L InsertColumn(Column as SQL::DataTypeInfo [, InsertBefore as N = 1])  'Insert a new Column.
    L InsertIndex(Index as SQL::IndexInfo [, InsertBefore as N = 1])  'Insert a new Index.
    C ListColumns([L IncludeTypes = .f.])  'Create a list of the table columns (optionally including types).
    L MoveColumn(MoveTo as N, MoveFrom as N [ItemsToCopy as N = 1])  'Move a block of Column items.
    L MoveIndex(MoveTo as N, MoveFrom as N [ItemsToCopy as N = 1])  'Move a block of Index items.
    P NewInstance()  'Create a new object instance of the same type.
    L UpdateStatistics(P Connection)  'Gather statistical information about the actual table.
    ActiveLinkDBFRowSyntax = ID,N,11,0,"notnullable,constant,setnull,showautonumber"
    SUBJECT,M,10,0,"notnullable,updateable,initialize"
    LOCATION,M,10,0,"nullable,updateable,setnull,shownull"
    DESCRIPTION,M,10,0,"nullable,updateable,setnull,shownull"
    STARTTIME,T,17,0,"notnullable,updateable,initialize"
    ENDTIME,T,17,0,"notnullable,updateable,initialize"
    ISALLDAYEVENT,N,6,0,"nullable,updateable,setnull,shownull"
    COLOR,M,10,0,"nullable,updateable,setnull,shownull"
    RECURRINGRULE,M,10,0,"nullable,updateable,setnull,shownull"
    MULTI_DAY,N,6,0,"nullable,updateable,setnull,shownull"
    EDITABLE,N,6,0,"nullable,updateable,setnull,shownull"
    ATTENDEES,M,10,0,"nullable,updateable,setnull,shownull"
    RECURRING,N,6,0,"nullable,updateable,setnull,shownull"
    GROUPID,N,11,0,"nullable,updateable,setnull,shownull"
    
    Alias = ""
    CanUpdateInTransaction = .T.
    Catalog = ""
    +Column.
    Comment = ""
    DBFRowSyntax = ID,N,11,0,"notnullable,constant,generate"
    SUBJECT,M,10,0,"notnullable,updateable,initialize"
    LOCATION,M,10,0,"nullable,updateable,setnull,shownull"
    DESCRIPTION,M,10,0,"nullable,updateable,setnull,shownull"
    STARTTIME,T,17,0,"notnullable,updateable,initialize"
    ENDTIME,T,17,0,"notnullable,updateable,initialize"
    ISALLDAYEVENT,N,6,0,"nullable,updateable,setnull,shownull"
    COLOR,M,10,0,"nullable,updateable,setnull,shownull"
    RECURRINGRULE,M,10,0,"nullable,updateable,setnull,shownull"
    MULTI_DAY,N,6,0,"nullable,updateable,setnull,shownull"
    EDITABLE,N,6,0,"nullable,updateable,setnull,shownull"
    ATTENDEES,M,10,0,"nullable,updateable,setnull,shownull"
    RECURRING,N,6,0,"nullable,updateable,setnull,shownull"
    GROUPID,N,11,0,"nullable,updateable,setnull,shownull"
    
    IdentityColumnSubscript = 1
    IdentitySequenceName = ""
    +Index.
    LastKnownRowCount = -1
    Name = "jqcalendar"
    NativeAPI = "ADOX"
    NativeSyntax = "Access"
    Owner = ""
    ...... lots more stuff
    Jim Coltz
    Alpha Custom Database Solutions, LLC
    A5CustomSolutions.com
    jimcoltz@a5customsolutions.com

  4. #4
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,639

    Default Re: Get columns from Access Table

    Thanks Jim, looks like what I need.
    -Steve


Similar Threads

  1. Insert fails if table has computed columns
    By nlights in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 03-21-2012, 05:15 PM
  2. Compare table function to display in 2 columns
    By MikeData in forum Code Archive
    Replies: 4
    Last Post: 02-23-2012, 11:16 AM
  3. Compare table function to display in 2 columns
    By MikeData in forum Alpha Five Version 11 - Desktop Applications
    Replies: 9
    Last Post: 02-22-2012, 01:25 PM
  4. Access table in dialog
    By russellhaggard in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 07-21-2011, 01:18 PM
  5. Update Access table
    By Bill Parker in forum Web Application Server v6
    Replies: 5
    Last Post: 03-29-2005, 07:12 PM

Bookmarks

Posting Permissions

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