Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Fill a cbo with table names?

    Hi ALL!
    I would like to get the names of all tables, and queries in my database using an ado Rs and I would also like to put 2 Combo boxes on a form and fill them with the tables & queries from my database, is this possible?


    M~

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Yes, possible...
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2003
    Posts
    267
    Norberto.

    How? I am interested too.

    S-

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    For the query with the querydefs.
    For the tables with tabledefs.
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Posts
    267
    I guess I am still a little confused,

    How do I use..

    For the query with the querydefs.
    For the tables with tabledefs

    With ADO

    Thanks.

    S-

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    This sample find if there is a query call "ContratosRecientes" in the current data base and remove then from the colecction QueryDefs.

    Sub ConsultaNueva()

    Dim dbs As Database, qdf As QueryDef
    Dim strSQL As String


    Set dbs = CurrentDb

    dbs.QueryDefs.Refresh

    For Each qdf in dbs.QueryDefs
    If qdf.Name = "ContratosRecientes" Then
    dbs.QueryDefs.Delete qdf.Name
    End If
    Next qdf
    End Sub

    This sample remove all the link ( only the link ) tables from the current
    data base.

    Option Compare Database
    Option Explicit
    Sub BorrarTablas()
    Dim dbs As Database, tdf As TableDef
    Dim strName As String
    Dim intBucle As Integer

    ' Devolver referencia a la base de datos actual.
    Set dbs = CurrentDb


    dbs.TableDefs.Refresh
    With dbs

    'Debug.Print .TableDefs.Count & _
    ' " TableDefs "
    volver:
    For intBucle = 0 To .TableDefs.Count - 1

    ' Comparar el valor de la propiedad y la constante en cuestión.
    If (.TableDefs(intBucle).Attributes And dbSystemObject) Or _
    (.TableDefs(intBucle).Attributes And dbHiddenObject) Then
    'Debug.Print tdf.Name
    Else
    If (.TableDefs(intBucle).Attributes And dbAttachedTable) Then

    strName = .TableDefs(intBucle).Name
    dbs.TableDefs.Delete strName
    dbs.TableDefs.Refresh
    GoTo volver
    End If

    End If
    Next intBucle

    .Close
    End With

    Set dbs = Nothing
    End Sub
    Saludos
    Norberto

  7. #7
    Join Date
    Nov 2003
    Posts
    267
    Thanks, That is great.

    S-

  8. #8
    Join Date
    Oct 2003
    Posts
    311
    Argentina saves the day!!


    Thanks!

    ill try it

  9. #9
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Fill a cbo with table names?

    Originally posted by mikezcg
    Hi ALL!
    I would like to get the names of all tables, and queries in my database using an ado Rs and I would also like to put 2 Combo boxes on a form and fill them with the tables & queries from my database, is this possible?


    M~
    Easier, you could fill a Combo Box with table names using a SELECT statement like that:

    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Flags)<>-2147483648 And (MSysObjects.Flags)<>2 And (MSysObjects.Flags)<>-2147483645) AND ((MSysObjects.Type) In (1,4 )))
    ORDER BY MSysObjects.Name;

    The red 4 includes the linked tables

  10. #10
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Fill a cbo with table names?

    Originally posted by hammbakka
    Easier, you could fill a Combo Box with table names using a SELECT statement like that:

    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Flags)<>-2147483648 And (MSysObjects.Flags)<>2 And (MSysObjects.Flags)<>-2147483645) AND ((MSysObjects.Type) In (1,4 )))
    ORDER BY MSysObjects.Name;

    The red 4 includes the linked tables
    In Access 97 i can't find nothing about MSysObjects, and you must now
    the value of constant.
    Saludos
    Norberto

  11. #11
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb Re: Fill a cbo with table names?

    Originally posted by Norberto
    In Access 97 i can't find nothing about MSysObjects, and you must now
    the value of constant.
    MSysObjects is a hidden table which contains information about your tables, linked tables, quries, reports, modules. If you go under tools you can make SystemObjects visible

    If you copy and past the sql in a query in Access97, it should give you all tables names. I use also Access97

  12. #12
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Fill a cbo with table names?

    Originally posted by hammbakka
    MSysObjects is a hidden table which contains information about your tables, linked tables, quries, reports, modules. If you go under tools you can make SystemObjects visible

    If you copy and past the sql in a query in Access97, it should give you all tables names. I use also Access97
    Thank
    ......But if is and hidden table i prefer not use.
    Saludos
    Norberto

  13. #13
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmmmm......OK.

    I think it's a GREAT tip. I will be using it in the future.

Posting Permissions

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