Failed RowSource for Listbox

[scking]

Yesterday my Access project (adp) was working well using a listbox with the RowSource of 'EXEC spFillLb1 @TOP=50'. This successfully allowed me to set whatever stored procedures I needed and still pass in parameters. I've moved the SQL Server database to a SQL Server (SP3) from my MSDE (SP1) and now get an error when opening the form. The error states that the record source specified on this form does not exist. It does and I can still successfully run it in stand alone mode.

This capability is a feature that is extremely important to the application. Although I have had it working with loading data strings then listbox is then constrainted to a maximum of 2k characters.

Anyone aware what may be causing this problem? Could the service pack difference from SP1 to SP3 be causing these problems or could it be a permissions problem? If so, then why could I run the sp manually. Any help would be appreciated.



---------------------
scking@arinc.com
---------------------

 

[RoyVidar]

Hi!

For the first part of the question, about the SP, and what might cause this, you might have better luck posting in the ADP forum (forum958) - and difference in behaviour, perhaps one of the SQL fora?

To circumvent the 2k rowsource limitation of listbox rowsources (not sure, but I think thats a a2k limitation, I think xp+ versions allow more), you might consider using a callback function. Haven't tried that myself, but there should be info available either thru a search here, or your favourite web search engine (found this reference somewhere Populating Listbox).

Else, the messages you provide, seems to relate to the recordsource of the form, not the rowsource of the listbox - is there anything there? Sometimes messages like that might relate to some (slight?) corruption, have you tried to recreate the form/import to another adp, or I think the /decompile option should work with ADP's too (Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times)

Roy-Vidar

 

[scking]

I've determined the solution to the problem is not related to Access but to the permissions assignments in SQL Server. Once I unchecked the Deny Read permission on another database that was being accessed the problem disappeared.

Concerning the 2k character limitation to the listbox, I'm constrained to using Access 2000 for it's ability to interface to SourceSafe and manage Access objects individually. Although Access 2002 has this feature you can no longer purchase the developers toolbox except by third party vendors. Access 2003 no longer supports this feature thanks to Microsoft.

Previous to my forum message I had constructed a callback for the listbox but was not aware of any means to pass a parameter into a callback since they are strictly limited to the calls that Microsoft Access would make to the function.



---------------------
scking@arinc.com
---------------------