Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    5

    Question Unanswered: Access displaying SQL Table Names

    So I have a sql database with an Access 2000 front-end user interface. I'm using ODBC to connect to the SQL database and process all the necessary processes and doing all the other cool crap. Now I'm looking to add a section to ease the job of the user. This section needs to display all the current user created tables in the sql database.

    Ex.
    SQL Database (nomen)
    nomen_200409
    nomen_200411
    nomen_200501
    etc

    I want the Access display to be
    nomen_200409
    nomen_200411
    nomen_200501
    etc

    Is this possible to do without any user interaction?

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    I think you can do it using a pass through query....

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look in the help system for the Tabledefs collection if using DAO, otherwise look in the Catalog / Tables collection if using ADO/ADOX

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    Dim cat As New ADOX.Catalog
    Dim conn As New ADODB.Connection
      
    conn.Open connString
    cat.ActiveConnection = conn
    
    Dim table As ADOX.table
    Dim column As ADOX.column
      
    For Each table In cat.Tables
      
      Select Case table.Type
       
         Case "VIEW"
     
    
         Case "TABLE"
         
           For Each column In table.Columns
           
           
           Next
           
      End Select
      
    Next
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Oct 2004
    Posts
    5
    thanks

    I like that method much better than using a pass-through query to hit the sysobjects table. It makes everything just a little cleaner plus gives me the abiltiy to add some additional functionality instead of adding additional queries

    also works to return user table's name (in sql or with pass-through)
    select name
    from sysobjects
    where type = 'U'

Posting Permissions

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