Stored procedure that updates table (ADP)

[lemes_m]

Hi All!
I have stored procedure called "UpdatingInvoices" that updates field
"OrderNo" in table "Invoices". Value should be taken from one form and
I tried to do it by puting parameter in
my stored procedure called @Order.

When user submits updating I run following code and ADO command behind
form:

Private Sub cmdSave_Click()
DoCmd.RunCommand acCmdSaveRecord
Call UpdatingInvoices
End Sub

Function UpdatingInvoices()
Dim qdfCurr As DAO.QueryDef
Set qdfCurr = CurrentDb().QueryDefs("UpdatingInvoices")
qdfCurr.SQL = "Exec UpdatingInvoices @Order = " & me.OrderNumber &
""
End Function

If I run stored procedure from Database container and manualy enter
parameter value, stored procedure updates table, but not with this
code. Anyone knows the solution?

 

[Graham R Seach]

Unfortunately, you're mixing data access technologies. DAO knows nothing
about stored procedures. You need to use ADO.

1. Set a reference to ADO:
a). Open any code module
b). Select References from the Tools menu
c). Find and select Microsoft ActiveX Data Objects 2.x Library
d). Click OK.

2. The following is the code you want to use:
Private Sub UpdatingInvoices()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

'You might need to build a connection string
'if you're not using an ADP.
cn.Open CurrentProject.ActiveConnection

With cmd
.CommandText = "UpdatingInvoices"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@Order") = Me.OrderNumber
.Execute
End With

cn.Close
Set cmd = Nothing
Set cn = Nothing
End Sub


Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[lemes_m]

Thanks for your reply, I solved this problem with using DAO in
complete. I've made one line of sql code behind form which is not using
parametars (it refers to value of control directly).

In the first moment I solved this problem with following code:

docmd.runsql "Exec UpdatingInvoices @Order = " & me.OrderNumber &
""

and it was functioning until I needed one more parametar and I faced
new problem - I couldn't force SP to run, so I decided to use sql code
from "normal Access query" which I used before in MDB's.

Is there any difference in application performance between ADO and DAO
and what is better option. It is quite important for me because this
application will hande about 1.000.000 records.

Regards,

Mirnes Lemes

 

[Graham R Seach]

Against a Jet database, DAO has better performance because DAO was developed
and specifically optimised for Jet.

Against external (non-Jet) data sources, ADO has better performance.

But since you're executing stored procedures against SQL, all the work is
done on the server, so it doesn't make much difference whether you're using
DAO or ADO, especially if using a pass-through query.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia