Running a stored proc in sql 2005 from Access using VBA

  • Thread starter Thread starter bmd473
  • Start date Start date
B

bmd473

I am trying to run a stored procedure from Access using the following
code, I am receiving a "Syntax Error or Access Violation" when I try
to run it. If I paste the code directly in to sql and run it, it
works fine. Any help would be appreciated. I want to do this
complete through code and not using a passthrough query if possible.
Thanks

Private Sub Command0_Click()

Dim conn As String
conn = "Provider=SQLOLEDB;" & _
"Data Source = servername;" & _
"Initial Catalog=dbname;" & _
"Integrated Security=SSPI"
Dim ds As New ADODB.Command
Dim sql As String
sql = "ForTim"
Dim parm As String
parm = "jill"
With ds
..ActiveConnection = conn
..CommandText = sql & " '" & parm & "'"
..CommandType = adCmdStoredProc
..Execute
End With

End Sub
 
First, don't use the WITH instruction or the Dim ... As New with complex
objects such as ADO. Second, as you are adding parameter after the name of
the stored procedure (SP), it's no longer a SP call but a text command. It
would also be a good idea to EXEC before it:

Dim ds as ADODB.Command
Set ds = New ADODB.Command

' Remove the WITH:
ds.ActiveConnection = conn
ds.CommandText = "EXEC " & sql & " '" & parm & "'"
ds.CommandType = adCmdText
ds.Execute

Also, if you are using ADP - as this is the subject of this newsgroup - then
you can use the CurrentProject.Connection object as your connection to the
database. If this is the case, you should add a Set command in order to
reuse the same object instead of creating a new one:

Set ds.ActiveConnection = CurrentProject.Connection

Finally, you don't say if this SP will return a resultet (or recordset) or
not. If yes, then you should Dim an ADODB.Recordset object and use it to
capture the result; if not, then you should add the parameter
adExecuteNoRecords to the Execute command:

ds.Execute , , adExecuteNoRecords

or:

Dim rs as ADODB.recordset
...
Set rs = ds.Execute

In the case of ADP, a shorter way would be to simply call the .Execute
method of the CurrentProjectConnection object:

CurrentProject.Connection.Execute SQL, , adCmdText + adExecuteNoRecords

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
iletiiuuiusinde sunu yazdi said:
I am trying to run a stored procedure from Access using the following
code, I am receiving a "Syntax Error or Access Violation" when I try
to run it. If I paste the code directly in to sql and run it, it
works fine. Any help would be appreciated. I want to do this
complete through code and not using a passthrough query if possible.
Thanks

Private Sub Command0_Click()

Dim conn As String
conn = "Provider=SQLOLEDB;" & _
"Data Source = servername;" & _
"Initial Catalog=dbname;" & _
"Integrated Security=SSPI"
Dim ds As New ADODB.Command
Dim sql As String
sql = "ForTim"
Dim parm As String
parm = "jill"
With ds
.ActiveConnection = conn
.CommandText = sql & " '" & parm & "'"
.CommandType = adCmdStoredProc
.Execute
End With

End Sub
Click to expand...
 
I am trying to run a stored procedure from Access using the following
code, I am receiving a "Syntax Error or Access Violation" when I try
to run it. If I paste the code directly in to sql and run it, it
works fine. Any help would be appreciated. I want to do this
complete through code and not using a passthrough query if possible.
Thanks

Private Sub Command0_Click()

Dim conn As String
conn = "Provider=SQLOLEDB;" & _
"Data Source = servername;" & _
"Initial Catalog=dbname;" & _
"Integrated Security=SSPI"
Dim ds As New ADODB.Command
Dim sql As String
sql = "ForTim"
Dim parm As String
parm = "jill"
With ds
.ActiveConnection = conn
.CommandText = sql & " '" & parm & "'"
.CommandType = adCmdStoredProc
.Execute
End With

End Sub
Click to expand...
 
I am trying to run a stored procedure from Access using the following
code, I am receiving a "Syntax Error or Access Violation" when I try
to run it. If I paste the code directly in to sql and run it, it
works fine. Any help would be appreciated. I want to do this
complete through code and not using a passthrough query if possible.
Thanks

Private Sub Command0_Click()

Dim conn As String
conn = "Provider=SQLOLEDB;" & _
"Data Source = servername;" & _
"Initial Catalog=dbname;" & _
"Integrated Security=SSPI"
Dim ds As New ADODB.Command
Dim sql As String
sql = "ForTim"
Dim parm As String
parm = "jill"
With ds
.ActiveConnection = conn
.CommandText = sql & " '" & parm & "'"
.CommandType = adCmdStoredProc
.Execute
End With

End Sub
Click to expand...
 
Back
Top