I have been using MS Access ADP front end to an SQL Server 2012 database in several applications using hundreds of forms and reports over nearly ten years. However, in almost all cases I set the datasource at design time or by using vba dependent on user selection/events.
I have almost never used the InputParameters property, not least because any change to the form caused the expression to be dropped and unless I had pasted a copy into notepad I had to laboriously reconstruct it. As a further discouragement there is almost no documentation about the use of this property - I don't know how to specify a boolean/bit parameter so in this example @ProjRates is passed as an integer instead.
In this case I was using a subReport with:
recordsource: spProjectStageTime
inputparameters: @ProjectID INT = HomeProject(), @StartDate DATETIME = StartDate(), @EndDate DATETIME = EndDate(), @Stage NVARCHAR(50) = Stage(), @ProjRates INT = ProjRates()
Where HomeProject() etc are VBA functions.
This worked fine when I was using the adp connected as sa or with integrated security and a login with sysadmin rights but it wouldn't retrieve any data for ordinary users even if I granted them dbowner on the database(s). I spent an afternoon tweaking permissions and using profiler on the SQL server trying to work out why no data was returned.
In the end I discovered it was because I hadn't specified the recordsource qualifier. When I changed this to dbo and rebuilt the inputparameters string which Access promptly dropped (thanks Microsoft :banghead
it worked fine. I can probably now remove some of the enhanced permissions I granted my users while trying to fix this.
I conclude there is some schema ownership/assumptions thing going on.
This post might just save someone else a lot of debugging....
Steve
I have almost never used the InputParameters property, not least because any change to the form caused the expression to be dropped and unless I had pasted a copy into notepad I had to laboriously reconstruct it. As a further discouragement there is almost no documentation about the use of this property - I don't know how to specify a boolean/bit parameter so in this example @ProjRates is passed as an integer instead.
In this case I was using a subReport with:
recordsource: spProjectStageTime
inputparameters: @ProjectID INT = HomeProject(), @StartDate DATETIME = StartDate(), @EndDate DATETIME = EndDate(), @Stage NVARCHAR(50) = Stage(), @ProjRates INT = ProjRates()
Where HomeProject() etc are VBA functions.
This worked fine when I was using the adp connected as sa or with integrated security and a login with sysadmin rights but it wouldn't retrieve any data for ordinary users even if I granted them dbowner on the database(s). I spent an afternoon tweaking permissions and using profiler on the SQL server trying to work out why no data was returned.
In the end I discovered it was because I hadn't specified the recordsource qualifier. When I changed this to dbo and rebuilt the inputparameters string which Access promptly dropped (thanks Microsoft :banghead
it worked fine. I can probably now remove some of the enhanced permissions I granted my users while trying to fix this.I conclude there is some schema ownership/assumptions thing going on.
This post might just save someone else a lot of debugging....
Steve
