Poppy Lochridge


I'm upsizing a database from Access to a SQL backend combined with an Access frontend. Along the way, I'm trying to shift the larger queries into stored procedures, but many of our queries require variables. I'm having a hard time with the VBA to run this stored procedure, and looking for suggestions that make sense to a relative newbie to VBA.

Here's my code, which is being triggered by a button click on a form containing input boxes StartDt and EndDt for the date range:
Code Block

Private Sub btnDateFormQuery_Click()
'On Error GoTo Err_btnDateFormQuery_Click

If CreateDSNConnection("server", "database", "user", "password") Then
'// All is okay.
Else
'// Not okay.
End If

Dim rs_sp As Recordset 'Set in Global Module
Dim qdf As QueryDef 'Set in Global Module
Dim SP_SQL As String 'Set in Global Module
Dim Db As Database
Set Db = CurrentDb()
Set qdf = Db.CreateQueryDef("qry_Donors") 'Set in Global Module
qdf.ReturnsRecords = True 'Set in Global Module
qdf.ODBCTimeout = 15 'Set in Global Module
SP_SQL = "Execute sp_DonorQRY " & "'" & StartDt & "'" & ", " & "'" & EndDt & "'"
qdf.SQL = SP_SQL 'Set in Global Module

'Check Dates for errors
Call CheckDates 'Procedure to check dates
If CheckDatesErr = True Then
Exit Sub ' don't continue
End If

'Here is where we need to enter the code that will drive the query.


Set rs_sp = qdf.OpenRecordset
qdf.Close

rs_sp.MoveFirst

rs_sp.Close


Exit_btnDateFormQuery_Click:
Exit Sub

Err_btnDateFormQuery_Click:
MsgBox Err.Description
Resume Exit_btnDateFormQuery_Click

End Sub



During Debugging, when I reach the line "qdf.SQL = SP_SQL", I receive the following error:
Runtime Error '3129'
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


I've been able to copy out the value of SP_SQL and paste it into a pass-through query, and it runs beautifully. Just not having any luck trying to script it.

Thanks in advance.



Re: Executing a SQL Stored Procedure from MS Access via VBA

Cringing Dragon


If you don't have much luck getting an answer from this forum, you might have more success posting on the transact-sql forum (link below). Although your problem is about VBA, and I'm not suggesting that you're off topic here, the sql forums are more active and you might have a better chance of finding someone who can help.

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=85&SiteID=1

Also post what version of Access and SQL server you're using (it can make quite a difference).

The wording of the error implies that .SQL can't be used with execute (execute is not listed as "expected").

I don't know if I can help much, but I'll try. We're using Excel VBA to run sql queries (not Access, so they may be completely different). We don't use .SQL to pass the statement, instead we use:

.CommandType = xlCmdSql
.CommandText = Array(QueryArray)
If there is an Access VBA equivalent to this, you'd need to replace "xlCmdSql" with an Access constant. We have to pass the sql string as an array to get around a character limitation - I suspect that's an Excel VBA peculiarity. Also, we're not using stored procedures, only queries.

Good luck.