Procedure/Query Confusion

[Morris]

I've created a procedure/query in an Access 2000 database using the
following code:
<%
strAppend = "ProcName"
strSQL = "Create Procedure [qry" & strAppend & "] AS Select * From Stories
ORDER BY StoryID DESC;"
cn.execute(strSQL)
%>

I know I have successfully created the procedure/query, because when I run
the code again, it gives me the following error:

Microsoft JET Database Engine (0x80040E14)
Object 'qryProcName' already exists.

I look in the Access database and can't find qryProcName as a query, macro
or any other object. How do I access it?

Morris

 

[Ray Costanzo [MVP]]

You should see it in the query collection in Access.

Mind if I ask why you're creating "stored procedures" this way? Do you not
have access to the Access file?

Ray at work

 

[Morris]

It doesn't appear in the Query tab in Access.

I have access to the mdb file, but users of our intranet do not. The reason
for needing to create queries is a long story. It's also quite difficult to
explain. It may not even be the ideal solution (Bob's posts on
parameterised queries have got me thinking...), but it's one that could
work.

I could try to explain off list. If you manage to a) read through the whole
scenario (which will be long!) and b) think it's acceptable to make it a
post, I'd post it to the group as a whole.

Morris

You should see it in the query collection in Access.

Mind if I ask why you're creating "stored procedures" this way? Do you
not
have access to the Access file?

Ray at work

I've created a procedure/query in an Access 2000 database using the
following code:
<%
strAppend = "ProcName"
strSQL = "Create Procedure [qry" & strAppend & "] AS Select * From
Stories
ORDER BY StoryID DESC;"
cn.execute(strSQL)
%>

I know I have successfully created the procedure/query, because when I
run
the code again, it gives me the following error:

Microsoft JET Database Engine (0x80040E14)
Object 'qryProcName' already exists.

I look in the Access database and can't find qryProcName as a query,
macro
or any other object. How do I access it?

Morris

 

[Bob Barrows [MVP]]

It doesn't appear in the Query tab in Access.

It won't. This is a limitation of Access. Procedures created
programmatically are not displayed in the GUI.

Bob Barrows

 

[Ray Costanzo [MVP]]

Really? I tried it and I see it. I used Access 2002 and this code:

dim cn
set cn = createobject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\somepath\file.mdb"
strAppend = "ProcName"
strSQL = "create Procedure [qry" & strAppend & "] AS Select * From Stories
ORDER BY StoryID;"
cn.execute strSQL,,129
cn.close
set cn = nothing

Ray at work

 

[Bob Barrows [MVP]]

In earlier versions, it would not show up. It appears they have corrected
this.

Or maybe I have it confused with using ADOX to create the procedure ... I
have no time to test this now.

Really? I tried it and I see it. I used Access 2002 and this code:

dim cn
set cn = createobject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\somepath\file.mdb" strAppend = "ProcName"
strSQL = "create Procedure [qry" & strAppend & "] AS Select * From
Stories ORDER BY StoryID;"
cn.execute strSQL,,129
cn.close
set cn = nothing

Ray at work

It won't. This is a limitation of Access. Procedures created
programmatically are not displayed in the GUI.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Morris]

I'm using Access 2000 :-( I might test it on 2003 at work though.

Morris

In earlier versions, it would not show up. It appears they have corrected
this.

Or maybe I have it confused with using ADOX to create the procedure ... I
have no time to test this now.

Really? I tried it and I see it. I used Access 2002 and this code:

dim cn
set cn = createobject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\somepath\file.mdb" strAppend = "ProcName"
strSQL = "create Procedure [qry" & strAppend & "] AS Select * From
Stories ORDER BY StoryID;"
cn.execute strSQL,,129
cn.close
set cn = nothing

Ray at work

Morris wrote:
It doesn't appear in the Query tab in Access.

It won't. This is a limitation of Access. Procedures created
programmatically are not displayed in the GUI.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Chris Hohmann]

In earlier versions, it would not show up. It appears they have corrected
this.

Or maybe I have it confused with using ADOX to create the procedure ... I
have no time to test this now.

You're not confused. This was a known issue in Access2K, resolved in
Access2K2. Here's a previous thread:
http://groups-beta.google.com/group..._frm/thread/9a81ee4ab96353a6/92dcd119946d7e57