Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Return list of tables in a query

    This is probably a simple request, and I'm sure I used to have the code to do it, but I've lost it and can't find it on the web. Basically, I need a query that returns a list of tables in the current database. Can anyone please help?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT name
    FROM  MSysObjects
    WHERE  type = 1
     AND name NOT LIKE "~*"
     AND name NOT LIKE "msys*"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Question Poots...
    would
    type = 1 signify a table (as opposed to a query?)
    What other type values are there and what do they represent?
    George
    Home | Blog

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    This might help.

    You might want to convert this to a function, to use elsewhere besides the immediate window, but that depends where/how you want to use it.
    Me.Geek = True

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Question Poots...
    would
    type = 1 signify a table (as opposed to a query?)
    What other type values are there and what do they represent?
    Yes - 1 = table not query.
    You can google this info - I can't really remember but quick guestimates:
    Code:
    -32768 = form
    -32764 = report
    -32757 = errr....
    1      = table
    2      = ummm.....
    3      = container
    5      = query
    I think different queries (update, delete, crosstab etc) might have different type numbers.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Quote Originally Posted by pootle flump
    Code:
    SELECT name
    FROM  MSysObjects
    WHERE  type = 1
     AND name NOT LIKE "~*"
     AND name NOT LIKE "msys*"
    Thanks for that. Do you know which VBA constants I can use to refer to each object type if I do this in VBA?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    TMK there are none. You can create your own though. Have you used enums in VBA?
    http://www.cpearson.com/excel/Enums.htm

    BTW - I should probably point out the MS don't recommend (or more accurately recommend that you don't) access system tables directly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Very useful. I didn't know about enums. Thanks.

  9. #9
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Hmmm, I'm having a few problems. When my databse closes, I want to delete any import error tables generated by the user importing data during a session.

    Code:
        Dim cnn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim lngCounter As Long
        
        Set cnn = CurrentProject.Connection
        Set rs = New ADODB.Recordset
        
        rs.CursorLocation = adUseClient
        rs.Open "qryLocalTables", cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc
        
        If rs.EOF <> True Then
            rs.MoveFirst
            For lngCounter = 1 To rs.RecordCount
                DoCmd.DeleteObject acTable, rs("name")
                rs.MoveNext
            Next lngCounter
        End If
    
        rs.Close
        cnn.Close
        Set rs = Nothing
        Set cnn = Nothing
    
        DoCmd.SetWarnings True
        DoCmd.Quit acExit
    I saved your query as qryLocalTables. However, when I execute the code, rs.RecordCount never returns anything other than zero. If I double click on qryLocalTables I get 2 rows. I tried using another query to test my code was correct, and it returns rows without problem from other queries.

  10. #10
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Here's the source for qryLocalTables:
    Code:
    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Name) Like 'Sheet*' 
    And (MSysObjects.Name) Not Like '~*' 
    And (MSysObjects.Name) Not Like 'msys*') 
    AND ((MSysObjects.Type)=1))

Posting Permissions

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