• Grabbing Tables/Field names (2003)

    Author
    Topic
    #442400

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1064850

      I finally came across the table names in the MSysObjects table blush, among the other objects’ names.

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

      http://www.perfectparadigm.com/tip001.html%5B/url%5D

      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

      • #1064855

        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

        • #1064902

          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.

    Viewing 0 reply threads
    Reply To: Grabbing Tables/Field names (2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: