DoCmd.OpenStoredProcedure won't display altered proc

[Guest]

Hi,

I am using an ADP project and I have a procedure that is altered each time
to display a grid as a result of the DoCmd.OpenStoredProcedure command
(because there was no other way to pass parameters from controls on the form
to the sub). The code is as follows:

stDocName = "adhocquery"
CurrentProject.Connection.Execute "drop procedure " & stDocName
CurrentProject.Connection.Execute "alter procedure " & stDocName & " as
begin " & vbCrLf & Me.Text0 & vbCrLf & " end"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acReadOnly

Now the problem is that whenever I execute the proc the sub always shows the
results grid from the first alteration of the proc. What I mean is that upon
successive alter commands, the results grid does not reflect the changes. I
am new to access and I would really appreciate it if some can help me out
here.

P.S. I am trying to allow a user to execute ad hoc queries as ADP does not
allow the execution of a sql query without first saving it as a stored
procedure or a view or function, which I is not what the client needs to do.
Is there any other way of executing queries and looking up the results in
grid using ADP.

 

[aaron.kempf]

although Access Data Projects are the rightful heirs to the
microsoft.public.access newsgroup; I would reccomend looking in the ADP
newsgroup.

all you've got to do is write a wrapper form around your sproc; and you
can do whatever friggin parameters you want.

dont toy with 'inputParameters' property.

if your sproc is lookig for a parameter named @PLU all you've got to do
is to make a text control named 'PLU' and then bind it to a VB
function.


it's about 100 times more powerful than stupid silly MDB crap.

 

[Guest]

The code reads like this:

stDocName = "adhocquery"
CurrentProject.Connection.Execute "alter procedure " & stDocName & " as
begin " & vbCrLf & Me.Text0 & vbCrLf & " end"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acReadOnly

Can anyone please help

 

[Dirk Goldgar]

The code reads like this:

stDocName = "adhocquery"
CurrentProject.Connection.Execute "alter procedure " & stDocName & "
as begin " & vbCrLf & Me.Text0 & vbCrLf & " end"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acReadOnly

Can anyone please help

I've never worked with ADPs, but does it make any difference if you use
CurrentProject.AccessConnection, rather than CurrentProject.Connection?
E.g,

CurrentProject.AccessConnection.Execute _
"alter procedure " & stDocName & " as begin " & _
vbCrLf & Me.Text0 & vbCrLf & " end"

 

[aaron.kempf]

Dirk;

you've NEVER WORKED WITH ADP?

how can you POSSIBLY be an 'expert' with Microsoft access then?

ADP is superior to MDB. Anyone that tells you otherwise is a senile 60
year old retard like Larry Linson; that is TOO ****ING LAZY to learn
new technologies.

ADP is superior in development, administration, security, performance,
stability.

Lose the training wheels you dipshit MVP that haven't USED 2/3rds OF
ACCESS