How do I create an MS SQL Server stored procedure from MS Access V

[Guest]

Access 2002 VBA provides easy ways to delete, copy, and run an MS SQL Server
stored procedure, via DoCmd. However, I cannot find any way to CREATE an MS
SQL Server stored procedure from Access 2002. Here's what I'm trying now, but
it doesn't work:

CurrentProject.Connection.Execute "CREATE PROCEDURE proc_name AS SELECT *
etc. etc."

NOTE: The "CREATE PROCEDURE proc_name ..." statement works to successfully
create the stored procedure when pasted into Enterprise Manager. Once
created, the stored procedure runs successfully. Options I've tried with the
..Execute command include adCmdText, adCmdStoredProc, adCmdUnknown, and
adAsyncExecute.

Any help you could give would be greatly appreciated!

 

[Brendan Reynolds]

MDB or ADP?

If it's an ADP, then you can disregard the following, but if it's an MDB,
then the problem is the use of CurrentProject.Connection, which in an MDB is
a connection to the local JET database, not to the SQL Server database.
You'd need something more like ...

Public Sub TestCreateSproc()

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=(local)"
.Open
.Execute "CREATE PROCEDURE MyTestSproc AS SELECT * FROM Books"
.Close
End With

End Sub