Hello Everyone, I am a new user to this forum and I have a question that I posted in another forum that has not been answered.
I am using SQL Server 2005 with a MS Access frontend. How can I trigger an event (like a stored proc or query), in SQL Server with a button click on an Access Switchboard?
My particular issue is that I have a button on a MS Access Switchboard for Data Entry screens. I want to restrict access to certain users so when the button is clicked the user name is checked and if the user is in a certain users group the access is denied. I am using Windows authentication on the SQL Server backend.
Thank You
You need to determine what group your user is in. You obviously know that on the server side but you need to return that value back to your application. One technique is to set your main menu forms recordsource to a stored procedure the return value of which is a one row SELECT providing the single ROLE that the user belongs to ie READER, READWRITE and so on. From your main menu you can then control what they can do based on the return value (it being displayed in a textbox that you place on screen. The below Stored procedure will return such a value if you reference it as the controlsource for your main menu. This gives a double whammy, in that if no value is displayed you know something is wrong ie: like your connection has not succeeded initially. You don't say what file format you are connecting to SQL server with ...ADP or MDB I am assuming ADP? I have procedures for testing user membership on my 2000 box but not as yet on 2005. I am sure there is a sproc available that has been fully compat tested for 2005 but in the absence of that here is the 2000 sproc untested where you pass in as the parameter value the network login name of the user via an access function
CREATE PROCEDURE dbo.usp_Membership
(@mynetworkuser varchar(100))
AS
SELECT susr1.name AS Membership
FROM dbo.sysusers susr1 INNER JOIN
dbo.sysmembers smbr ON smbr.groupuid = susr1.uid INNER JOIN
dbo.sysusers susr2 ON smbr.memberuid = susr2.uid
WHERE (susr2.name = @mynetworkuser)
GO
Jim
6
