Rayne
asked on
Newly added Column in Table that Query is not pulling
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Jim,
This is Awesome :)
I just put on my developer hat...I am going through it
This is Awesome :)
I just put on my developer hat...I am going through it
ASKER
this is beautiful and i can customize it to perfection, thank you again Jim
You are a life saver
You are a life saver
No problem.
Jim.
Jim.
ASKER
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
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
ASKER
any ideas to pull the actual field names and not the renamed field names from the query?
tHank you
tHank you
ASKER
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
ASKER
I got it, thanks
Jim.