Stored Procs, Parameters and Access Runtime

Stored Procs, Parameters and Access Runtime

Post by Chris Anderso » Thu, 11 Jul 2002 00:38:05



Hi,

I have a form in an ADP that I want to execute a stored procedure on click
of a command button.

The code behind my command button looks like...

dim cmdUpdate as adodb.command

Set cmdUpdate = New Command

With cmdUpdate
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "sp_OutworkerPayments"
    .Parameters.Refresh





    .Execute
End With

Set cmdUpdate = Nothing

MsgBox "Payment made successfully.", vbInformation, Caption

.... my problem is that while this code works on my development machine
(which has the full version of Access 2K) when I package and deploy it to
the end users (with Access 2K runtime) it falls over.

Through a series of message boxes inserted in appropriate places I've worked
out that the error occurs on the .parameters.refresh line but I've no idea
why? especially when it works on other machines with the FULL version of
Access.

Are there some more properties of the command object I need to use/set?

Any help will be massively appreciated 'cause this is sending me nutz!

Thanks,

Chris.

 
 
 

Stored Procs, Parameters and Access Runtime

Post by Chris Anderso » Thu, 11 Jul 2002 01:14:03


Folks,

I thought I'd post this before I waste anyone's time.

I've found a solution, someone pointed out that I could just use an ado
parameter object and append the parameters to the command object without
having to refresh the collection.  I tried it and it worked.

I would still like to know...

a) why my original code didn't work under a runtime environment yet it
worked on the full version of Access?
b) why the same code used elsewhere in the project to run different stored
procedures works a treat in both runtime and normal versions of access?

...if anyone has the time or the knowledge.

CA


> Hi,

> I have a form in an ADP that I want to execute a stored procedure on click
> of a command button.

> The code behind my command button looks like...

> dim cmdUpdate as adodb.command

> Set cmdUpdate = New Command

> With cmdUpdate
>     .ActiveConnection = CurrentProject.Connection
>     .CommandType = adCmdStoredProc
>     .CommandText = "sp_OutworkerPayments"
>     .Parameters.Refresh





>     .Execute
> End With

> Set cmdUpdate = Nothing

> MsgBox "Payment made successfully.", vbInformation, Caption

> .... my problem is that while this code works on my development machine
> (which has the full version of Access 2K) when I package and deploy it to
> the end users (with Access 2K runtime) it falls over.

> Through a series of message boxes inserted in appropriate places I've
worked
> out that the error occurs on the .parameters.refresh line but I've no idea
> why? especially when it works on other machines with the FULL version of
> Access.

> Are there some more properties of the command object I need to use/set?

> Any help will be massively appreciated 'cause this is sending me nutz!

> Thanks,

> Chris.


 
 
 

Stored Procs, Parameters and Access Runtime

Post by Vadim Rap » Thu, 11 Jul 2002 02:04:47


Quote:> a) why my original code didn't work under a runtime environment yet it
> worked on the full version of Access?

apparently, Access thought that refreshing parameters can be qualified as design.

Quote:> b) why the same code used elsewhere in the project to run different stored
> procedures works a treat in both runtime and normal versions of access?

this is probably next to impossible to say, especially w/o additional details. You
might run Profiler to see what's going on on the sql server in this palce and in
others.

Note that the whole your command-related code might be replaced by the following
single line:

currentproject.connection.execute _
"sp_OutworkerPayments('" & ddlocation & "',' & txtchequeDate & "','" ............ &
")",,,,adCmdStoredProc

Vadim

 
 
 

Stored Procs, Parameters and Access Runtime

Post by Chris Anderso » Thu, 11 Jul 2002 17:23:43


Thanks for the answers, it's nice to know.

CA


Quote:> > a) why my original code didn't work under a runtime environment yet it
> > worked on the full version of Access?

> apparently, Access thought that refreshing parameters can be qualified as
design.

> > b) why the same code used elsewhere in the project to run different
stored
> > procedures works a treat in both runtime and normal versions of access?

> this is probably next to impossible to say, especially w/o additional
details. You
> might run Profiler to see what's going on on the sql server in this palce
and in
> others.

> Note that the whole your command-related code might be replaced by the
following
> single line:

> currentproject.connection.execute _
> "sp_OutworkerPayments('" & ddlocation & "',' & txtchequeDate & "','"
............ &
> ")",,,,adCmdStoredProc

> Vadim

 
 
 

1. Accessing a Stored Procedure from an Access Module

I'm trying to figure out how to gain access to a Stored
Procedure from code with in an Access Data Base.
I can create a work space and open up a connection but I'm
just unable to understand/figure out how to get at the
stupid SP itself.  I've gone to Microsoft, Access help
purchased a couple books but I'm just plain stumped.  If
somebody had some code that does just this and could post
that would be great.  I want to pass a value to the sp and
get a result returned.  I have seen code that has the SQL
embedded in the call but I want to make sure the query is
run on the SQL server not on the client and I'm not sure
that the code will run on the server if I do a querydef
call.

Thanks in advance

Paul

2. StreamServer Port : bind() failed : How to solve this problem

3. storing/accessing binary data with stored procs.

4. Informix 'reader' utility

5. User functions in Access Stored Queries accessing through VB

6. DATETIME syntax for MS Access

7. Pasar consultas a proyectos access / Convert access queries to access projects

8. SQL Server 6.5 and Access97

9. Access 2002 / sql 2000 / pb avec Stored Procedures as source for reports

10. Read an Access Database from inside a SQL7 Stored Procedure

11. Stored Procedure access data in another database

12. accessing stored procedures from excel

13. Join query works fine as Stored Procedure, returns unmatched results in Access