Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    20

    Unanswered: Dynamical Column Retrieval

    I want to be able to dynamically display a resultset based on a query supplied by a user.

    I know how to retrieve tables from an mdb using:
    Select * from MsysObjects
    where Type = 1
    and Name Not Like 'MSys%'

    But does anyone know of a way to retrieve the column names of those tables it retrieves?

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Dynamical Column Retrieval

    Originally posted by nrage
    I want to be able to dynamically display a resultset based on a query supplied by a user.

    I know how to retrieve tables from an mdb using:
    Select * from MsysObjects
    where Type = 1
    and Name Not Like 'MSys%'

    But does anyone know of a way to retrieve the column names of those tables it retrieves?
    you can replace the * with a varaible that has in it stored the column names that u want ie

    "Select " & varaible & " from MsysObjects" & _
    "where Type = 1" & _
    " and Name Not Like 'MSys%'"

    and the variable would have set in it coloumnA, coloumnB, columnC

    that may help
    Jim

  3. #3
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Create another recordset inside a loop of the tables and use the Name property.

    Something like this:

    Dim rs As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim strSQL As String
    Dim FieldCount as Integer

    strSQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE Type = 1 AND Name Not Like 'MSys%'"

    With rs
    .ActiveConnection = CurrentProject.Connection
    .Open strSQL, , adOpenKeyset, adLockOptimistic

    Do Until rs.EOF Or rs.BOF
    Debug.Print "Table Name: " & rs!Name
    strSQL = "SELECT * FROM " & rs!Name
    With rs2
    .ActiveConnection = CurrentProject.Connection
    .Open strSQL, , adOpenKeyset, adLockOptimistic
    For FieldCount = 0 To rs2.Fields.Count - 1
    Debug.Print "Field Name: " & rs2.Fields(FieldCount).Name
    Next FieldCount
    rs2.Close
    End With
    rs.MoveNext
    Loop

    End With
    rs.Close
    Set rs = Nothing
    Set rs2 = Nothing

    -Matt

Posting Permissions

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