473,289 Members | 1,780 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Trigger SQL Server event from MS Access

6
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
Aug 20 '07 #1
3 2146
ck9663
2,878 Expert 2GB
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

I think this is more of a front-end handling than back-end...Handle it on your button OnClick event...
Aug 20 '07 #2
Jim Doherty
897 Expert 512MB
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
Aug 23 '07 #3
pgill
6
Hi Jim,

Thank you for your detailed explaination. I am using MDB format. I have been programming the Switchboard by SwitchboardID along with the ItemNumber to determine which switchboard the user is trying to access. If it is the Data Entry switchboard I call a stored proc on the server. This is about as far as I have got so far. I appreciate your point of view on this and I will explore it further.

Thanks Again.
Aug 23 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Tushar | last post by:
Hello, I am working on a client server application involving TOMCAT as Web server, MySQl as the DB and combination of JSP, Servlet, and JavaBeans to access and write Data back. There is...
9
by: Lauren Quantrell | last post by:
Is there a way to create a text file (such as a Windows Notepad file) by using a trigger on a table? What I want to do is to send a row of information to a table where the table: tblFileData has...
4
by: d0wsdkn02 | last post by:
I have an ASP.NET application that performs ADO.NET commands that cause triggers to be run in SQL Server 2000. My question is how would I best access the user ID from my custom .NET Principal in...
6
by: Jchick | last post by:
Im a newbie with a sql table in a MSDE database that contains fields of CustName, BrokerName, Type, Status. I am trying to write a trigger that does this: 1. When a new record in a table is...
4
by: Jim Devenish | last post by:
We have an Access database with Access front-end to Access back-end. Another company has installed a separate application using SQLServer. At present certain information in the first application is...
2
by: Tom_F | last post by:
To comp.databases.ms-access -- I would like to trigger an event when I close a form -- but ONLY when the data in the "RecordSource" table has been updated. I tried using the AfterUpdate event,...
0
by: wugon.net | last post by:
Hi , Anyone know how to monitor db2 trigger activity ? We suffer some trigger issue today and we try to monitor trigger's behavior use event monitor and db2audit, but both tools can not get...
5
by: Hubert Trzewik | last post by:
Hi, I want to have all-in-one trigger, defined like this: CREATE TRIGGER MyInsertDeleteUpdateHandler ON MyTable FOR DELETE, INSERT, UPDATE AS BEGIN (...)
1
by: daonho | last post by:
I tried to use javascript to trigger up the button click function when user press enter key from the textbox. This function work fine with a single button click such has login page. However, if the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.