Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    US
    Posts
    3

    Unanswered: Return table name in query

    Hello-

    How can I return the following as columns in a select query:

    1. a table name from a participant in the query
    2. the query name
    2. the name of the database

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You can make functions to get the data... How's your vba?

  3. #3
    Join Date
    Mar 2004
    Location
    US
    Posts
    3
    I don't really know VBA. Just formatting macros in Excel, etc. Can you help with a statement?

    Thanks for the help.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Here is how you can get a list of tables using ado:

    'Set a reference for Microsoft ADO Ext. 2.1
    'for DDL and security.
    Dim cboStr As String, cancel As Integer
    Dim objT As Table, objV As Object

    Set adoxCat = New ADOX.Catalog
    adoxCat.ActiveConnection = strConnect
    cboStr = "Table/View Name;Type;"

    For Each objT In adoxCat.Tables
    If Left(objT.Name, 4) = "mSys" Or _
    Left(objT.Name, 1) = "~" Then
    GoTo NotATable
    End If

    ' Before adding the table to the ComboBox string, make
    ' sure the table isn't a view as we'll add these later.
    If objT.Type <> "VIEW" Then
    cboStr = cboStr & objT.Name & ";table;"
    End If

    NotATable:
    Next objT


    I found this on
    http://msdn.microsoft.com/library/de...ataShaping.asp

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I saw this same type of question posted recently ... Try searching for it ...

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Return table name in query

    Originally posted by MAS
    Hello-

    How can I return the following as columns in a select query:

    1. a table name from a participant in the query
    2. the query name
    2. the name of the database

    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Name)<>'MSysObjekts') AND ((MSysObjects.Flags)<>-2147483648 And (MSysObjects.Flags)<>2 And (MSysObjects.Flags)<>-2147483645) AND ((MSysObjects.Type) In (1,4)))
    ORDER BY MSysObjects.Name;

    Will return the tables names

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Thanks Hammbakka. That is a great sql statement; however, in Access 2003, it also shows MSysAccessStorage with flag of 10. You may want to add that to your query:

    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Name)<>'MSysObjekts') AND ((MSysObjects.Flags)<>-2147483648 And (MSysObjects.Flags)<>2 And (MSysObjects.Flags)<>10 And (MSysObjects.Flags)<>-2147483645) AND ((MSysObjects.Type) In (1,4)))
    ORDER BY MSysObjects.Name;

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thank you I'll modify my query, and see whether it functions under Acce$$97.

    Thanks

  9. #9
    Join Date
    Mar 2004
    Location
    US
    Posts
    3
    Thanks friends! I tried the query for 2000 and it worked great.

Posting Permissions

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