Re: Recordsource only available to Administrator on clients

The Chipman and Baron comment is about SQL-Server 2000 but with 2005, things
have changed a little. However, if in the ADP all database items appear
with the recordsource qualifier of "dbo." then you should be OK. However,
don't forget to set the Record Source Qualifier to dbo (even if ADP is
displaying all items with dbo. as the schema).

Instead of dbo.uspPt, try using uspPt as the Record Source. Is this doesn't
work, try to use a sql string instead with the EXEC statement before the
name of the SP and the parameters added at the end:

"EXEC uspPT " & FirstParameter & ", " & SecondParameter ...

Finally, if you open the Views/Stored Procedures/Functions Windows in ADP,
are you able to double-click and execute the uspPT stored procedure from
there?

And what about make a direct call using ADO objects?

Finally, what are seeing with the SQL-Server Profiler?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"John Hackert" <hackertjohnb@xxxxxxxxx> wrote in message
news:1180450183.910042.30790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thank you for replying.

To clarify the issues: The one and only SQL Server instance in
question is that on the SBS machine (entitled "SCRS-SERVER
\SQLEXPRESS"). On the SBS machine as well as the clients, the SQL
Server instance is readily connected via SSMSE (Server type: Database
Engine; Server name: SCRS-SERVER\SQLEXPRESS; Authentication: Windows
Authentication). As I noted, all database elements have been visible
in SSMSE, including the stored procedures. However, a consistent
observation is that on the clients after a "successful" connection, if
the empty white circle icon does not change to the green-circle-with-
white-arrowhead form, the problem in question is reproduced.

As I've continued to try to troubleshoot this, without question I have
found that if the client logon has administrator privileges with
respect to SBS, the green circle/white arrowhead appears, and the
problem does not occur in the ADP. When I referenced "The
Administrator," what I meant was the SBS Administrator account. In
the SBS "Server Management" module, if I use "Change User Permissions"
to temporarily set a client user's privileges from, say "User
Template" or "Power User Template" to "Administrator Template," then
logon that user on a client, the SSMSE shows the green circle/white
arrowhead, and the problem is absent. Of further interest, if, while
the user-as-administrator remains logged on to the client, I can
change the user's permissions back to, eg, "User Template" on the SBS,
and the SSMSE on the client continues to show the green+white icon.
When the user logs off and back on (no longer as an administrator),
the problem is reproduced.

On your suggestion I tried setting the clients' "User Mapping" to
"dbo" from "db_owner" for the database in question on the SQL Server
instance in question. I read further on how dbo/db_owner compare and
contrast. Chipman and Baron admonish, "Don't confuse dbo with the
db_owner" fixed database role...." Although I now understand these
terms better, unfortunately making the database role change to "dbo"
did not change the problem. BTW, in the ADP all database items appear
with the recordsource qualifier of "dbo."

Although I observe similar behavior in other forms bound to a
parameterized stored procedure, the particular form in question
("frmPt") has the following recordsource, "dbo.uspPt":

********************************
USE [Patients]
GO
/****** Object: StoredProcedure [dbo].[uspPt] Script Date:
05/29/2007 00:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspPt]
-- Add the parameters for the stored procedure here
@PtID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PtID is null

set @PtID = dbo.usfLastPatient();
-- Insert statements for procedure here

SELECT dbo.tblPt.PtID, dbo.tblPt.ProviderID, dbo.tblPt.OfficeID,
dbo.tblPt.SalutationID, dbo.tblPt.FirstNameID, dbo.tblPt.MiddleNameID,
dbo.tblPt.LastNameID,
dbo.tblPt.SuffixID, dbo.tblPt.MaritalStatusID,
dbo.tblPt.GenderID, dbo.tblPt.BirthDate, dbo.tblPt.SSN,
dbo.tblPt.Admitted, dbo.tblPt.Comments
FROM dbo.tblPt
WHERE (dbo.tblPt.PtID = @PtID)
********************************


As a means to open the single-record form to the last entry,
"dbo.usfLastPatient()" reads as follows:


*********************************************
USE [Patients]
GO
/****** Object: UserDefinedFunction [dbo].[usfLastPatient] Script
Date: 05/29/2007 00:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[usfLastPatient]
(
-- Add the parameters for the function here
--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
DECLARE @LastPtID int

-- Add the T-SQL statements to compute the return value here
SET @LastPtID = (SELECT TOP (1) dbo.tblPt.PtID FROM dbo.tblPt ORDER
BY dbo.tblPt.PtID DESC)

-- Return the result of the function
RETURN (@LastPtID)

END
*********************************************


The above procedures have been working in a stable manner for several
months on a single-machine/single-user prototype, and I'm pretty sure
the same were working on my last formal tests (now some time ago) in a
multi-user environment. The only changes would be that my trial
version of SBS 2003 R2 that I was using melted down and required a re-
installation. I'm getting ready in the next several weeks to fully
implement the system in a new practice.



.

Relevant Pages

  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... you're careful about your database design, though, that may be the way to ... As a result of the difficulties I've had, I try to avoid SQL Server ... Would you consider using an ADP talking across an fast broadbank link to ... A thin client interface has been suggested, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... The client runs a VP from Philly to NYC. ... Would you consider using an ADP talking across an fast broadbank link to ... local database, but ideally we would like to centralise. ... the front end over a VPN connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Recordsource only available to Administrator on clients
    ... Do your users have the right to access this second database? ... On the SBS machine as well as the clients, ... to temporarily set a client user's privileges from, ... parameterized stored procedure, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: How to handle concurrency issue with better performance?
    ... Would you put the timestamp check in the stored procedure on the server ... that have been modified since they were pulled from the database. ... select command). ... client) downgrade very much in using such ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordsource only available to Administrator on clients
    ... On the SBS machine as well as the clients, ... Server instance is readily connected via SSMSE (Server type: Database ... to temporarily set a client user's privileges from, ... parameterized stored procedure, ...
    (microsoft.public.access.adp.sqlserver)
Loading