Execute SQL Procedure in Ms Access

[imfarhan]

<TABLE cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TD class=smalltxt vAlign=top>Hi
The procedure called from SQL database and would like to run through Ms Access
In Ms Access I already running Queries using following function

'RRH - Executes a specified list of queries
Private Function ExecuteQueryBatch(TaskQCode As String) As ReturnStatus
Dim sSQL As String
Dim rsQryList As DAO.Recordset
Dim uStatus As ReturnStatus
Dim iCount As Integer, iMax As Integer

On Error GoTo Err_Handler

'Check if there are any queries to process
sSQL = "SELECT * FROM QueryList WHERE QueryCode = '" & TaskQCode & "' AND QueryBatch <> 0 ORDER BY QueryBatchOrder"
Set rsQryList = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

If rsQryList.RecordCount = 0 Then
ExecuteQueryBatch = NothingToDo
Exit Function

End If
rsQryList.MoveLast
rsQryList.MoveFirst
iMax = rsQryList.RecordCount

'Execute each query
For iCount = 1 To iMax
uStatus = ExecuteQuery(rsQryList!QueryName)

If uStatus = AllOK Then
LogUpdate " Query execution SUCCESSFUL (" & rsQryList!QueryName & ")"

If rsQryList!QueryName = "PMF Conversions Mk-Tbl" Then
'alternative is to create new field in querylist table called "ProcName" and call that procedure if not null
Call PMF_Conversion
Else
LogUpdate " Query execution FAILED (" & rsQryList!QueryName & ")"
Exit For
End If

rsQryList.MoveNext

Next

ExecuteQueryBatch = uStatus

Private Function ExecuteQuery(sQueryName As String) As ReturnStatus
Dim QryDB As DAO.Database

On Error GoTo Err_Handler


Set QryDB = DBEngine.Workspaces(0).OpenDatabase(sCOREDB, False, False)

DoCmd.SetWarnings False
QryDB.Execute sQueryName
DoCmd.SetWarnings True
DoEvents

The above function execute all the SQLs from the table, but now I would like to run/execute the
procedure I've copied the procedure in the same table where I have all queries the queries table also keep the field QRY_Type i.e. "QRY","QBT" and so on , QRY=Simple SQL query QBT= MAketable,update, append queries
Now I'm thinking I think I can control by using IF like sQueryName = "ProcedureQuery" THEN ...
OR IF the Query type "PRO" THEN call specific function... to run the procedure but I don't know how?

I hope it does make sense to you.

Many thanks for you help
Farhan





</TD></TR></TBODY></TABLE>

 

[xenou]

Possibly:

'Execute each query
For iCount = 1 To iMax
    [COLOR="Red"]If Left(rsQryList!QueryName, 3) = "QRY" Then[/COLOR]
        uStatus = ExecuteQuery(rsQryList!QueryName)
        
        If uStatus = AllOK Then
            LogUpdate " Query execution SUCCESSFUL (" & rsQryList!QueryName & ")"
        
        If rsQryList!QueryName = "PMF Conversions Mk-Tbl" Then
            'alternative is to create new field in querylist table called "ProcName" and call that procedure if not null
            Call PMF_Conversion
        Else
            LogUpdate " Query execution FAILED (" & rsQryList!QueryName & ")"
            Exit For
        End If
    [COLOR="Red"]End If[/COLOR]
    rsQryList.MoveNext
Next

This gets a little tricky though embedding such logic in your code. I would probably prefer to change the query that creates the recordset in the first place:

SELECT * FROM QueryList WHERE Left(QueryName,3) In ("QRY") ...

OR

SELECT * FROM QueryList WHERE Left(QueryName,3) In ("QBT") ...

OR

SELECT * FROM QueryList WHERE QueryName In ("Query1", "Query2") ...

I.e. - run queries by type or name from your list ... all you need to do is change the record source (your sSQL).