CurrentUser issue

[Jacques Latoison]

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

 

[Stefan Hoffmann]

hi Jacques,

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/library/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/library/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/library/aa140011(office.10).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 <--

 

[Jacques Latoison]

I'm going to try this out.
Thank you.

hi Jacques,

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/library/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/library/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/library/aa140011(office.10).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 <--