July 17, 2013 at 2:07 pm
Yes!!!
That will suffice. Thank you so much!!!!!!
It Works!
Public Function GetWindowUser() as String
GetWindowUser = Environ("USERNAME")
End Function
July 17, 2013 at 2:51 pm
kwoznica (7/17/2013)
Thanks for the suggestions but I have not been successful.I used Environ("UserName"), suser_sname(), and select system_user to populate the value in the text box on the form.
I am setting this in the Control Source of the Textbox Data Properties. After doing so it returns a value of #Name? or Admin. I am no logged in as either of these ID's.
....
If you are going to let SQL Server do the work for you, then you need to set the Default Value in the table definition in SQL Server, not in an Access text box. The T-SQL for doing that is:
ALTER TABLE [dbo].[YourTable] ADD CONSTRAINT [DF_YourTable_strWhoEdited] DEFAULT (suser_sname()) FOR [strWhoEdited]
However we usually use the table designer SSMS to set such things as default values, Null or Not Null, etc.
Wendell
Colorado, USA
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 19, 2013 at 6:55 am
Thanks Wendal.
I like that approach to make the backend do the work. I didn't realize suser_sname() was meant to be put in the table definition which is why I was confused. I'll give it a try.
I really appreciate everyone's suggestions!
July 19, 2013 at 8:54 am
There are several advantages to that approach - it works whether the record is created using a form or added at the table level. It also uses the system level authentication ID, so if you have users roaming across several workstations, it still captures the correct name. And it doesn't complicate your form design by using VBA to track things. The one thing it doesn't do is track who last changed the record. That can be done on an Access form, but you can't deal with changes made at the table level. SQL Server triggers are the best way of doing that IMHO.
Wendell
Colorado, USA
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 7, 2013 at 2:54 pm
Wendell,
When I made the change to add suser_sname() as the default value for the field it works well only from my system.
If I launch the access file from another user's system the field does not populate. I have tried setting the user's having the issue in the db_owner role for the database but the same thing still occurs.
Is there a specific permission that needs to be set in order to enable the user updating or adding a record on the form?
Also my system is running access 2013 with sql server 2012 native client odbc driver. The user systems have access 2010 running sql server 2008 R2 native client odbc driver.
Please let me know what you think.
August 7, 2013 at 2:58 pm
Also here is the complete table definition.
USE [GKHumanResource]
GO
/****** Object: Table [dbo].[HRCaseData] Script Date: 8/7/2013 3:59:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRCaseData](
[Id] [int] IDENTITY(100,1) NOT NULL,
[CaseDate] [date] NOT NULL,
[Customer] [varchar](50) NOT NULL,
[HrRepId] [varchar](50) NOT NULL,
[Category] [int] NOT NULL,
[CaseDescription] [varchar](30) NOT NULL,
[CaseStatus] [bit] NOT NULL,
[ClosedDate] [date] NULL,
[Resolution] [varchar](1000) NULL,
[DepartmentID] [smallint] NOT NULL,
CONSTRAINT [PK_ID_Customer_Category] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Customer] ASC,
[Category] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[HRCaseData] ADD CONSTRAINT [DF_HRCaseData_HrRepId] DEFAULT (suser_sname()) FOR [HrRepId]
GO
ALTER TABLE [dbo].[HRCaseData] ADD DEFAULT ((7)) FOR [DepartmentID]
GO
ALTER TABLE [dbo].[HRCaseData] WITH CHECK ADD CONSTRAINT [FK_Category_CatType] FOREIGN KEY([Category])
REFERENCES [dbo].[HRCategory] ([ID])
GO
ALTER TABLE [dbo].[HRCaseData] CHECK CONSTRAINT [FK_Category_CatType]
GO
ALTER TABLE [dbo].[HRCaseData] WITH CHECK ADD CONSTRAINT [FK_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[HRDepartment] ([DepartmentID])
GO
ALTER TABLE [dbo].[HRCaseData] CHECK CONSTRAINT [FK_Department]
GO
August 7, 2013 at 3:23 pm
There should not be any difference based on SQL Server version or Access version. This technique is dependant only on SQL Server, and we have similar tables that work just fine on both versions you mention.
On permissions, we normally give each user dbowner on the database so that we don't have to set permissions on all of the objects - as in general our users should be able to update all the tables. (We do restrict users by presenting the data in forms and do not give them access to the tables.)
Just a hunch, but by chance are they using an Access form to add records that has a different default value set for that field? That seems to me to be the most logical explanation for the behavior you are seeing. To test that you could try adding a record to the table directly without using an Access form. Another more remote possibility is that there is an issue with the UserName in SQL Server - is this Integrated Security or SQL Server Security? Also it would help to know if the field is truly a Null, a series of blank characters, or an empty text string.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 8, 2013 at 6:22 am
Sorry for the false alarm. It was me being a fool. When I added the ODBC connection from access I was importing the data instead of choosing linking. Once I changed that about 3 hours of banging my head against the wall was all due to a simple over sight.
It is working very well now. Thanks for your help.
January 11, 2017 at 11:01 am
How can we capture if someone edits a record without using a trigger. We need a column in the same table. Can you please help me with this.
January 11, 2017 at 11:37 am
sumanth.pathuri (1/11/2017)
How can we capture if someone edits a record without using a trigger. We need a column in the same table. Can you please help me with this.
you should start a new thread to get the best help, since this thread is four years old or so. also, the forum is related to Access, but you really mean SQL, right?
so are you sure you want to paint yourself in a corner with a rule that says absolutely, positively, without a trigger AND you need whodunnit information?
a trigger is the best way to handle that requirement, i would say.
For data changes, you can use SQL Temporal Tables, Change Data Capture to track what specific data changed, Change Tracking to capture which data changed, but neither has who did it information.
For whodunnit changes, SQL Audit or an Extended event has who changed the data, but not the details of which specific data was changed.
I'm under the impression that the only place for both data and user info is at the trigger level, unless someone can correct me on my conceptions/misconceptions.
Lowell
January 11, 2017 at 11:40 am
sumanth.pathuri (1/11/2017)
How can we capture if someone edits a record without using a trigger. We need a column in the same table. Can you please help me with this.
As this is a rather old thread, are you working in the same situation as the original poster? That is, are you working with ODBC linked tables in an Access front-end? Or are you working with an Access ADP that connects directly to SQL Server? Also the versions may be important. In the first case, you can create VBA procedures that are initiated by the OnChange event of the form that update a last changed field, assuming that your form is bound to a table source. However if you allow editing at the table level, and at least some users will probably have that capability, then using triggers is the only effective way we have found to track who and when a record was last changed.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 23, 2017 at 8:26 am
Hi,
There is a type of a Microsoft Project where you do not use linked table or connect via VBA Code.
You can point you stored procedures to the queries..
It has been a long time since I did this but I would appreciate any help.Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 23, 2017 at 10:10 am
Welsh Corgi - Sunday, April 23, 2017 8:26 AMHi,There is a type of a Microsoft Project where you do not use linked table or connect via VBA Code.
You can point you stored procedures to the queries..
It has been a long time since I did this but I would appreciate any help.Thank you.
is this a new question? suggest starting a new thread if it is
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 23, 2017 at 10:25 am
J Livingston SQL - Sunday, April 23, 2017 10:10 AMWelsh Corgi - Sunday, April 23, 2017 8:26 AMHi,There is a type of a Microsoft Project where you do not use linked table or connect via VBA Code.
You can point you stored procedures to the queries..
It has been a long time since I did this but I would appreciate any help.Thank you.
is this a new question? suggest starting a new thread if it is
Sorry but I though I was opening a new post.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 23, 2017 at 10:27 am
WendellB - Wednesday, January 11, 2017 11:40 AMsumanth.pathuri (1/11/2017)
How can we capture if someone edits a record without using a trigger. We need a column in the same table. Can you please help me with this.As this is a rather old thread, are you working in the same situation as the original poster? That is, are you working with ODBC linked tables in an Access front-end? Or are you working with an Access ADP that connects directly to SQL Server? Also the versions may be important. In the first case, you can create VBA procedures that are initiated by the OnChange event of the form that update a last changed field, assuming that your form is bound to a table source. However if you allow editing at the table level, and at least some users will probably have that capability, then using triggers is the only effective way we have found to track who and when a record was last changed.
Does Microsoft Access still suport ADP?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply