Results 1 to 9 of 9

Get Field names from a table in CurrentDB()

  1. #1
    e.badin is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    16

    Question Get Field names from a table in CurrentDB()

    Hello,



    Is there a way of getting the field names from a table in currentdb()?
    I need to be able to access them as a vector, [1],[2].... etc

    Why do I need it? Well I have a table that always changes the no. of columns and column names. What I need is to get all the column names starting the 4th column.

    Thanks

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What you want to do is possible. Allen Browne has some code here that will loop through the fields of a table (the code does a lot more but you should be able to extract what you need).

    My bigger concern is why do you constantly need to change your table structure? It sounds like your database may not be normalized. Could you provide more details about what you are doing and why?

  3. #3
    e.badin is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    16
    Quote Originally Posted by jzwp11 View Post
    What you want to do is possible. Allen Browne has some code here that will loop through the fields of a table (the code does a lot more but you should be able to extract what you need).

    My bigger concern is why do you constantly need to change your table structure? It sounds like your database may not be normalized. Could you provide more details about what you are doing and why?
    Thanks jzwp, I figured it on my own.
    Here is an example for people that may struggle with something similar:

    -----------------------------------------------------------------------
    Function countfld(tablename As String) As Integer
    On Error GoTo NoTable 'If there is no table capture the error.
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim y As Integer
    y = 0
    Set rst = CurrentDb.OpenRecordset(tablename)
    'Go throught the column names
    For Each fld In rst.Fields
    y = y + 1
    Next fld
    countfld = y
    rst.Close
    Set rst = Nothing
    Set fld = Nothing
    Exit Function
    NoTable:
    MsgBox "Table does not exist!...Incorect use of function countfld()", vbOKOnly, "Error"

    End Function
    ---------------------------------------------------------------------


    Regarding why? Well, the dept I work for needs some reports that are based on csv files generated by a project management tool. The csv gets generated based on the filters used by a user in the PM tool so everytime the number of columns and column names may change (4 stay the same always, the rest of the column names are dates in a weird format, like "DDD_DD-MM", so they change). The end product should be a barchart with filters on some of the fields, very user friendly so that my collegues could use it.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad to hear that you worked out a solution.

  5. #5
    e.badin is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    16
    thanks, the trouble is just starting, I'm opening more topics as I have a lot of questions unanswered

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I was wondering if there was another way of doing what you are attempting without constantly changing your table structure. Could you take the column headers of the csv file and add those as records (not fields) to a table and then have another table that holds the data and join the two related tables together?

  7. #7
    e.badin is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    16
    I have up to 255 columns in the csv file and 10000 rows... hmmm... have think about it... I mean it would simplify things the problem is how

  8. #8
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Can you post an example of the csv file?

  9. #9
    e.badin is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    16
    I can send you the an exact replica if you send me your e-mail over pm and if it's not too much trouble

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to retrieve field names in a table
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-05-2010, 09:09 PM
  2. Replies: 1
    Last Post: 05-17-2010, 12:21 PM
  3. creating field names in table without typing
    By GHanover in forum Database Design
    Replies: 2
    Last Post: 12-14-2009, 05:13 AM
  4. Replies: 5
    Last Post: 03-31-2009, 09:16 PM
  5. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums