Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Need to lookup and Select Table Names-Not Fields

    I looked at ListBox and TableDefs but I don't think they are what I need.

    What I'm doing . . .

    Open form . . .

    Need to look for Tables names in a specific folder on the hard drive.

    Select a tableName . . .

    Create a variable of the tablename. . . , Dim etc., or other method.

    Pass that tablename variable to a query and run the report. The query is a subReport in the report footer so it will execute upon opening the report.

    Can someone point me in the right direction please?

    Thanks . . . Rick

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    A few ways you can get this. From the system tables:
    Code:
    SELECT Name 
    FROM MSysObjects 
    WHERE Type IN (1, 4, 6) 
    AND Name NOT LIKE "~*" 
    AND Name NOT LIKE "MSys*"
    Or you could loop through the tabledefs collection or the CurrentDB.Containers("Tables") contatiner.

    I wouldn't bother with the variable. Chuck the table name in a textbox (hidden or otherwise) and have the query read that

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    HTH . . . in a few days???

    Please explaine (1, 4, 6)

    ~ is a variable? (Paradox uses this for variables.)

    Thanks much . . . Rick

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Rick Schreiber
    HTH . . . in a few days???
    Eh? HTH = Hope this Helps

    1, 4 and 6 are different types of table. As I recall (not certain here):
    1 = local
    4 = ODBC linked
    6 = Other linked

    You could try looking them up though - google MSysObjects

    Deleted objects in access are only flagged for deletion and not actually deleted until the database is next compacted. These "deleted" objects are prefixed with "~". Also quasi objects, such as queries that have been typed directly into a form's recordsource.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    . . .
    SELECT Name
    FROM MSysObjects
    WHERE Type IN (1, 4, 6)
    AND Name NOT LIKE "~*"
    AND Name NOT LIKE "MSys*"

    This works great!

    I would like to highlight and select [Enter key] a table name in the dynaset and have it passed to a variable in a query?

    Dim TableName as String ???

    Thanks . . . Rick

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    . . ."I wouldn't bother with the variable. Chuck the table name in a textbox (hidden or otherwise) and have the query read that."

    That sounds good, but when I create a text box and select a table using queries I get an error "no valid fields can be found in the "qrySelecTable." Please select a new source.

    This is what I have under Option Compare Database

    Private Sub Check8_Click()

    DoCmd.OpenQuery "qrySelectTable", acViewPreviw
    End Sub

    Private Sub Command0_Click()
    DoCmd.OpenReport "rpSCMLS_report", acViewPreview
    End Sub

    Private Sub Detail_Click()

    End Sub

    Any ideas . . . Thanks Much!

    Rick

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Rick Schreiber
    That sounds good, but when I create a text box and select a table using queries I get an error "no valid fields can be found in the "qrySelecTable." Please select a new source.
    qrySelecTable is the SQL I gave you yes? So - how do you select from it? Actually - combining the ideas - why not make the query the row source for a combo box. That way you need no code or textbox - just select the table name and have the query read the combobox.
    Last edited by pootle flump; 07-19-06 at 17:51.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Interesting . . . I 'm going to try that this afternoon.

    Uh . . . "why not make the query the row source for a combo box"

    Don't understand this really. I'm pretty new to Access 2003, but I'm going to see if I can figure it out today.

    THANKS MUCH ! ! !

    Rick

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Record source.... I meant record source oops

    Yeah - just stick a combo on the form and paste the sql in the record source.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2010
    Location
    Switzerland
    Posts
    1

    Thanks, it works, but...

    Quote Originally Posted by pootle flump View Post
    Yeah - just stick a combo on the form and paste the sql in the record source.
    Thanks for the code!

    Just wanted to say, that this didn't work (put the sql in record source)!

    But, if I put the sql in Form_Load():
    Code:
        Dim strSql As String
        strSql = "SELECT MSysObjects.Name as FeatureTabellen from MSysObjects WHERE (((MSysObjects.Name) Like 'Feature_Daten_*' And (MSysObjects.Name) Not Like '~*' And (MSysObjects.Name) Not Like 'MSys*') AND ((MSysObjects.Type) In (1,4,6))) ORDER BY MSysObjects.Name;"
    
        FeatureTabellen.RowSource = strSql
    It does work.

    Any ideas why it won't work if I put this sql directly into the record source from my combobox?

Posting Permissions

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