| Author |
Procedure/Query Confusion
|
|
| Morris 2005-02-24, 8:55 pm |
| 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] 2005-02-24, 8:55 pm |
| 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" <user@domain.com> wrote in message
news:386uldF5mbkqsU1@individual.net...
> 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
>
>
>
| |
| Morris 2005-02-24, 8:55 pm |
| 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
"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
message news:O6LkLrrGFHA.3076@tk2msftngp13.phx.gbl...
> 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" <user@domain.com> wrote in message
> news:386uldF5mbkqsU1@individual.net...
>
>
| |
| Bob Barrows [MVP] 2005-02-24, 8:55 pm |
| 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.
| |
| Ray Costanzo [MVP] 2005-02-25, 3:56 pm |
| 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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OP7fWQsGFHA.1396@TK2MSFTNGP10.phx.gbl...
> Morris wrote:
>
> 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.
>
>
| |
| Bob Barrows [MVP] 2005-02-25, 3:56 pm |
| In earlier versions, it would not show up. It appears they have corrected
this.
Or maybe I have it  with using ADOX to create the procedure ... I
have no time to test this now.
Ray Costanzo [MVP] wrote:[color=darkred]
> 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]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OP7fWQsGFHA.1396@TK2MSFTNGP10.phx.gbl...
--
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 2005-02-25, 8:55 pm |
| I'm using Access 2000 :-( I might test it on 2003 at work though.
Morris
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eYMPSC1GFHA.2804@TK2MSFTNGP10.phx.gbl...
> In earlier versions, it would not show up. It appears they have corrected
> this.
>
> Or maybe I have it with using ADOX to create the procedure ... I
> have no time to test this now.
>
> Ray Costanzo [MVP] wrote:
>
> --
> 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 2005-02-25, 8:55 pm |
| "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eYMPSC1GFHA.2804@TK2MSFTNGP10.phx.gbl...
> In earlier versions, it would not show up. It appears they have corrected
> this.
>
> Or maybe I have it with using ADOX to create the procedure ... I
> have no time to test this now.
You're not . This was a known issue in Access2K, resolved in
Access2K2. Here's a previous thread:
http://groups-beta.google.com/group...2dcd119946d7e57
|
|
|
|