Check to see if stored proc exists

[itdamon]

Hi,

I'm trying to check if an Access query (stored procedure) exists in a database, and:

If Stored_Proc exists:
run the query
Else
run a different query (pardon my pseudo code)

I can connect to the DB fine with ADO, I can run the query fine with: Set oRS=MyDatabase.Execute(sql), but I can't figure this one out.

I've tried several versions of:

SELECT name FROM sysobjects WHERE name = 'InsertAccount' AND type = 'P'

But I can't seem to make this work right.

 

[hithere]

which version of Access are you running? In the XP version, you can build a "project" and click on the query view and it will show all the sp's that are on the server (kinda neat!). if not, you can have your dba create a "view" of the sp's and connect to it as though it were a table (xp/97). those would give you visibility of whether it was there or not.

not sure I understand the 2nd part of the message. is that what the sp is supposed to do? or is that the query you want run if the sp doesn't exist or what?
mb

"Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!"
Marvin the Martian

 

[itdamon]

Perhaps this will help

Here are my variables:

sql = "exec"
sql = sql & " " & SCuser (SCuser is a stored query in Access 2000)

<%

' Construct path to database
sPath = Request.ServerVariables(&quot;Path_Translated&quot
sPath = Left(sPath,InStrRev(sPath,&quot;\&quot) & &quot;../lists/lists.mdb&quot;

' Open Connection & Recordset
set oSample = Server.CreateObject(&quot;ADODB.Connection&quot
oSample.Open _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Persist Security Info=TRUE;&quot; & _
&quot;Data Source=&quot; & sPath, &quot;Admin&quot;, &quot;&quot;

' Check for default...
PROPERTY = Trim(Request(&quot;SiteName&quot)

'THIS IS WHERE I NEED HELP!!!!! The following code executes a stored query in Access 2000, but now I need to somehow test the variable 'SCuser' to see if it exists in the database. If it exists, I want to run the sql query, if it doesn't, I need to execute a different (default) query. Any ideas?

Set oRS=oSample.Execute(sql)

DO WHILE NOT oRS.EOF
mSelected = &quot;&quot;
IF PROPERTY=trim(oRS(&quot;SiteName&quot) then mSelected=&quot; SELECTED&quot;
%>
<option<%=mSelected%>><%=oRS(&quot;SiteName&quot%></option>
<%
oRS.MoveNext
Loop
%>