Avatar of bfuchs
bfuchs
Flag for United States of America asked on

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

Hi Experts,

I am trying to get rid of many fields of a table that are not in use anymore, and we have a database of hundreds of queries and would like to know which of them would get affected by this change.

How can I loop all queries and get a list of all of them that are using one of those removed fields, which in that case when opening normally Access will display a message "enter parameter value.."

I guess something like the below for looping..
    For Each qdf In db.QueryDefs
        If Mid(qdf.Name, 1, 1) <> "~" Then

Open in new window

DatabasesMicrosoft AccessVBA

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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
    ...
bfuchs

ASKER
@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
Rey Obrero (Capricorn1)

can you get a table schema of the database?

try opening the query as recordset.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
bfuchs

ASKER
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

Open in new window

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

Thanks,
Ben
Rey Obrero (Capricorn1)

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.
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

queries that uses Select *.  will not be a problem since it will ignore the fields that was removed.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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.
bfuchs

ASKER
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
Rey Obrero (Capricorn1)

I ask you a question about getting the table schema of the database...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bfuchs

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

Thanks,
Ben
bfuchs

ASKER
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
Rey Obrero (Capricorn1)

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 ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
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
bfuchs

ASKER
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

Open in new window


Thanks,
Ben
PatHartman

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rey Obrero (Capricorn1)

@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

Open in new window

bfuchs

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

Thanks,
Ben
Untitled2.png
Rey Obrero (Capricorn1)

oops, just delete the line, rs was not use
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
@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
bfuchs

ASKER
@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

Open in new window


Thanks,
Ben
Rey Obrero (Capricorn1)

use
asw Employeestbl

and better if you will replace the space with underscore  asw_Employeestbl
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
bfuchs

ASKER
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

Open in new window

Thanks,
Ben
Rey Obrero (Capricorn1)

which table/view is used in the queries?
bfuchs

ASKER
Employeestbl

Thanks,
Ben
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

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

ASKER
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;

Open in new window

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

Thanks,
Ben
Rey Obrero (Capricorn1)

are you sure? if you open query "ZZ-Staff ColerGoldwater", does it give you a "Enter Parameter..."?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
bfuchs

ASKER
Absolutely
see attached,

Thanks,
Ben
Untitled.png
Rey Obrero (Capricorn1)

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.
bfuchs

ASKER
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

Open in new window

I see nothing

Thanks,
Ben
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

add the code in sub  getQueriesAndFields()
bfuchs

ASKER
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

Open in new window


Thanks,
Ben
Rey Obrero (Capricorn1)

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
bfuchs

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

Thanks,
Ben
PatHartman

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.AccessQuerydefs.JPG
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

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

Thanks,
Ben
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

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

Option Compare Database
Option Explicit
-----------------------------
Rey Obrero (Capricorn1)

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
bfuchs

ASKER
@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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rey Obrero (Capricorn1)

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

ASKER
@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
bfuchs

ASKER
@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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
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

Open 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
Rey Obrero (Capricorn1)

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.
    -
bfuchs

ASKER
Thank you experts!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy