subform data not displaying with non sa account

[Guest]

Hi,

I have developed an application that displays data from a query in a
subform. This worked fine when I developed it with the SA account but when
creating specific accounts for users, no data is displayed in the subform. I
have granted rights to all tables and stored procedures. Is there any other
difference between the two accounts that could cause this problem.

This may or maynot be related but when I switch between form design and
normal runtime form view I get the following error message.

Multiple-step OLE DB operation generated errors, check each OLE-DB status
value if available. No work was done.

Any ideas

 

[privatenews]

Hello,

You may want to temporarily grant database owner role to the user you use
on SQL Server side. This will isolate if it is a permission issue on server
side. Also, please use profiler to trace the statements running in the
background when the issue occurs.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi,

I have created a user called test to give various rights, to identify the
problem as suggested. Please read below I tried with the following.

1. I added it to the database owner group and the subform still didn't
populate with any data.

2. I added the test user to the sysadmin group and then the subform did
populate with data from the query as it should.

I ran SQL profiler as suggested but this didn't really show anything
unexpected. It showed the same statement with either of the two situations
above. Statement shown below.

exec sp_executesql N' EXEC "dbo"."CC_ComplaintSelectionSub_SEL" @P1 , @P2 ,
@P3 , @P4 ', N'@P1 nvarchar(4),@P2 nvarchar(3),@P3 nvarchar(10),@P4
nvarchar(10)', N'DATE', N'N/A', N'2005-12-21', N'2006-01-19'

The only other statement that I am not sure what its for is below.

SET NO_BROWSETABLE ON

Any more suggestions?

 

[privatenews]

Hello,

SET NO_BROWSETABLE ON is an undocumented option performed for Remote Data
Service (RDS) ActiveX Data Connector (ADC) connections to SQL Server.
Enabling this option makes every SELECT statement act as though FOR BROWSE
had been appended to the statement, but bypasses the temporary table that
FOR BROWSE normally pipes the results through.

FIX: Insert Error (Msg 213 ) with NO_BROWSETABLE and INSERT EXEC
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q275483

Usually NO_BROWSETABLE permissions default to all users.

Did you enable "errors and warnning" events in profiler to trace if any
error occurs when sysadmin role is not enabled for the user? If you run the
query of subform directly, or you use it as a form other than subform, what
is the difference?

Do you see some specical statment in CC_ComplaintSelectionSub_SEL?

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi,

I have completed the trace with 'errors and warnings' but there aren't any
errors or warnings. I can send you a copy of the trace if there is a way of
sending it. I will try running the subform in a min.

For information when I wrote the stored procedure I found problems with
access when using many if statements (I think because access couldn't handle
multiple return values so I had to use SET NOCOUNT ON and SET NOCOUNT OFF
when the if statements find the corect select statement to exectcute, could
this have anything to do with it? A copy of the stored procedure is below, I
have altered the company specific column names, so my appologies if this
introduces any syntax errors.


--------------------------------------------------------------------

CREATE PROCEDURE dbo.test_SEL
(
@SelectionType VARCHAR(4),
@SelectionCriteria VARCHAR(50),
@DateFrom datetime,
@DateTo datetime
)
AS
SET NOCOUNT ON
--SET DATEFORMAT dmy
If @SelectionType = 'CCID' -- Used for selection on all or part of CCID
BEGIN
SET NOCOUNT OFF
SELECT col1,
col2,
col3,
col4,
col5
FROM dbo.table1
WHERE col1 Like '%' + @SelectionCriteria + '%'
ORDER BY col2
RETURN
SET NOCOUNT ON
END
ELSE IF @SelectionType = 'CUST'
BEGIN
SET NOCOUNT OFF
SELECT col1,
col2,
col3,
col4,
col5
FROM dbo.table1
WHERE col2 Like '%' + @SelectionCriteria + '%'
ORDER BY col2
SET NOCOUNT ON
END
ELSE IF @SelectionType = 'ORDR'
BEGIN
SET NOCOUNT OFF
SELECT col1,
col2,
col3,
col4,
col5
FROM dbo.table1
WHERE col3 Like '%' + @SelectionCriteria + '%'
ORDER BY col2
SET NOCOUNT ON
END
ELSE IF @SelectionType = 'DATE'
BEGIN
SET NOCOUNT OFF
SELECT col1,
col2,
col3,
col4,
col5
FROM dbo.table1
WHERE col4 > @DateFrom AND col5 < @DateTo
ORDER BY Col2
RETURN
SET NOCOUNT ON
END

GO

-----------------------------

 

[Guest]

Please read the related reply above, in addition to that post I have tried
running the subform on its own and it does populate with data.

 

[privatenews]

Hello,

It's weird that the issue only occur in subform. I think "set nocount
on/off" shall not be a problem here.

You may want to create a small new adp project with subform to see if you
could reproduce it. If so, please send the adp and database backup to me at
(e-mail address removed). I will try to duplicate on my side.

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Sylvain Lafontaine]

The error message that you are seeing might be related to something else
than the way your SP is written.

Just to make sure, exactly how are the Record Source and the Record Source
Qualifier defined for the subform?

 

[Guest]

Hi,

In rebuilding a form to duplicate the problem I found what was causing it.
The subform container held on the main form had incorrect properties defined
for 'link child fields' and 'link master fields'. As I had no data on the
main form these should have been empty.

Thanks for all the help on this.

 

[privatenews]

Hi,

Great to hear you found the resolution. This will surely benefit the
community.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.