Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    retrive name of all table in dta base ... (6.0)

    I use ADO and vb classic .
    My conn is:
    CNSQL1.Open "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=yy;Initial Catalog=DB_PAST_DUE;Data Source=MY-F0D2D0395FFESQLEXPRESS"

    is possible to retrive all name of all table refered DB_PAST_DUE...

    ???

    Tks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: retrive name of all table in dta base ... (6.0)

    You can use code like this:

    Dim rstTables As ADODB.Recordset
    Dim strName As String

    ' Open recordset with list of tables
    Set rstTables = CNSQL1.OpenSchema(adSchemaTables)
    Do While Not rstTables.EOF
    ' The name of the table is in the TABLE_NAME field
    strName = rstTables!TABLE_NAME
    ...
    ' Go to next record
    rstTables.MoveNext
    Loop
    ' Clean up
    rstTables.Close
    Set rstTables = Nothing

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retrive name of all table in dta base ... (6.0)

    PERFECT!!!!
    only you!
    sal.

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retrive name of all table in dta base ... (6.0)

    hummmmmmmmmmmm...
    Wath is this:

    MSysAccessObjects
    MSysACEs
    MSysObjects
    MSysQueries
    MSysRelationships

    i have retrived also this name of table in list ??????

    Result of debug.print rstTables!TABLE_NAME

    AREA_TERR
    DATE_INQ
    DATI
    GRADI
    MERCATI
    MERCATI_STAFF
    MSysAccessObjects
    MSysACEs
    MSysObjects
    MSysQueries
    MSysRelationships
    SCHEDA
    SETTORI
    SPORTELLI
    SPORTELLI_1
    STAFF
    TABELLA_SETT_ESTESA
    UTENTI

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: retrive name of all table in dta base ... (6.0)

    An Access database uses hidden system tables to store information about the database itself. Access uses these tables internally. In most circumstances, you don't want to include them in your list. So you could use

    Do While Not rstTables.EOF
    ' The name of the table is in the TABLE_NAME field
    strName = rstTables!TABLE_NAME
    If Not strName Like "MSys*" Then
    ...
    End If
    ' Go to next record
    rstTables.MoveNext
    Loop

Posting Permissions

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