asked on

VBA script in MS Access calling stored procedure - Invalid Syntax

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

John Easton

Don't you need to put "EXEC" before the procedure name?

John Easton

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

marku24

ASKER

I get an invalid syntax if I write it like this:

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

ASKER CERTIFIED SOLUTION

John Easton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

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

marku24

ASKER

awesome!!!!