Executing SP from Access form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all. I'm experiencing the following problem: I have an ADP db linked to
SQL Server 2000. Through a form I launch a SP using the method
CurrentProject.Connection.Execute and then open a report with a DoCmd. The SP
initially empty a table using a DELETE and then populate it with INSERT and
UPDATES. Now using my user everything is OK, the SP runs correctly and the
report shows the expected results. Executing the same steps with a different
user the SP runs partially (only the DELETE statement) and the table remains
empty. I thought it was a permission problem on SQL but they are OK. The
strangest thing is if I run the SP from Access without using the form it
works with all users !!!!

Anybody has any clue about such behaviour ?

Thanks in advance for any suggestion.
 
Hi all. I'm experiencing the following problem: I have an ADP db linked to
SQL Server 2000. Through a form I launch a SP using the method
CurrentProject.Connection.Execute and then open a report with a DoCmd. The SP
initially empty a table using a DELETE and then populate it with INSERT and
UPDATES. Now using my user everything is OK, the SP runs correctly and the
report shows the expected results. Executing the same steps with a different
user the SP runs partially (only the DELETE statement) and the table remains
empty. I thought it was a permission problem on SQL but they are OK. The
strangest thing is if I run the SP from Access without using the form it
works with all users !!!!
Click to expand...

Always(!) make sure you are using the full name including the owner of
the relevant object when adressing any SQL-Server object. E.g do _not_
just write

DELETE FROM myTable

but instead write

DELETE FROM username.myTable

Unless you are expressing yourself explicitely in this matter, SQL-Server
will have to figure out by himself what object you actually meant. - This
does not work as expected in every case...

The only exception to this rule is, when you really want diffrent users
to affect diffrent object by executing the same sql.

Best wishes
Phil
 
Thanks Phil, but it did not work either.

I changed the SP referencing all objects as dbname.owner.objectname but I
still get the same problem. What is not clear to me is why if I launch the SP
from Access without using the form it goes OK. I don't understand which
difference might be.
Thanks anyway

Best Regards

Mauro
 
Show us:

1) the exact definitino of the stored proc
2) the exact definition of the script that runs in the form (i.e. is it an on_click event?)

Maybe that will helpp...???

--Malcolm
 
1) Here below is the SP def

CREATE PROC tblLoansAverageMargin_sp
(@DateFrom smalldatetime, @DateTo smalldatetime, @DateExcRate smalldatetime,
@Days int)
AS

DELETE FROM ProfitabilitySQL.dbo.tblLoansAverageMargin

INSERT INTO ProfitabilitySQL.dbo.tblLoansAverageMargin
(LoanReference, CustomerNumber, BookCode, LastBusinessDate,
Currency, CurrentPrincipalAmount,
AverageVolume, TotalMonthlyMargin)

SELECT tblCorporateLoansGlobal.LoanReference,
tblCorporateLoansGlobal.CustomerNumber, tblCorporateLoansGlobal.BookCode,
MAX(tblCorporateLoansGlobal.BusinessDate),
tblCorporateLoansGlobal.Currency,
tblCorporateLoansGlobal.CurrentPrincipalAmount,
AverageVolume,SUM(tblCorporateLoansGlobal.DailyMargin) AS TotalMonthlyMargin
SUM(tblCorporateLoansGlobal.VolumeAmount) AS
AverageVolume,SUM(tblCorporateLoansGlobal.DailyMargin) AS TotalMonthlyMargin

FROM HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd INNER JOIN
tblCorporateLoansGlobal ON
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND
((tblCorporateLoansGlobal.BusinessDate >= @DateFrom) AND
(tblCorporateLoansGlobal.BusinessDate <= @DateTo))
GROUP BY tblCorporateLoansGlobal.LoanReference,
tblCorporateLoansGlobal.CustomerNumber,
tblCorporateLoansGlobal.BookCode,
tblCorporateLoansGlobal.Currency,
tblCorporateLoansGlobal.CurrentPrincipalAmount

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET AverageEUROutstanding
=((ProfitabilitySQL.dbo.tblLoansAverageMargin.AverageVolume /
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate]) / @Days),
TotalEURMarginIncome =
(ProfitabilitySQL.dbo.tblLoansAverageMargin.TotalMonthlyMargin /
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate])

FROM ProfitabilitySQL.dbo.tblLoansAverageMargin INNER JOIN
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency INNER JOIN
tblCorporateLoansGlobal ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.LoanReference =
tblCorporateLoansGlobal.LoanReference AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.CustomerNumber =
tblCorporateLoansGlobal.CustomerNumber AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND

(HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.MultiplyDivideIndicator =
'D')

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET AverageEUROutstanding
=((ProfitabilitySQL.dbo.tblLoansAverageMargin.AverageVolume *
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate]) / @Days),
TotalEURMarginIncome =
(ProfitabilitySQL.dbo.tblLoansAverageMargin.TotalMonthlyMargin *
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate])
FROM ProfitabilitySQL.dbo.tblLoansAverageMargin INNER JOIN
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency INNER JOIN
tblCorporateLoansGlobal ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.LoanReference =
tblCorporateLoansGlobal.LoanReference AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.CustomerNumber =
tblCorporateLoansGlobal.CustomerNumber AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND

(HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.MultiplyDivideIndicator =
'M')

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET DateFrom = @DateFrom,
DateTo = @DateTo,
DateExc = @DateExcRate
GO

2) The script runs after on_click event - here below the statement:

CurrentProject.Connection.Execute cmdText, , adExecuteNoRecords

where cmdText is:

spName = "exec ProfitabilitySQL.dbo.tblLoansAverageMargin_sp "
cmdText = spName & " '" & Data1 & "'" & ", '" & Data2 & "'" & ", '" &
Data3 _
& "'" & ", '" & Forms![frmReportParameters]!Days & "'"

Thanks for any suggestion
 
First, you should use alias for your tables, will make things a lot clearer
to read.

Second, are you sure that there is not permission problem associated with
tblCorporateLoansGlobal? What about specifying the whole path
(dbo.tblCorporateLoansGlobal or databasename.dbo.tblCorporateLoansGlobal) as
well for this table? One way to test this would be to use a simple Select
statement on this table and see what may be read (or returned) from it.
Repeat the same verifications will all other tables as well; as this is the
only way to make sure that you don't have some permission problem.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Mauro said:
1) Here below is the SP def

CREATE PROC tblLoansAverageMargin_sp
(@DateFrom smalldatetime, @DateTo smalldatetime, @DateExcRate
smalldatetime,
@Days int)
AS

DELETE FROM ProfitabilitySQL.dbo.tblLoansAverageMargin

INSERT INTO ProfitabilitySQL.dbo.tblLoansAverageMargin
(LoanReference, CustomerNumber, BookCode, LastBusinessDate,
Currency, CurrentPrincipalAmount,
AverageVolume, TotalMonthlyMargin)

SELECT tblCorporateLoansGlobal.LoanReference,
tblCorporateLoansGlobal.CustomerNumber, tblCorporateLoansGlobal.BookCode,
MAX(tblCorporateLoansGlobal.BusinessDate),
tblCorporateLoansGlobal.Currency,
tblCorporateLoansGlobal.CurrentPrincipalAmount,
AverageVolume,SUM(tblCorporateLoansGlobal.DailyMargin) AS
TotalMonthlyMargin
SUM(tblCorporateLoansGlobal.VolumeAmount) AS
AverageVolume,SUM(tblCorporateLoansGlobal.DailyMargin) AS
TotalMonthlyMargin

FROM HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd INNER JOIN
tblCorporateLoansGlobal ON
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND
((tblCorporateLoansGlobal.BusinessDate >= @DateFrom) AND
(tblCorporateLoansGlobal.BusinessDate <= @DateTo))
GROUP BY tblCorporateLoansGlobal.LoanReference,
tblCorporateLoansGlobal.CustomerNumber,
tblCorporateLoansGlobal.BookCode,
tblCorporateLoansGlobal.Currency,
tblCorporateLoansGlobal.CurrentPrincipalAmount

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET AverageEUROutstanding
=((ProfitabilitySQL.dbo.tblLoansAverageMargin.AverageVolume /
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate]) / @Days),
TotalEURMarginIncome =
(ProfitabilitySQL.dbo.tblLoansAverageMargin.TotalMonthlyMargin /
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate])

FROM ProfitabilitySQL.dbo.tblLoansAverageMargin INNER JOIN
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency INNER JOIN
tblCorporateLoansGlobal ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.LoanReference =
tblCorporateLoansGlobal.LoanReference AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.CustomerNumber =
tblCorporateLoansGlobal.CustomerNumber AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND

(HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.MultiplyDivideIndicator =
'D')

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET AverageEUROutstanding
=((ProfitabilitySQL.dbo.tblLoansAverageMargin.AverageVolume *
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate]) / @Days),
TotalEURMarginIncome =
(ProfitabilitySQL.dbo.tblLoansAverageMargin.TotalMonthlyMargin *
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate])
FROM ProfitabilitySQL.dbo.tblLoansAverageMargin INNER JOIN
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency INNER JOIN
tblCorporateLoansGlobal ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.LoanReference =
tblCorporateLoansGlobal.LoanReference AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.CustomerNumber =
tblCorporateLoansGlobal.CustomerNumber AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND

(HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.MultiplyDivideIndicator =
'M')

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET DateFrom = @DateFrom,
DateTo = @DateTo,
DateExc = @DateExcRate
GO

2) The script runs after on_click event - here below the statement:

CurrentProject.Connection.Execute cmdText, , adExecuteNoRecords

where cmdText is:

spName = "exec ProfitabilitySQL.dbo.tblLoansAverageMargin_sp "
cmdText = spName & " '" & Data1 & "'" & ", '" & Data2 & "'" & ", '" &
Data3 _
& "'" & ", '" & Forms![frmReportParameters]!Days &
"'"

Thanks for any suggestion

Malcolm Cook said:
Show us:

1) the exact definitino of the stored proc
2) the exact definition of the script that runs in the form (i.e. is it
an on_click event?)

Maybe that will helpp...???

--Malcolm
Click to expand...
Click to expand...
 
The problems has been sorted out. It was due to the format of the date
paratemeters of the SP. I was not using the ISO format so the SP did not
select any rows to be inserted in the table.

Thanks to all of you guys for your efforts and suggestions.

Best Regards, Mauro

Sylvain Lafontaine said:
First, you should use alias for your tables, will make things a lot clearer
to read.

Second, are you sure that there is not permission problem associated with
tblCorporateLoansGlobal? What about specifying the whole path
(dbo.tblCorporateLoansGlobal or databasename.dbo.tblCorporateLoansGlobal) as
well for this table? One way to test this would be to use a simple Select
statement on this table and see what may be read (or returned) from it.
Repeat the same verifications will all other tables as well; as this is the
only way to make sure that you don't have some permission problem.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Mauro said:
1) Here below is the SP def

CREATE PROC tblLoansAverageMargin_sp
(@DateFrom smalldatetime, @DateTo smalldatetime, @DateExcRate
smalldatetime,
@Days int)
AS

DELETE FROM ProfitabilitySQL.dbo.tblLoansAverageMargin

INSERT INTO ProfitabilitySQL.dbo.tblLoansAverageMargin
(LoanReference, CustomerNumber, BookCode, LastBusinessDate,
Currency, CurrentPrincipalAmount,
AverageVolume, TotalMonthlyMargin)

SELECT tblCorporateLoansGlobal.LoanReference,
tblCorporateLoansGlobal.CustomerNumber, tblCorporateLoansGlobal.BookCode,
MAX(tblCorporateLoansGlobal.BusinessDate),
tblCorporateLoansGlobal.Currency,
tblCorporateLoansGlobal.CurrentPrincipalAmount,
AverageVolume,SUM(tblCorporateLoansGlobal.DailyMargin) AS
TotalMonthlyMargin
SUM(tblCorporateLoansGlobal.VolumeAmount) AS
AverageVolume,SUM(tblCorporateLoansGlobal.DailyMargin) AS
TotalMonthlyMargin

FROM HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd INNER JOIN
tblCorporateLoansGlobal ON
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND
((tblCorporateLoansGlobal.BusinessDate >= @DateFrom) AND
(tblCorporateLoansGlobal.BusinessDate <= @DateTo))
GROUP BY tblCorporateLoansGlobal.LoanReference,
tblCorporateLoansGlobal.CustomerNumber,
tblCorporateLoansGlobal.BookCode,
tblCorporateLoansGlobal.Currency,
tblCorporateLoansGlobal.CurrentPrincipalAmount

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET AverageEUROutstanding
=((ProfitabilitySQL.dbo.tblLoansAverageMargin.AverageVolume /
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate]) / @Days),
TotalEURMarginIncome =
(ProfitabilitySQL.dbo.tblLoansAverageMargin.TotalMonthlyMargin /
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate])

FROM ProfitabilitySQL.dbo.tblLoansAverageMargin INNER JOIN
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency INNER JOIN
tblCorporateLoansGlobal ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.LoanReference =
tblCorporateLoansGlobal.LoanReference AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.CustomerNumber =
tblCorporateLoansGlobal.CustomerNumber AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND

(HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.MultiplyDivideIndicator =
'D')

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET AverageEUROutstanding
=((ProfitabilitySQL.dbo.tblLoansAverageMargin.AverageVolume *
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate]) / @Days),
TotalEURMarginIncome =
(ProfitabilitySQL.dbo.tblLoansAverageMargin.TotalMonthlyMargin *
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Exchange Rate])
FROM ProfitabilitySQL.dbo.tblLoansAverageMargin INNER JOIN
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.Currency INNER JOIN
tblCorporateLoansGlobal ON
ProfitabilitySQL.dbo.tblLoansAverageMargin.LoanReference =
tblCorporateLoansGlobal.LoanReference AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.CustomerNumber =
tblCorporateLoansGlobal.CustomerNumber AND
ProfitabilitySQL.dbo.tblLoansAverageMargin.Currency =
tblCorporateLoansGlobal.Currency
WHERE (HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.[Date] =
@DateExcRate) AND

(HVBMilanDataWareHouse.dbo.MidasCurrencysdcuhspd.MultiplyDivideIndicator =
'M')

UPDATE ProfitabilitySQL.dbo.tblLoansAverageMargin
SET DateFrom = @DateFrom,
DateTo = @DateTo,
DateExc = @DateExcRate
GO

2) The script runs after on_click event - here below the statement:

CurrentProject.Connection.Execute cmdText, , adExecuteNoRecords

where cmdText is:

spName = "exec ProfitabilitySQL.dbo.tblLoansAverageMargin_sp "
cmdText = spName & " '" & Data1 & "'" & ", '" & Data2 & "'" & ", '" &
Data3 _
& "'" & ", '" & Forms![frmReportParameters]!Days &
"'"

Thanks for any suggestion

Malcolm Cook said:
Show us:

1) the exact definitino of the stored proc
2) the exact definition of the script that runs in the form (i.e. is it
an on_click event?)

Maybe that will helpp...???

--Malcolm


Thanks Phil, but it did not work either.

I changed the SP referencing all objects as dbname.owner.objectname but
I
still get the same problem. What is not clear to me is why if I launch
the SP
from Access without using the form it goes OK. I don't understand which
difference might be.
Thanks anyway

Best Regards

Mauro

:


Hi all. I'm experiencing the following problem: I have an ADP db
linked to
SQL Server 2000. Through a form I launch a SP using the method
CurrentProject.Connection.Execute and then open a report with a
DoCmd. The SP
initially empty a table using a DELETE and then populate it with
INSERT and
UPDATES. Now using my user everything is OK, the SP runs correctly
and the
report shows the expected results. Executing the same steps with a
different
user the SP runs partially (only the DELETE statement) and the table
remains
empty. I thought it was a permission problem on SQL but they are OK.
The
strangest thing is if I run the SP from Access without using the
form it
works with all users !!!!

Always(!) make sure you are using the full name including the owner of
the relevant object when adressing any SQL-Server object. E.g do _not_
just write

DELETE FROM myTable

but instead write

DELETE FROM username.myTable

Unless you are expressing yourself explicitely in this matter,
SQL-Server
will have to figure out by himself what object you actually meant. -
This
does not work as expected in every case...

The only exception to this rule is, when you really want diffrent
users
to affect diffrent object by executing the same sql.

Best wishes
Phil
Click to expand...
Click to expand...
Click to expand...
 
Back
Top