Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Newly added Column in Table that Query is not pulling

Posted on 2014-04-19
9
Medium Priority
?
587 Views
Last Modified: 2014-04-19
Hello,

is there a way to compare a query definition to a table definition and tell if the query is missing any newly added column from the table.

Reason - i have a query that pulls all the columns from that same table but renames all of them to increase readability. I cant store the readable columns as headers in the original table as they have special characters and spaces in them.

so the only way is to >> create  query to pull those renamed columns by the column names i need.

now bad news - if a new column is added to table, the query has to be updated to include that new column too

so is there  query or vba to tell me what columns are in table1 which are not getting pulled by query?


Thank you
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40010383
<<so is there  query or vba to tell me what columns are in table1 which are not getting pulled by query?>>

 That would be doable in VBA.   A TableDef(), QueryDef, and Recordset all have a fields collection which allows you to look at the fields. So if you went to the debug window and did:

? CurrentDB().TableDefs("myTable").Fields(0).Name

 You would get the field name of the first filed in that table.

 Below is some example code from the online help with working with the fields collection and the field object.

 That should get your started.

Jim.

Sub FieldX()

      Dim dbsNorthwind As Database
      Dim rstEmployees As Recordset
      Dim fldTableDef As Field
      Dim fldQueryDef As Field
      Dim fldRecordset As Field
      Dim fldRelation As Field
      Dim fldIndex As Field
      Dim prpLoop As Property

      Set dbsNorthwind = OpenDatabase("Northwind.mdb")
      Set rstEmployees = _
            dbsNorthwind.OpenRecordset("Employees")

      ' Assign a Field object from different Fields
      ' collections to object variables.

Set fldTableDef = _
            dbsNorthwind.TableDefs(0).Fields(0)
      Set fldQueryDef =dbsNorthwind.QueryDefs(0).Fields(0)
      Set fldRecordset = rstEmployees.Fields(0)
      Set fldRelation =dbsNorthwind.Relations(0).Fields(0)
      Set fldIndex = _
            dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

      ' Print report.
      FieldOutput "TableDef", fldTableDef
      FieldOutput "QueryDef", fldQueryDef
      FieldOutput "Recordset", fldRecordset
      FieldOutput "Relation", fldRelation

FieldOutput "Index", fldIndex

      rstEmployees.Close
      dbsNorthwind.Close

End Sub

Sub FieldOutput(strTemp As String, fldTemp As Field)
      ' Report function for FieldX.

      Dim prpLoop As Property

      Debug.Print "Valid Field properties in " & strTemp

      ' Enumerate Properties collection of passed Field
      ' object.
      For Each prpLoop In fldTemp.Properties
            ' Some properties are invalid in certain
            ' contexts (the Value property in the Fields

' collection of a TableDef for example). Any
            ' attempt to use an invalid property will
            ' trigger an error.
            On Error Resume Next
            Debug.Print "    " & prpLoop.Name & " = " & _
                  prpLoop.Value
            On Error GoTo 0
      Next prpLoop

End Sub
0
 
LVL 58
ID: 40010387
I should add that the fields collection also has a Count Property, so you can easily do a loop on the table, store all the field names, then do the same for the querydef and figure out if all fields are referenced or not.

Jim.
0
 

Author Comment

by:Rayne
ID: 40010409
Thank you Jim,

This is Awesome :)
I just put on my developer hat...I am going through it
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Rayne
ID: 40010428
this is beautiful and i can customize it to perfection, thank you again Jim
You are a life saver
0
 
LVL 58
ID: 40010431
No problem.

Jim.
0
 

Author Comment

by:Rayne
ID: 40010437
Hello Jim,

i was able to pull in all the actual field names from the original table. Now i twicked the loop a bit so that it goes through each field in query and tells me their actual Source names (Not the renamed column names) - but its not doing that. Its pulling renamed column names only

Sub FieldX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim fldTableDef As Field
    Dim fldQueryDef As Field
    Dim fldRecordset As Field
    Dim fldRelation As Field
    Dim fldIndex As Field
    Dim prpLoop As Property

    Set dbsNorthwind = CurrentDb
    'open query
    Set rstEmployees = _
    dbsNorthwind.OpenRecordset("qryRenamed")

    'display query columns-
    For Each flds In rstEmployees.Fields
        Set fldTableDef = flds
        ' Print report.
        FieldOutput "TableDef", fldTableDef
    Next flds

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Open in new window

0
 

Author Comment

by:Rayne
ID: 40010438
any ideas to pull the actual field names and not the renamed field names from the query?

tHank you
0
 

Author Comment

by:Rayne
ID: 40010440
further i modified the diplay function a bit like this to only show field name

Sub FieldOutput(strTemp As String, fldTemp As Field)
' Report function for FieldX.

    Dim prpLoop As Property

    'Debug.Print "Valid Field properties in " & strTemp

    ' Enumerate Properties collection of passed Field
    ' object.
    For Each prpLoop In fldTemp.Properties
        ' Some properties are invalid in certain
        ' contexts (the Value property in the Fields

        ' collection of a TableDef for example). Any
        ' attempt to use an invalid property will
        ' trigger an error.
        If LCase(prpLoop.Name) = "name" Then
            On Error Resume Next
            'Debug.Print "    " & prpLoop.Name & " = " & _
             prpLoop.Value
            Debug.Print prpLoop.Value
            Exit For
            On Error GoTo 0
        End If
    Next prpLoop
    '    Stop
End Sub

Open in new window

0
 

Author Comment

by:Rayne
ID: 40010491
I got it, thanks
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question