Solved

VBA script in MS Access calling stored procedure - Invalid Syntax

Posted on 2013-05-28
6
459 Views
Last Modified: 2013-05-28
I am trying to call a stored procedure from SQL server from Access.  I know the procedure works fine because I can run in manually, enter the parameters and it writes to the table.  I am getting an invalid syntax and assume I am getting my " ' " mixed up.  Here it is:

Dim strAssetName As String
Dim strAssetDate As String
Dim strAssetFlag As String
Dim strAssetID As String

Dim sSQL As String



strAssetName = Form_frmDashboard.txtAssetName.Value
strAssetDate = Form_frmDashboard.txtAssetDate.Value
strAssetFlag = Form_frmDashboard.txtAssetFlag.Value
strAssetID = Form_frmDashboard.txtAssetID.Value

sSQL = "spAddAssetInventory '" & strAssetName & "' & '" & strAssetDate & "' & '" & strAssetFlag & "' & '" & strAssetID & "'"

DoCmd.RunSQL sSQL

Can anyone help? ~ Thanks
0
Comment
Question by:marku24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:John Easton
ID: 39201226
Don't you need to put "EXEC" before the procedure name?
0
 
LVL 11

Expert Comment

by:John Easton
ID: 39201248
Also, I've just relised you do not seperate your parameters.  Normally I would expect the final SQL statement to look something like:
Exec  spAddAssetInventory @Asset='ABCD'

Open in new window

0
 

Author Comment

by:marku24
ID: 39201312
I get an invalid syntax if I write it like this:

sSQL = "EXEC spAddAssetInventory '" & strAssetName & "' & '" & strAssetDate & "' & '" & strAssetFlag & "' & '" & strAssetID & "'"
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 11

Accepted Solution

by:
John Easton earned 280 total points
ID: 39201325
Try this:
sSQL = "EXEC spAddAssetInventory '" & strAssetName & "', '" & strAssetDate & "', '" & strAssetFlag & "', '" & strAssetID & "'"

Open in new window

0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39201331
in your string

with the arguments are separated by &

strAssetName = "a"
strAssetDate = "b"
strAssetFlag = "c"
strAssetID = "d"

sSQL = "spAddAssetInventory '" & strAssetName & "' & '" & strAssetDate & "' & '" & strAssetFlag & "' & '" & strAssetID & "'"

Open in new window

this gives

spAddAssetInventory 'a' & 'b' & 'c' & 'd'

Open in new window


maybe

sSQL = "spAddAssetInventory '" & strAssetName & "' , '" & strAssetDate & "' , '" & strAssetFlag & "' , '" & strAssetID & "'"

Open in new window


Regards
0
 

Author Closing Comment

by:marku24
ID: 39201346
awesome!!!!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

624 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