[AccessD] A2003: Deploying Stored Procs to SQL Server

Now onto the Access part ;)

I created a new blank MDB (Actually an ACCDB in 2007).

Creat a new query in design view.
Change the Query type to a passthrough query

Enter your call:

EXEC stpDistributeSproc 'YourServerNameHere', 'DBnameHere',
'C:\TestSproc.sql'


Create a new Module, paste the following in it:
Public Sub ExecuteStoredProcedureWithoutRecordset(strSQLStatement As String)
    'Execute a stored procedure without expecting a recordset

    'Variables
    Dim db As Database, qdf As QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query1")
    qdf.SQL = strSQLStatement

    'Execute the SQL statement
    DoCmd.SetWarnings False
    qdf.ReturnsRecords = False
    DoCmd.OpenQuery qdf.Name
    DoCmd.SetWarnings True

    'Reset variables
    Set qdf = Nothing
    Set db = Nothing
End Sub


Now create a form with a button on it, place the following code in the
OnClick event of the button:


Private Sub cmdDistributeSprocs_Click()
 Dim YourServerName As String, YourDataBaseName As String,
YourFilePathAndName As String, strSQL As String
 YourServerName = "MKDBSRV"
 YourDataBaseName = "SSIS_Test"
 YourFilePathAndName = "C:\TestSproc.sql"
 'strSQL = "EXEC xp_cmdshell sqlcmd -S " + YourServerName + " -d " +
YourDataBaseName + " -i " + YourFilePathAndName
 'Debug.Print (strSQL)

strSQL = "EXEC stpDistributeSproc '" & YourServerName & "', '" &
YourDataBaseName & "', '" & YourFilePathAndName & "'"
' Debug.Print (strSQL)
Call modSQLServer.ExecuteStoredProcedureWithoutRecordset(strSQL)

End Sub


if it all works out ok, you'll only need to keep a table of paths and .sql
file names and then open and loop through the record set, calling the
function as above, in the onlclick even. Let me know if you need help with
that.

I have to give credit for calling the sproc  from this link:
http://blog.ambitionit.nl/archive/2009/07/15/execute-a-stored-procedure-from-an-mdb.aspx

HTH
David