Access ADP, filter lookup & SQL Server 2000

[xvblack]

Hi - I have an Access ADP front ending a SQL Server 2000 database. This has been working fine for some time but I now have a problem that I have spent many days trying to fix in vain.

The problem revolves around using the filter lookup property on some textboxes on the Access forms. When I use the form everything works fine as I am an administrator (we use NT Authentication on SQL Server) but when a normal user tries to use it they just get the 'NULL/IS NOT NULL' default values.

I have read many posts and have made sure that the record source qualifier is set to 'dbo' and have used SQL Profiler to trace what is happening. I believe the problem lies somewhere with the permissions that normal users have (or don't have!) but I can't see what.

The Profiler trace showed that a temporary SQL table is created by a system stored procedure (sp_MShelpcolumns) and my account inserts records into it whereas a user account doesn't.

Anyone got any ideas??

 

[SQL_Hell]

Hi log on to query analzer with the user account that gives the problems and try the query again, QA should report a more accurate error message

 

[xvblack]

Thanks for the reply SQL_Hell but I don't think this is where the problem lies as the query runs fine regardless of who runs it.

Let me explain a bit more....

The Access form uses a stored procedure as it's underlying record source - this actually runs ok whether as myself or as a normal user. What the user actually wants to do is to do a filter by form query against this recordset.

When this happens Access must fire off some sort of query against the database to populate any controls that have the filter lookup property set. This is what isn't happening for the user - but when I do it then it works fine! The only differences are the SQL Server permissions that I have as opposed to a normal user ('admin' vs 'user').

Does that help explain?

 

[giovi2002]

I've done alot with sql server adp and encountered the same problem.

You can try the following:

Goto tools>options>edit/find
Goto show list of values in and select both options (records in local snapshot records at server)

If you turn on the ServerFilterByForm property at your form, your form will open in a special view that turns text boxes into
combo boxes. Within the options specified in tools>options you told access to show values within the combo boxes instead of the standard Null or Is null.