How to add a store procedure using vba

[Dirk Goldgar]

In

I need to create an Access stored procedure using vba. First of all
delete the one if already exist. I can take care of the dynamic
generate Sql statement as follow:

Sub AddQuery()

Dim strSql As String
Dim strSql1 As String
Dim strSql2 As String
Dim i As Integer

strSql1 = "SELECT Status, Branch, Method"
For i = 1 To Me.Candidate
strSql = ", Count(F" & i & ") AS CountOfF" & i
strSql1 = strSql1 + strSql
Next i
strSql2 = strSql & " FROM Scantron GROUP BY Status, Branch, Method
ORDER BY Status, Branch, Method;"

End Sub

Thanks

By stored procedure, I gather you are talking about a stored query.
Here's the DAO method:

' ... assuming strSQL contains the SQL of the query.
With CurrentDb

On Error Resume Next
.QueryDefs.Delete "qryYourQueryName"
Select Case Err.Number
Case 0, 3265
' deleted, or didn't exist -- ignore
Case Else
' unexpected error
MsgBox Err.Description, vbExclamation, "Error " &
Err.Number
On Error GoTo 0 ' or your error-handler
Exit Sub
End Select

' Restore normal error-handling.
On Error GoTo 0 ' or your error-handler

.CreateQueryDef "qryYourQueryName", strSQL

End With

Here's the ADO method:

With CurrentProject.Connection

On Error Resume Next
.Execute "DROP PROCEDURE qryYourQueryName"
On Error GoTo 0 ' or your error-handler
.Execute _
"CREATE PROCEDURE qryYourQueryName AS " & strSQL

End With

Note that I left out the close examination of any error that might be
returned by the DROP PROCEDURE statement. If the procedure didn't
exist, the error raised would be different from that returned in the DAO
version.

 

[Paul]

I need to create an Access stored procedure using vba. First of all delete
the one if already exist. I can take care of the dynamic generate Sql
statement as follow:

Sub AddQuery()

Dim strSql As String
Dim strSql1 As String
Dim strSql2 As String
Dim i As Integer

strSql1 = "SELECT Status, Branch, Method"
For i = 1 To Me.Candidate
strSql = ", Count(F" & i & ") AS CountOfF" & i
strSql1 = strSql1 + strSql
Next i
strSql2 = strSql & " FROM Scantron GROUP BY Status, Branch, Method ORDER
BY Status, Branch, Method;"

End Sub

Thanks