troubleshooting Question

Access SQL stored procedure error

Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBASQL
5 Comments1 Solution25 ViewsLast Modified:
Access VBA SQL Stored Procedure error.

I can run this on the server:

Update TransactionCode Set ParameterXML = '<StockTake xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xsd:noNamespaceSchemaLocation="INVTSCDOC.XSD"><Key><WarehouseCode>XX</WarehouseCode></Key><Option><CreateBins>N</CreateBins><CreateSerials>N</CreateSerials><CreateLots>N</CreateLots><ValidateReturnsAll>N</ValidateReturnsAll><ValidateOnly>N</ValidateOnly><IgnoreOtherWhsItems>Y</IgnoreOtherWhsItems><CaptureTicketNumbers>N</CaptureTicketNumbers><TicketNumbersExist>N</TicketNumbersExist><DefaultCaptureMethod>I</DefaultCaptureMethod><XslStylesheet/></Option></StockTake>' Where TransCodeID = 23

When I run this in Access:
SQLUpdate = "Update TransactionCode Set ParameterXML = '" & XMLstr & "' Where TransCodeID = 23"

        objCmd.CommandText = "Exec usp_ExecuteSQL" & " '""" & SQLUpdate & """'"
        objCmd.ActiveConnection = adoConn_Dataswitch
        Set objRs = objCmd.Execute

I get "Incorrect syntax near '<' "

Can anyone help?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros