If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
CurrentUser issue
Hello all,
After migrating a financial DB from Access to SQL certain functions do not work. Primarily, the Access version of the DB had A table with user records (that they input themselves). A query that gives the CurrentUser() the list of their records only A form that shows the query, and defaults any new record with the currently logged in user's username by having the default value is the UserName field be CurrentUser(). This allows for multiple people to be in the same system and input and view ONLY their own records. So the table is the source, the query provides the filter/view, and the form is the interface. My problem is that the form (because of the query) won't allow new records in SQL. Only the tables were upsized. The Access DB has not changed. The tables are now linked tables. A regular user can input new records into the source table directly, but not through the query. The table is on a SQL server, the query and form are in an Access application. I want to assume that the problem is the CurrentUser() command not working in SQL, but what would I replace it with, and why should I have to, seeing that the default value is located in the Access form and not in the SQL table. Thanks beforehand |
#2
|
|||
|
|||
CurrentUser issue
hi Jacques,
Jacques Latoison wrote: I want to assume that the problem is the CurrentUser() command not working in SQL, but what would I replace it with, and why should I have to, seeing that the default value is located in the Access form and not in the SQL table. Yes, I assume this is the problem, as you have a completely different kind of user management in SQL Server. First of all, you have to decide to use either the security identifier or the user name. http://technet.microsoft.com/en-us/l.../ms191126.aspx I often use the the name. Another important thing is that you setup the users for SQL Server. The best way: - Create a user group for your application in the Active Directory of your domain. - Assign the users to this group. - In SQL Server assign the necessary rights to this group. http://technet.microsoft.com/en-us/l.../aa337562.aspx So you need a table like that in SQL Server: CREATE TABLE [UserSpecificData] ( [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, [ChangedAt] DATETIME NOT NULL DEFAULT (GETDATE()), [ChangedBy] NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()), [UpdateVersion] ROWVERSION NOT NULL, [Payload] NVARCHAR(MAX) NOT NULL ) And you need this view: CREATE VIEW [vw_UserSpecificData] AS SELECT * FROM [UserSpecificData] WHERE [ChangedBy] = SUSER_SNAME ; And instead of linking the table in your front-end mdb, link this view. After linking it, you need to create the primary key information in Access to make the linked view updateable in Access: Dim Sql As String Sql = "CREATE INDEX PK_linkedViewName " & _ "ON linkedViewName (Id) " & _ "WITH PRIMARY" CurrentDb.Execute Sql, dbFailOnError http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx You need this trigger to track changes, just to show you your possibilites: CREATE TRIGGER [tr_UserSpecificData_Update] ON [UserSpecificData] AFTER UPDATE AS BEGIN UPDATE T SET T.[ChangedAt] = GETDATE(), T.[ChangedBy] = SUSER_SNAME() FROM [UserSpecificData] T INNER JOIN Inserted I ON I.[Id] = T.[Id] ; END ; In your case, of course this table seems to be sufficent: CREATE TABLE [UserSpecificData] ( [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, [Owner] NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()), [UpdateVersion] ROWVERSION NOT NULL, [Payload] NVARCHAR(MAX) NOT NULL ) The field [UpdateVersion] of datatype ROWVERSION or TIMESTAMP (deprecated) should exist in every table you have upsized as it simplifies the way Access/Jet handles the update concurrency check. It also avoids some errors which may occur due to the possible usage of imprecise data types. mfG -- stefan -- |
#3
|
|||
|
|||
CurrentUser issue
I'm going to try this out.
Thank you. "Stefan Hoffmann" wrote in message ... hi Jacques, Jacques Latoison wrote: I want to assume that the problem is the CurrentUser() command not working in SQL, but what would I replace it with, and why should I have to, seeing that the default value is located in the Access form and not in the SQL table. Yes, I assume this is the problem, as you have a completely different kind of user management in SQL Server. First of all, you have to decide to use either the security identifier or the user name. http://technet.microsoft.com/en-us/l.../ms191126.aspx I often use the the name. Another important thing is that you setup the users for SQL Server. The best way: - Create a user group for your application in the Active Directory of your domain. - Assign the users to this group. - In SQL Server assign the necessary rights to this group. http://technet.microsoft.com/en-us/l.../aa337562.aspx So you need a table like that in SQL Server: CREATE TABLE [UserSpecificData] ( [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, [ChangedAt] DATETIME NOT NULL DEFAULT (GETDATE()), [ChangedBy] NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()), [UpdateVersion] ROWVERSION NOT NULL, [Payload] NVARCHAR(MAX) NOT NULL ) And you need this view: CREATE VIEW [vw_UserSpecificData] AS SELECT * FROM [UserSpecificData] WHERE [ChangedBy] = SUSER_SNAME ; And instead of linking the table in your front-end mdb, link this view. After linking it, you need to create the primary key information in Access to make the linked view updateable in Access: Dim Sql As String Sql = "CREATE INDEX PK_linkedViewName " & _ "ON linkedViewName (Id) " & _ "WITH PRIMARY" CurrentDb.Execute Sql, dbFailOnError http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx You need this trigger to track changes, just to show you your possibilites: CREATE TRIGGER [tr_UserSpecificData_Update] ON [UserSpecificData] AFTER UPDATE AS BEGIN UPDATE T SET T.[ChangedAt] = GETDATE(), T.[ChangedBy] = SUSER_SNAME() FROM [UserSpecificData] T INNER JOIN Inserted I ON I.[Id] = T.[Id] ; END ; In your case, of course this table seems to be sufficent: CREATE TABLE [UserSpecificData] ( [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, [Owner] NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()), [UpdateVersion] ROWVERSION NOT NULL, [Payload] NVARCHAR(MAX) NOT NULL ) The field [UpdateVersion] of datatype ROWVERSION or TIMESTAMP (deprecated) should exist in every table you have upsized as it simplifies the way Access/Jet handles the update concurrency check. It also avoids some errors which may occur due to the possible usage of imprecise data types. mfG -- stefan -- |
Thread Tools | |
Display Modes | |
|
|