Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grabbing Tables/Field names (2003)

    Hi there,

    I'd like to have a combo box list all the table names of my Access database. I took a quick peek at the system tables but I can't get things to work. The MSysObjects table seems not to be listing table names, but rather query names and maybe other objects. What I'd like to know is if there is a table such as one I recall there is in SQL Server where I can find all the table names.

    Additionally, I'd like to have another combo box list all the field names of a certain table (which will in turn be the table selected in the first combo box). Can this be done?

    As for the comboboxes code, I think it's simple enough for me to handle. What I'd like to know is more related to WHERE these data are.

    Thank you very much in advance
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grabbing Tables/Field names (2003)

    I finally came across the table names in the MSysObjects table <img src=/S/blush.gif border=0 alt=blush width=15 height=15>, among the other objects' names.

    Furthermore I found how to filter tables from other object types:

    http://www.perfectparadigm.com/tip001.html

    What I'd like to do is filter (via a query) user-created tables from system tables. What I mean is, the Type value for tables is 1, but this value includes ALL tables. Intuition told me that all system tables were created at the moment the database is created, so I thought about using a filter like:
    "where DateCreate > (certain date)"
    But this is not true for ALL the system tables in my database (ie, some system tables have later DateCreate dates than user-created tables).

    Is there a conventional way of filtering system from user objects in the MSysObjects table?

    Thank you in advance and sorry for the mistake
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grabbing Tables/Field names (2003)

    You can use (for example)

    SELECT Name FROM MSysObjects WHERE Type=1 AND Not Left([Name], 4) = "MSYS"

    This will exclude the system tables.

    To get the fields, you can use DAO or ADOX. Set the Row Source Type of the combo box to Value List. For example using DAO:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("MyTable")

    For Each fld In tdf.Fields
    Me.cboFieldList.AddItem fld.Name
    Next fld

    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

  4. #4
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grabbing Tables/Field names (2003)

    Hello, Hans: as usual, thank you so much for your quick response.

    I'll try to work my way with your leads. Have a nice week.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

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