How to loop thru all queries and find which one's were affected by a table change.

dim qdf as dao.querydef, db as dao.database
set db=currentdb()

    For Each qdf In db.QueryDefs
        If Mid(qdf.Name, 1, 1) <> "~" Then
           debug.print qdf.name
        end if
   next


better would be if you know the names of the fields that was removed and put them in an array.
iterate through the array to find which query uses them with
for j=0 to ubound(arrField)
  if instr(qdf.sql, arrField(j)) then
    ...

@Ray,

better would be if you know the names of the fields that was removed

I'm dealing with a (linked SQL table) that has few hundred fields, and created a view with only half of them, so listing all fields would not be an option, what other way can I get the error message?

Thanks,
Ben

can you get a table schema of the database?

try opening the query as recordset.

Do you mean something like this?

Public Sub AllQueriesWorking()
    Dim db As Database, qdf As QueryDef, i As Integer, a As String, rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("testingquerymrb")
    On Error Resume Next
    For Each qdf In db.QueryDefs
        If Mid(qdf.Name, 1, 1) <> "~" Then
            i = i + 1
            a = qdf.Name & ": " & DCount("*", "[" & qdf.Name & "]")
            If Len(a) > 0 Then
            rs.AddNew
            rs("q1") = a
            rs.Update
            End If

        End If
    Next qdf
    ' MsgBox "finish Updating", vbInformation
End Sub

Select allOpen in new window

I'm currently running it, however not sure if that's accurate and its taking forever..

Thanks,
Ben

no, open the query as recordset, assuming qry1 is giving you Enter Parameter..  when you open it

set rs=db.openrecordset("qry1")

' see what error it will raised.

queries that uses Select *.  will not be a problem since it will ignore the fields that was removed.

Correct, except that they might be bound to controls in forms or reports or referenced in code and so their removal would indeed cause an error.

Even though it seems like Select * is safer and simpler, it interferes with this type of documentation so in the long run, it is generally better to be specific and only select the columns you actually intend to use.

set rs=db.openrecordset("qry1")

' see what error it will raised.

I get no error when opening a query referencing those fields.

Querydefs with a name starting with "~" should NOT be bypassed...

You right, in a FE app this would definitely be the case and perhaps I should apply that when I will perform the testing to my FE application, however I'm currently testing a file which only contains queries, and therefore that was put in place.

Thanks,
Ben

I ask you a question about getting the table schema of the database...

Not sure what you mean, should I post the script that creates the table in question, or you want the DSN?

Thanks,
Ben

At the moment looks like the code I posted (ID: 42099678) is doing the trick with small adjustment, see attached.

So far its still running and this looks like can go for hours..

Thanks,
Ben
Untitled.png

so, there is only one table that was change, right?
where are the queries based, on the linked table or view?
are the queries have joins ?

so, there is only one table that was change, right?

yes

where are the queries based, on the linked table or view?

there were on the table, however I just created a view with only the needed fields and linked to Access and renamed as the table name..

are the queries have joins ?

there are all types, some are based on joins.

Thanks,
Ben

Hi Experts,

I realized that this code is creating some duplicates or even 5-10 times a record per query, any idea why?

Public Sub UllQueriesWorking()
    Dim db As Database, qdf As QueryDef, i As Integer, a As String, rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("testingquerymrb")
    On Error Resume Next
    For Each qdf In db.QueryDefs
        If Mid(qdf.Name, 1, 1) <> "~" Then
            i = i + 1
            a = qdf.Name & ": " & DCount("*", "[" & qdf.Name & "]")
            If Len(a) > 0 Then
            rs.AddNew
            rs("q1") = a
            rs.Update
            End If

        End If
    Next qdf
 
End Sub

Select allOpen in new window

Thanks,
Ben

The code has several bugs plus I can't quite figure out what you intend it to do.
1. The ADO and DAO libraries share several objects so declaring objects that appear in both libraries without disambiguating them, causes reference issues.
2. errors are ignored which is what is causing the duplication.The "a= xxx" throws an error when the query is an action query and so the value of a doesn't change.  It just gets added to the table.
3. I'm not sure what counting the rows of a table has to do with the problem you are trying to solve
4. You are ignoring relevant queries by bypassing any query whose name starts with ~.

If you are serious about this documentation task, spend the money on Total Access Analyzer.

@Ben
Test this , not yet tested so, there might be some error/s so just post back for the result.
also, all fields from the query  was assumed not using alias.

Sub getQueriesAndFields()
Dim rs As dao.Recordset, rs2 As dao.Recordset
Dim db As dao.Database, qd As dao.QueryDef
Dim oSql As String, sSql As String, j As Integer, i As Integer
Dim strTable As String, strFields As String
Dim tblFldArr() As String, qryFldArr() As String, qryFld As String
Dim blnFieldFound As Boolean
Set db = CurrentDb
strTable = "NewViewName"  '<<< change with actual name
Set rs2 = db.OpenRecordset("Select * from NewViewName where 1=0")   '<<< change with actual name

    For j = 0 To rs2.Fields.Count - 1
        strFields = strFields & ";" & rs2(j).Name
    Next
    strFields = Mid(strFields, 2)
    tblFldArr = Split(strFields, ";")
For Each qd In db.QueryDefs
    oSql = qd.sql
    If InStr(oSql, strTable) Then
        sSql = Left(oSql, InStr(oSql, "From") - 1)
        sSql = Trim(Mid(sSql, InStr(sSql, " ")))
        qryFldArr = Split(sSql, ",")
        For j = 0 To UBound(qryFldArr)
            If Trim(Left(qryFldArr(j), InStr(qryFldArr(j) & ".", ".") - 1)) = strTable Then
                qryFld = Mid(qryFldArr(j), InStr(qryFldArr(j), ".") + 1)
                'look for the qryFld in tblFldArr
                blnFieldFound = False
                For i = 0 To UBound(tblFldArr)
                    If tblFldArr(i) = qryFld Then
                        
                        blnFieldFound = True
                        Exit For
                        
                    End If
                Next
                    If blnFieldFound = False Then
                       Debug.Print qd.Name
                       Debug.Print qryFld & " is not in " & strTable
                    End If                
            End If
        Next
    End If
Next
rs.Close
rs2.Close
Set db = Nothing
End Sub

Select allOpen in new window

@Rey,
I get the attached at the line rs.close

Thanks,
Ben
Untitled2.png

oops, just delete the line, rs was not use

@Pat,

The ADO and DAO libraries share several objects so declaring objects that appear in both libraries without disambiguating them, causes reference issues.

I dont have reference issues as only DAO is referenced.

2. errors are ignored which is what is causing the duplication.The "a= xxx" throws an error when the query is an action query and so the value of a doesn't change.  It just gets added to the table.

No, the else portion gets executed, see attached. (I tried that).

3. I'm not sure what counting the rows of a table has to do with the problem you are trying to solve

As you can see on attached, this is when len(a)>0 gets false, so its serving the purpose..

4. You are ignoring relevant queries by bypassing any query whose name starts with ~.

As mentioned, I will take this into consideration when testing other objects, at this point I'm only concerning about queries.

Thanks,
Ben
Untitled.png
Untitled1.png

@Ray,
Its showing me things that are on the new view, perhaps I'm not filling the values here correctly?
I have table name Employeestbl and view name View_Employeestbl, so in access I linked to the view and rename it asw Employeestbl, what should I put into those variables?

strTable = "NewViewName"  '<<< change with actual name
Set rs2 = db.OpenRecordset("Select * from NewViewName where 1=0")   '<<< change with actual name

Select allOpen in new window

Thanks,
Ben

use
asw Employeestbl

and better if you will replace the space with underscore  asw_Employeestbl

sorry it was a typo, I meant table is as "Employeestbl",
this is the code I tried.

strTable = "Employeestbl"  '<<< change with actual name
Set rs2 = db.OpenRecordset("Select * from Employeestbl where 1=0", dbReadOnly, dbSeeChanges = True)   '<<< change with actual name

Select allOpen in new window

Thanks,
Ben

which table/view is used in the queries?

Employeestbl

Thanks,
Ben

you are already using that table.
what seems to be the problem?

See here for example

SELECT DISTINCTROW Employeestbl.Title, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.AddressLine1, Employeestbl.AddressLine2, Employeestbl.State, Employeestbl.Zip, Employeestbl.City, Employeestbl.County, TovInfo.Day, TovInfo.Facility1, [Emoloyee'sFacilitiestbl].FacilitieID, Employeestbl.Workphone
FROM TovInfo RIGHT JOIN (Facilitiestbl INNER JOIN (Employeestbl INNER JOIN [Emoloyee'sFacilitiestbl] ON Employeestbl.ID = [Emoloyee'sFacilitiestbl].EmployeeID) ON Facilitiestbl.ID = [Emoloyee'sFacilitiestbl].FacilitieID) ON TovInfo.EmployeeID = Employeestbl.ID
WHERE (((Employeestbl.Title)="rn") AND ((Employeestbl.AddressLine1) Is Not Null) AND (([Emoloyee'sFacilitiestbl].FacilitieID)=182)) OR (((Employeestbl.Title)="rn") AND ((Employeestbl.AddressLine1) Is Not Null) AND (([Emoloyee'sFacilitiestbl].FacilitieID)=181)) OR (((Employeestbl.Title)="rn") AND ((Employeestbl.AddressLine1) Is Not Null) AND ((Employeestbl.Workphone) Like "(212) 318*"))
ORDER BY Employeestbl.Title DESC , Employeestbl.LastName;

Select allOpen in new window

your code is showing
myquery
Workphone
 is not in Employeestbl
while this field is there..

Thanks,
Ben

are you sure? if you open query "ZZ-Staff ColerGoldwater", does it give you a "Enter Parameter..."?

Absolutely
see attached,

Thanks,
Ben
Untitled.png

modify code as follow, after line 15  add the debug.print

    strFields = Mid(strFields, 2)

    Debug.print strFields         ' add this line, run the code once and see what was printed in the immediate window

    tblFldArr = Split(strFields, ";")


I just tested the codes and it is working fine.

you mean like this?

    For j = 0 To rs2.Fields.Count - 1
        strFields = strFields & ";" & rs2(j).Name
    Next
    strFields = Mid(strFields, 2)
Debug.Print strFields

Select allOpen in new window

I see nothing

Thanks,
Ben

add the code in sub  getQueriesAndFields()

when running step by step, the first one gives me this. (zz-...is one of the last ones, not sure how to get there)

ID;AclsExpires;AclsLetterDate;AclsSignedYN;AddFederal;AddressLine1;AddressLine2;Application;AttestationFormDate;AttestationFormSigned;AvailibilityPDate;AvailibilityPDays;AvailibilityPShifts;BackgroundAgency;BackgroundCheck;BackgroundCheckConsent;BackgroundCheckDate;BackgroundCheckResults;BclsExpires;BclsLetterDate;BclsSignedYN;Beeper;BeeperExt;BestTimeToReach;Bilingual;Birthdate;Chauncey_Date;Chauncey_Results;ChauncySanctionsDate;ChauncySanctionsResults;ChestXRayDate;ChestXRayRes;City;COIDate;CoreMandatory;CoreMandatoryDate;CorporateCompliancePolicyDate;County;CPIDate;CPR;CriminalPerApp;Degree;DocumentsYesNo;DriveYN;DrugScreen;DuplicateYN;EducationVerified;Email;Email2;EmailAddressInvalid;EmailLastVerifiedDate;EmailLastVerifiedInitial;EmailVerifiedByVendorDate;EmpCode;EmployeeStatus;EmployeeStatusDate;EmployeeStatusInitial;EmpPassword;EmpUserName;EthnicGroup;EVerifyYN;Exemptions;ExpectedGraduation;ExpectedGraduationDate;Extension;FacilityCompleted;FacilityCompletedDate;FacilityCompletedInitial;FileCompleted;
FileCompleteDate;FileCompleteInitial;FirstName;FluAttestationFormDate;FluExempt;FluShutDate;H1n1;HasSpecialtyLicenses_HHA;HasSpecialtyLicenses_ORT;HasSpecialtyLicenses_PCA;HasSpecialtyLicenses_PCT;HepBLabReports;HepBRes;HepBTiter;HepBVac;HepBWaiver;HIPAADate;HireDate;HomeAddressLine1;HomeAddressLine2;HomeCareExamsDueDate;HomeCity;HomePhone;HomeState;HomeZip;I9Complete;ID_A;ID_A_Expires;ID_B;ID_B_Expires;ID_C;Initial;IntrestedinVAFacilities;LastName;LicenseExpires;LicenseNum;LicenseNumSignedYN;LicenseState;LSFormYN;LSOnApplyingDate;MalLevelOK;MalpracticeCompany;MalpracticeExpires;MalpracticePolicyNo;MaritalStatus;MaskFitTestDate;MeaslesRubeolaDate;MeaslesRubeolaLabReports;MeaslesRubeolaRes;MeaslesRubeolaTiter;MedicalClearanceYN;MiddleInitial;MumpsDate;MumpsLabReports;MumpsRes;MumpsTiter;NalsExpires;NalsSignedYN;NoNightCalls;NotAvailChecked;NotAvailUntil;Note;NotSendEmail;NotSendMassEmail;NotSendMassTextMsg;NotSendTextMsg;NPI;OmigOigSamDate;OmigOigSamRes;Online;OP_Date;OP_Results;OrientationCheckList;Orientati
onDocumentationFacility;ORTResults;ORTVerificationDate;Paid;PaidInitial;PalsExpires;PalsLetterDate;PalsSignedYN;Performance_Eval_Comp;PhisycalPPD_Date;PhisycalPPD_Res;Phone2;Physical;PPD2ndStepDate;PPD2ndStepRes;Print_Label;RecOfEmploy;Reference1;Reference2;References1Date;References1Initial;References2Date;References2Initial;ReferredBy;ReferredByDate;ResumeYN;ResumeYNDate;ResumeYNInitial;RubellaDate;RubellaLabReports;RubellaRes;RubellaTiter;SanctionsDate;SanctionsResults;Scanner;Sex;SizeModel;SkillsChecklist;SkillsChecklistDate;SkillsChecklistScore;SkillsChecklistScore2;SkillsChecklistUnit1;SkillsChecklistUnit2;SMSProvider;SMSProviderLastVerifiedDate;SocialSecurityNumber;SSAYN;State;TaxCredit8850;TaxCredit8850YN;TBQDate;TBQResults;TestYN;Tetanus;Title;Title2;TravelerYN;Urinalysis;VaricellaDate;VaricellaLabReports;VaricellaRes;VaricellaTiter;VentTrainingClass;veteran;VolSelfID;W4;W4Date;WGDrugScreenDate;WGIDStatus;WGRecordsDate;WGRecordsInitial;WGRecordsYN;Workphone;Zip;SMSProviderInvalid

Select allOpen in new window

Thanks,
Ben

do a debug>Compile, correct any errors raised
do a compact and repair

remove the breakpoint and the Debug.print strFields line and run the codes

like I said above, just tested the codes and it is working as it should

The code looks like printing every single query and the last field name with the wording "Not in Employeestbl"

Thanks,
Ben

I dont have reference issues as only DAO is referenced.

Obviously, or you would have fixed them.  The database I had open when I pasted in the code had both DAO and ADO references so I had trouble.  Use Good Practice.  ALWAYS disambiguate.  That way if someone (or yourself) later adds code for the "other" library, existing code won't fail.

No, the else portion gets executed, see attached. (I tried that)

This line fails for action queries.  If you step through the code you will see it.  Then the code continues with the next line because of ---- On Error Resume Next   --- that is what is causing the duplicates.
a = qdf.Name & ": " & DCount("*", "[" & qdf.Name & "]")

As you can see on attached, this is when len(a)>0 gets false, so its serving the purpose

You are running a different procedure than what you posted

As mentioned, I will take this into consideration when testing other objects, at this point I'm only concerning about queries.

For other objects it doesn't matter.  It is only queries where Access helps you out and makes queries for you. The highlighted queries are QUERYDEFS that Access made for me because I put SQL strings into combos.

@Ray,
Same thing..
ZZ-Staff ColerGoldwater
Workphone
 is not in Employeestbl

Thanks,
Ben

do you have this two lines at the VERY top of the module

Option Compare Database
Option Explicit
-----------------------------

here is a sample db
there are two sub procedures  "CheckQueryFields" and "CheckQueryFields2" in module1
both are functioning properly

I added this "Employeestbl.XName"  to the query Sql statement, so it will be found not in table
db_29017263_Find_Qry_Fields.accdb

@Rey,

I didnt have option explicit, added that and tried again, but same results.

Re your attachment, I only have A2003 will have to look for a pc with later version.
But did you wrote there something else, perhaps you can just post the code.

Thanks,
Ben

use this, check your references  Tools > References and select the correct ones
db_29017263_Find_Qry_Fields.mdb

@Pat,

This line fails for action queries.  If you step through the code you will see it.  Then the code continues with the next line because of ---- On Error Resume Next   --- that is what is causing the duplicates.

Regardless it should not be creating duplicate entries per query as the for each..next loops for the next query, and there is only one insert in the loop body.
However if thats the case, how can I distinguish between select queries and action queries, so I add that logic to the code?

You are running a different procedure than what you posted

Not sure what you're referring to?

For other objects it doesn't matter.  It is only queries where Access helps you out ..

I meant to say, in this file there are not forms or reports only queries, so this is why its not necessary to include the hidden queries here.

Thanks,
Ben

@Rey,

1-See attached my checked references.
2- Also included whats the output- none of them make sense. (will remove it after you get to see it, so meanwhile dont post any comments..) I removed, let me know if you saw it.
3- The logic may be correct, however we have take in consideration the fact that there are action queries as well as Pat pointed out, or perhaps other factors that may be playing here..

Thanks,
ben
Untitled.png

Hi Experts,

I finally managed to get Rey's suggestion to work as follows

Sub getQueriesAndFields1()
Dim rs As dao.Recordset, rs2 As dao.Recordset
Dim db As dao.Database, qd As dao.QueryDef
Dim oSql As String, sSql As String, j As Integer, i As Integer
Dim strTable As String, strFields As String
Dim tblFldArr() As String, qryFldArr() As String, qryFld As String
Dim blnFieldFound As Boolean
Set db = CurrentDb
strTable = "Employeestbl"  '<<< change with actual name
Set rs2 = db.OpenRecordset("Select * from Employeestbl where 1=0", dbReadOnly)
    For j = 0 To rs2.Fields.Count - 1
        strFields = strFields & ";" & rs2(j).Name
    Next
    strFields = Mid(strFields, 2)
    tblFldArr = Split(strFields, ";")
For Each qd In db.QueryDefs
    oSql = qd.SQL
    If InStr(oSql, strTable) Then
        sSql = Left(oSql, InStr(oSql, "From") - 1)
        sSql = Trim(Mid(sSql, InStr(sSql, " ")))
        qryFldArr = Split(sSql, ",")
        For j = 0 To UBound(qryFldArr)
            If Trim(Left(qryFldArr(j), InStr(qryFldArr(j) & ".", ".") - 1)) = strTable Then
                qryFld = Trim(Mid(qryFldArr(j), InStr(qryFldArr(j), ".") + 1))
                qryFld = Replace(qryFld, "[", "")
                qryFld = Replace(qryFld, "]", "")

                'look for the qryFld in strFields
                If InStr(";" & strFields & ";", ";" & Replace(qryFld, vbCrLf, "") & ";") Then
                    'do nothing
                    Else
                        If InStr(qryFld, " as ") Then
                            If InStr(";" & strFields & ";", ";" & Replace(Mid(qryFld, 1, InStr(qryFld, " as ") - 1), vbCrLf, "") & ";") Then
                            Else
                                Debug.Print qd.Name & " - "; Replace(qryFld, vbCrLf, "") & " is not in " & strTable
                            End If
                        Else
                            Debug.Print qd.Name & " - "; Replace(qryFld, vbCrLf, "") & " is not in " & strTable
                        End If
                End If
                
            End If
        Next
    End If

Next
 
rs2.Close
Set db = Nothing
End Sub

Select allOpen in new window

Now wondering, since there is a big list and the debug window can not keep all the info, how can I run this loop in separate lists, like first only till letter "E", and then continue till letter...?

Or perhaps you have a better idea how to get the complete list?

Thanks,
Ben

instead of debug.print, you can
1. create a table with two fields  QueryName, FieldName
    - replace the  debug.print lines with append query

2. output the result to a text file.
    -

Thank you experts!