Need help editing a stored procedure with ADO

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I'm working with an Access 2000 project with an SQL Server 7 database. How
can I edit a stored procedure in the database using ADO? Is there a
collection of views/stored procedures exposed somewhere? I assume I'll need
to use the ADODB.Command object, but where do I get the Command object for
the view/procedure I want to edit?

- Don
 
I couldn't find a way to do it with ADODB and/or ADOX, but the following
example using SQLDMO worked for me.

Public Sub TestSub()

Dim objServer As SQLDMO.SQLServer
Dim objDatabase As SQLDMO.Database
Dim objSproc As SQLDMO.StoredProcedure
Dim strSQL As String

'The original sproc sorted descending, for testing purposes, we'll
change 'DESC' to 'ASC'.

strSQL = "Create Procedure " & Chr$(34) & "Ten Most Expensive Products"
& Chr$(34) & " AS " & _
"SET ROWCOUNT 10 SELECT Products.ProductName AS
TenMostExpensiveProducts, " & _
"Products.UnitPrice FROM Products ORDER BY Products.UnitPrice ASC" &
vbCrLf & "GO"

Set objServer = New SQLDMO.SQLServer
objServer.connect "YourServer", "YourUserName", "YourPassword"
Set objDatabase = objServer.Databases("NorthwindCS")
Set objSproc = objDatabase.StoredProcedures("Ten Most Expensive
Products")
objSproc.Alter (strSQL)

End Sub
 
Does anyone else know if there's anyway to do it from within Access2000's
connection to the SQL Server?
 
Don said:
Does anyone else know if there's anyway to do it from within Access2000's
connection to the SQL Server?
Click to expand...

The original poster's SQL statement will work with ADO, too:

Dim oComm As New ADODB.Command

oComm.ActiveConnection = CurrentDb.Connection
oComm.CommandText = strSQL
oComm.Execute
 
Ron is right, Don, I was doing it the hard way, you can just execute the SQL
statement. It was only my unfamiliarity with T-SQL that kept me from seeing
that ...

Public Sub TestSub2()

Dim strSQL As String

'Changed DESC to ASC last time, so this time we'll change it back.

strSQL = "ALTER PROCEDURE " & Chr$(34) & "Ten Most Expensive Products" &
Chr$(34) & " AS " & _
"SET ROWCOUNT 10 SELECT Products.ProductName AS
TenMostExpensiveProducts, " & _
"Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC"

CurrentProject.Connection.Execute strSQL, , adCmdText

End Sub
 
My original request was not to execute an SQL query against the database,
but to edit a stored procedure from the Access 200 end that existed in the
SQL Server. The code you gave me was enough for me to figure out what I
needed to do.

My only problem now is trying to figure out the values entered for a
parameterized query that is used as a form's underlying recordset. I don't
think I can do that with the SQLDMO code since, I assume, it grabs the query
directly from the SQL Server.

- Don
 
I don't understand the distinction you're making here, Don. The last code I
posted does precisely that - it edits a SQL Server stored procedure from VBA
code within an Access ADP.

You did mean edit the *definition* of the stored procedure, didn't you?
We're not talking about editing the *data* returned by the stored procedure?
 
I'll try to clear this up with an example. Say I have a stored procedure
that prompts the user for a date, then returns a recordset of all records
whose date is earlier the one entered. After I have executed the stored
procedure and have a recordset to work with, is there any possible way to
determine what the user entered in for the date, possibly from the recordset
object itself?

- Don
 
I'm beginning to wonder if this is a wind-up, as I can't see any connection
between this and the previous question, or the subject line.

In case I'm wrong, I'll ask how you're having the stored procedure prompt
the user for the parameter value? In my tests, while this worked if I just
ran the stored procedure from the Access user interface, I could not find a
way to open a recordset on a stored procedure with parameters without
assigning values to the parameters before opening the recordset.

That is, of course, how I would do it anyway - I would not let the stored
procedure prompt for the parameters, but capture them via a form before
executing the stored procedure. Reading the value of the parameters would
then not be an issue, as I'd simply read the values from the controls on the
form.
 
This was a second question was a followup question that I introduced as a
new issue about three responses into the thread after explaining that the
solution you gave me worked for the initial problem, which is why it no
longer matches the thread title. I'm sorry if this was confusing, I just
didn't want to throw a new thread into the sea of posts when I already had
someone knowledgeable noticing this one.

I have no choice but to work with a form that is bound to one of a number of
stored procedures that prompt for values. The new problem is how can I tell
what the user entered when he was prompted for the values for the stored
procedure when it was initially bound to the form? Is it even possible?
Any attempts at trying to retrieve the form's recordset's record source
simply return the name of the stored procedure, not the actual SQL query
with the arguments the user entered. I was hoping, perhaps, that there was
a collection somewhere in the recordset object that stored what the user
entered.

- Don
 
OK. Please forgive my paranoia, Don - put it down to a bad experience in
another newsgroup.

The Parameters collection is a property of the Command object, not the
Recordset object, so I tried to get at it using the ActiveCommand property
of the form's Recordset. Unfortunately, it doesn't work. The first message
box in the code snippet below displays 'True', so it would seem that the
form's Recordset has no active command. The code after that fails with an
'object variable or with block variable not set' error message. I'm not sure
what to try next. Anyone else have any suggestions?

Private Sub Command8_Click()

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set rst = Me.Recordset
Set cmd = rst.ActiveCommand
MsgBox (cmd Is Nothing)
cmd.Parameters.Refresh
MsgBox cmd.Parameters.Count
If cmd.Parameters.Count <> 0 Then
For Each prm In cmd.Parameters
MsgBox prm.Name & " = " & CStr(prm.Value)
Next prm
End If

End Sub
 
No prob.

You've gotten as far as I managed to get. I'm beginning to think this is
just one of those things that Access, in all its wisdom, decides to keep
hidden from the developer.
:-(

- Don
 
Back
Top