Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Update Field to current logged in user ID with new record command button Expand / Collapse
Author
Message
Posted Wednesday, August 7, 2013 2:58 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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



Post #1482073
Posted Wednesday, August 7, 2013 3:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 148, Visits: 543
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!
Post #1482078
Posted Thursday, August 8, 2013 6:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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.
Post #1482259
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse