|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Procedure/Query Confusion
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 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 Show quote "Morris" <u***@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 > > > 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 Show quote "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" <u***@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 wrote:
> It doesn't appear in the Query tab in Access. It won't. This is a limitation of Access. Procedures createdprogrammatically 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. 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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:OP7fWQsGFHA.1396@TK2MSFTNGP10.phx.gbl... > 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. > > 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. Ray Costanzo [MVP] wrote: Show quote > 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 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. I'm using Access 2000 :-( I might test it on 2003 at work though.
Morris Show quote "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 confused with using ADOX to create the procedure ... I > have no time to test this now. > > Ray Costanzo [MVP] wrote: >> 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 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. > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message You're not confused. This was a known issue in Access2K, resolved innews:eYMPSC1GFHA.2804@TK2MSFTNGP10.phx.gbl... > 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. Access2K2. Here's a previous thread: http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/browse_frm/thread/9a81ee4ab96353a6/92dcd119946d7e57  |
|||||||||||||||||||||||
Other interesting topics