Solved

Acces Query Window Updates But VBA execute of same SQL Statement doesn't update WHY?

Posted on 2016-09-12
2
31 Views
Last Modified: 2016-09-13
Why is it that when i copy the following sql statement from the immediate window and paste it into the query window and hit run query, the query will update my table. But the this same query will not update my table if i use the currentproject.connection.execute method.

"UPDATE CreditCardDetail SET CreditCardDetail.Vendor = 'ZFUEL', CreditCardDetail.GlAccount = '6110.00' WHERE (((CreditCardDetail.ImportDate) Is Null) AND ((CreditCardDetail.CompanyName)='PBI') AND ((CreditCardDetail.YearMonth)='2016_08') AND ((CreditCardDetail.CreditCard)='Amex') AND ((CreditCardDetail.Description) Like '*7-ELEVEN*' Or (CreditCardDetail.Description) Like '*CHEVRON*' Or (CreditCardDetail.Description) Like '*CITY EXPRESS*' Or (CreditCardDetail.Description) Like '*CIRCLE K*' Or (CreditCardDetail.Description) Like '*MAVERIK*' Or (CreditCardDetail.Description) Like '*PILOT*' Or (CreditCardDetail.Description) Like '*REBEL*' Or (CreditCardDetail.Description) Like '*SHELL OIL*' Or (CreditCardDetail.Description) Like '*TEXACO*' Or (CreditCardDetail.Description) Like '*UNION 76*'));"
0
Comment
Question by:jb702
2 Comments
 
LVL 13

Accepted Solution

by:
John Tsioumpris earned 500 total points
ID: 41795544
how about running
Docmd.RunSQL yourSQL

Open in new window


I formatted your query in order to be easier to read
UPDATE CreditCardDetail
SET CreditCardDetail.Vendor = 'ZFUEL'
	,CreditCardDetail.GlAccount = '6110.00'
WHERE (
		((CreditCardDetail.ImportDate) IS NULL)
		AND ((CreditCardDetail.CompanyName) = 'PBI')
		AND ((CreditCardDetail.YearMonth) = '2016_08')
		AND ((CreditCardDetail.CreditCard) = 'Amex')
		AND (
			(CreditCardDetail.Description) LIKE '*7-ELEVEN*'
			OR (CreditCardDetail.Description) LIKE '*CHEVRON*'
			OR (CreditCardDetail.Description) LIKE '*CITY EXPRESS*'
			OR (CreditCardDetail.Description) LIKE '*CIRCLE K*'
			OR (CreditCardDetail.Description) LIKE '*MAVERIK*'
			OR (CreditCardDetail.Description) LIKE '*PILOT*'
			OR (CreditCardDetail.Description) LIKE '*REBEL*'
			OR (CreditCardDetail.Description) LIKE '*SHELL OIL*'
			OR (CreditCardDetail.Description) LIKE '*TEXACO*'
			OR (CreditCardDetail.Description) LIKE '*UNION 76*'
			)
		);

Open in new window

0
 

Author Comment

by:jb702
ID: 41796055
Yes, that worked, but why?. Ive used the currentproject.connection.execute method for years and never had a problem. What's the difference between the two.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

862 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now