We help IT Professionals succeed at work.
Private
Troubleshooting Question

looping thru all table fields and constructing a SQL where string

66 Views
Last Modified: 2020-09-07
Hi Experts,

I am looking to dynamically construct a SQL string based on two tables as follows.

Having two tables with 90% same field names, it should join by PK and have a where clause as below
 nz(a.fieldA)<> nz(b.FieldA)
or
 nz(a.fieldB)<> nz(b.FieldB)
or
 nz(a.fieldC)<> nz(b.FieldC)

See example
SELECT S.Client_Last_Name, S.Visit_Date
FROM Skilled_Nursing_Visit_Note AS S INNER JOIN V_Visit_Note_Export AS V ON S.SNV_ID = V.SNV_ID
WHERE (((S.Client_Last_Name)<>Nz([v].[Client_Last_Name]))) OR (((S.Visit_Date)<>Nz([v].[visit_date])));

Here is the logic how it should loop thru all fields of table A and construct the SQL.
https://www.experts-exchange.com/questions/29165999/Build-SQL-statement-from-a-list-of-fields.html 
https://www.experts-exchange.com/questions/29104646/looping-thru-all-table-fields-and-constructing-a-SQL-update-string.html#a42599149 

In case there is no such a field in tableB it should ignore and continue further with the loop.
Query should return all not matching records.
Would also want to see which field/s are not matching..

Thanks

Comment
Watch Question

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Hi John,

You was the one who originated the idea, no-:)

Tried running but for some reason the resume next does not work.

See attached.

Thanks

Untitled.png
CERTIFIED EXPERT

Author

Commented:
Tried the below, nothing different.

Select Tools | Options in the Visual Basic Editor.
Activate the General tab.
In the Error Trapping section, make sure that "Break on All Errors" is NOT selected ("Break on Unhandled Errors" is the preferred option).


John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
In the immediate window you get why the code is not running, you need the dbSeeChanges.
CERTIFIED EXPERT

Author

Commented:
It looks like getting stuck by the following
If Len(rst2.Fields(fldName).Name) > 0 Then
as no such field exists in second table.
CERTIFIED EXPERT

Author

Commented:
In immediate window I get the following, which means both recordsets are fine.
?rst1.recordcount
 1
?rst2.recordcount
 1
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Have you tried it step by step ?
Just create 2 dummy tables with a couple of fields and try the code.This should work ...after that "adjust" the code to your case
CERTIFIED EXPERT

Author

Commented:
Hi,
Attached you have it (couldn't be more simpler), can you make it work?
thanks
Database9.zip
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Some minor modifications, i reckon it just needs a little workDatabase9.accdb
CERTIFIED EXPERT

Author

Commented:
I'm having exact same issue as attached above.
CERTIFIED EXPERT

Author

Commented:
When I get to the fourth round on the loop, and column does not exists it popups an error message.
Not happening by you?
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
if you run the module it should just return

CERTIFIED EXPERT

Author

Commented:
It's getting stuck by the column I as mentioned.
see attached.
Untitled.png
CERTIFIED EXPERT

Author

Commented:
I mean by column N, which only exists on table1.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
On my system it runs just fine...i open the application , run the function , msgbox pops up.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
It seems that there is a small mistype error
change to this
Dim rst1, rst2 As DAO.Recordset
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Dim rst1, rst2 As DAO.Recordset

Open in new window

Only rst2 is a Recordset.  Don't think it will functionally matter, but will affect Intellisense.

You have a trailing comma character in your SQL.  This will certainly affect the validity of the SQL.
CERTIFIED EXPERT

Author

Commented:
Hi Experts,
you're not addressing the problem I've been encountering..
The problem is that on error resume next is not functioning in this case.
Have attached twice screenshot of this issue.
@John,
can you please post what does the immediate window have for the strSQL string after fldname has been assigned to "N"
See attached.
Thanks
Untitled.png
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

I got your error when i switched from Unhandled errors

Check the rest tabs maybe something is different


Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Personally, rather than Resume Next, I would have inserted a line label at row 21 like:

NextField:

Then would have used "Resume NextField" in the error handler

CERTIFIED EXPERT

Author

Commented:
Thanks Experts!

Guess I had to switch from unhandled to break on all errors and then switch back to unhandled in order to take effect.

Will open now a new thread on the missing parts.
CERTIFIED EXPERT

Author

Commented:

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.