<div>
This CommandText should be OK:
<pre><code class="code-1 nolinks" id="code-20-43077078-1">objCmd.CommandText = &quot;Exec usp_ExecuteSQL&quot; &amp; &quot; '&quot; &amp; SQLUpdate &amp; &quot;'&quot;
</code></pre>
BUT I don't know data type of the TransactionCode column and I also don't know exact content of the XMLstr variable.
</div>

<div>
Put a breakpoint here : 
objCmd.ActiveConnection = adoConn_Dataswitch 
add a watch or use Immediate windows and check exactly what the objCmd.CommandText carries 
Easiest way to be 100% certain just copy paste the result from &nbsp;Immediate to SSMS and check if it works as it should.
</div>

IT Technical

HKFuey

<div>
pcelba I did try it that way with same result 
 
John, tried that and it looks like the single quotes needed replacing with double. I'm now getting a permission error but it's a step forward!
</div>

<div>
Thanks all, going to log another issue for the permissions error. 
 
A
</div>

<div>
<div class="content wysiwyg-content">
Access VBA SQL Stored Procedure error. 
 
I can run this on the server: 
 
Update TransactionCode Set ParameterXML = '&lt;StockTake xmlns:xsd=&quot;<a href="http://www.w3.org/2001/XMLSchema-instance" rel="ugc">http://www.w3.org/2001/XMLSchema-instance</a>&quot; xsd:noNamespaceSchemaLocation=&quot;INVTSCDOC.XSD&quot;&gt;&lt;Key&gt;&lt;WarehouseCode&gt;XX&lt;/WarehouseCode&gt;&lt;/Key&gt;&lt;Option&gt;&lt;CreateBins&gt;N&lt;/CreateBins&gt;&lt;CreateSerials&gt;N&lt;/CreateSerials&gt;&lt;CreateLots&gt;N&lt;/CreateLots&gt;&lt;ValidateReturnsAll&gt;N&lt;/ValidateReturnsAll&gt;&lt;ValidateOnly&gt;N&lt;/ValidateOnly&gt;&lt;IgnoreOtherWhsItems&gt;Y&lt;/IgnoreOtherWhsItems&gt;&lt;CaptureTicketNumbers&gt;N&lt;/CaptureTicketNumbers&gt;&lt;TicketNumbersExist&gt;N&lt;/TicketNumbersExist&gt;&lt;DefaultCaptureMethod&gt;I&lt;/DefaultCaptureMethod&gt;&lt;XslStylesheet/&gt;&lt;/Option&gt;&lt;/StockTake&gt;' Where TransCodeID = 23 
 
When I run this in Access: 
SQLUpdate = &quot;Update TransactionCode Set ParameterXML = '&quot; &amp;&nbsp;XMLstr &amp;&nbsp;&quot;' Where TransCodeID = 23&quot; 
 
&nbsp; &nbsp; &nbsp; &nbsp; objCmd.CommandText = &quot;Exec usp_ExecuteSQL&quot; &amp;&nbsp;&quot; '&quot;&quot;&quot; &amp;&nbsp;SQLUpdate &amp;&nbsp;&quot;&quot;&quot;'&quot; 
&nbsp; &nbsp; &nbsp; &nbsp; objCmd.ActiveConnection = adoConn_Dataswitch 
&nbsp; &nbsp; &nbsp; &nbsp; Set objRs = objCmd.Execute 
 
I get &quot;Incorrect syntax near '&lt;' &quot; 
 
Can anyone help?
</div>
</div>

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?

Access SQL stored procedure error

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.