Stored procedure as row source for a combo box

[Merlinus]

A few combo boxes in an Access 2003 / 2007 ADP connecting to SQL Server 2005
/ 2008 are using stored procedures as row source. No problem as long as
Access and SQL Server are on the same machine. If the Access front-end
resides on another machine, dropping down any of the combo boxes displays
this error message:

The record source '<spName>' specified on this form or report does not exist.
The name of the recordsource may be misspelled, the recordsource was deleted
or renamed, or the recordsource exists in a different database.

Double-clicking on the stored procedures under Queries executes them as
expected (so, it's not a permission problem).
This only happens when the stored procedure is assigned via the form's
properties. Parameterized SPs that are assigned to combo boxes in code work
fine.
Stored procedures used in a different context, for example as a record
source for a form, work well.

Does anybody know what could cause this behavior? (If there's no simple fix
I probably can assign the row source for all combo boxes in code).

 

[Sylvain Lafontaine]

Looks like that you might have a problem with the schema/owner of the
stored procedures (SP); as ADP has the bad habit of adding the user account
before the name of SP without ownership. For example, instead of calling
MySP or dbo.MySP, it will call UserName.MySP and if UserName.MySP doesn't
exist, SQL-Server cannot look anymore for dbo.MySP.

Try setting the Record Source Qualifier property of your forms to « dbo » or
use the command EXEC to call the SP for these record sources; for example
use "EXEC MySP" or "EXEC dbo.MySP" instead of simply "MySP".

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Merlinus]

Thank you so much. Your suggestion solves my problem.

 

[Kevin]

Merlinus,

I also had this exact same issue, but using Access 2000 to SQL Server 2008
(don't ask why my company is so far behind in their version of Access).
Anyway, after countless hours of head scratching and nail biting, it looks
like the solution was based on updating to the most current service pack for
MS Access Runtime.

 

[sorin]

Merlinus,

I also had this exact same issue, but using Access 2000 to SQL Server 2008
(don't ask why my company is so far behind in their version of Access).
Anyway, after countless hours of head scratching and nail biting, it looks
like the solution was based on updating to the most current service pack
for
MS Access Runtime.