Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: SQL resquest DB name

    hi
    i wanted to know if there is a sql command that can give me back the Table name of my DB but i cant find any on the internet
    something like "SELECT DB_NAME() AS dbName;"
    is it possible?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For an Access database with local ("native Access") tables you can use:
    Code:
    SELECT [Name] FROM MSysObjects WHERE [Type] = 1;
    For linked tables you can use:
    Code:
    SELECT [Name] FROM MSysObjects WHERE [Type] = 4;

    Notice however that Microsoft does not recommend this method. The canonical ways for retrieving the table names are:
    Code:
        Dim obj As AccessObject
        Dim dbs As Object
    
        Set dbs = Application.CurrentData
        For Each obj In dbs.AllTables
            Debug.Print obj.Name
        Next obj
        Set dbs = Nothing
    or:
    Code:
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            Debug.Print tdf.Name
        Next
        Set dbs = Nothing
    There must be a method using the ADODB library but I don't remember it right know (I almost never use ADO), and I'm too lazy to search for it.
    Have a nice day!

Posting Permissions

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