X
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.
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').
Anyone got any ideas??
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.
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').
Anyone got any ideas??