2010 Data macro capture user

#1

I'm thinking about using data macros (BACKEND table level) to track
changes to a table. This may be trivial, but I can't figure out how to
track which user in the FRONTEND made the change to the table so that
I can track what was changed and who changed it. The user would make
the changes via a form.

#2

On Mar 25, 8:23*pm, elgin <sfielg... (AT) hotmail (DOT) com> wrote:

Quote:

I'm thinking about using data macros (BACKEND table level) to track
changes to a table. This may be trivial, but I can't figure out how to
track which user in the FRONTEND made the change to the table so that
I can track what was changed and who changed it. The user would make
the changes via a form.

You could use a hidden form and place UserID in it and create a
Log_Table (UserID,Table,RecordID,ChangeDescription) and insert record
in it for the actions you would like to track.

#3

On Mar 25, 3:58*pm, exebat <srdjan.vasilje... (AT) gmail (DOT) com> wrote:

Quote:

On Mar 25, 8:23*pm, elgin <sfielg... (AT) hotmail (DOT) com> wrote:

I'm thinking about using data macros (BACKEND table level) to track
changes to a table. This may be trivial, but I can't figure out how to
track which user in the FRONTEND made the change to the table so that
I can track what was changed and who changed it. The user would make
the changes via a form.

You could use a hidden form and place UserID in it and create a
Log_Table (UserID,Table,RecordID,ChangeDescription) and insert record
in it for the actions you would like to track.

Thanks exebat.
I currently use a logging table that I populate using VBA on the front
end. However, I was hoping to use Data Macro which runs at the table
level on the back end of my split database. This way, it will track
any changes to the table(s) however they are made; form, macro, or
VBA. Also, once the Data Macro is in place, it is independent of the
front end forms, code, and versions. It's like an SQL trigger. Set it
and forget it.

What I believe I need is a way to track which computer/user's instance
of the frontend initiated the change.

#4

?"elgin" wrote in message
news:3ceb6275-8375-4f1c-be76-32bbf18893b6 (AT) i4g2000pro (DOT) googlegroups.com...

Quote:

This way, it will track
any changes to the table(s) however they are made; form, macro, or
VBA. Also, once the Data Macro is in place, it is independent of the
front end forms, code, and versions. It's like an SQL trigger. Set it
and forget it.

What I believe I need is a way to track which computer/user's instance
of the frontend initiated the change.

Ok, in the back end database, create a dummy table with one column and one
record:

tblDummy:ID (autonumber)
tblDummy

ummy (text)

Add one record to above.

Now, add the following VBA code (you can do this to both front end, and back
end - this code not needed in back end, but if you ever need to edit tables
direct in back end, you need VBA code in both front end + back end:

You can get the current network logon name with:
http://www.mvps.org/access/api/api0008.htm

And, the current computer name with:
http://www.mvps.org/access/api/api0009.htm

Now, build a query like:

SELECT tblDummy.ID, (fosUserName()) AS FUser
FROM tblDummy;

Save above in back end as qryUser

Now add a table trigger to the before update:

LookUp A Record In qryUser
Where Condition = [id] = 1
Alias
SetField (tblCustomers.EditBy,[qryUser].[Fuser]

There is a screen shot of above macro here:
http://nrfr5a.bay.livefilestore.com/...forechange.png

So, the above query could also have computer name in addition to the above
network logon name.

Note that the before change trigger event is quite light weight, and it does
not allow adding records to other tables, but you could well use the above
code in the after update event, or have a last edit + last computer edit
column in the above and then in the after update event, append the data to a
log.

Just keep in mind that the current record in context for the after update
event is read only, where in before change event, you can modify columns.
(and while the current record in the after update event is read only, you
can create a new instance of it if you needed, but I do not recommend as
such)

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

#5

"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote in
news:Cjijp.3015$aV3.284 (AT) newsfe02 (DOT) iad:

Quote:

Now, build a query like:

SELECT tblDummy.ID, (fosUserName()) AS FUser
FROM tblDummy;

Save above in back end as qryUser

You don't need a dummy table at all. This should suffice:

SELECT (fosUserName()) AS FUser

You don't actually need a FROM clause in the SQL statement.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#6

David-W-Fenton wrote:

Quote:

"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote in
news:Cjijp.3015$aV3.284 (AT) newsfe02 (DOT) iad:

Now, build a query like:

SELECT tblDummy.ID, (fosUserName()) AS FUser
FROM tblDummy;

Save above in back end as qryUser

You don't need a dummy table at all. This should suffice:

SELECT (fosUserName()) AS FUser

You don't actually need a FROM clause in the SQL statement.

Oh really? That's no longer a requirement? Very interesting! It's about
time!

#7

Bob Barrows wrote:

Quote:

David-W-Fenton wrote:
"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote in
news:Cjijp.3015$aV3.284 (AT) newsfe02 (DOT) iad:

Now, build a query like:

SELECT tblDummy.ID, (fosUserName()) AS FUser
FROM tblDummy;

Save above in back end as qryUser

You don't need a dummy table at all. This should suffice:

SELECT (fosUserName()) AS FUser

You don't actually need a FROM clause in the SQL statement.

Oh really? That's no longer a requirement? Very interesting! It's
about time!
Darn, that's been the case since A2003 and I never even noticed! Thanks!

#8

"Bob Barrows" wrote in message news:imoofa$5s6$1 (AT) dont-email (DOT) me...

Quote:

You don't need a dummy table at all. This should suffice:

SELECT (fosUserName()) AS FUser

You don't actually need a FROM clause in the SQL statement.

Oh really? That's no longer a requirement? Very interesting! It's about
time!

Well, actually the problem is the syntax does not seem to work for the
trigger. I not looked further, but it either the trigger wants + checks +
needs a table, or some other issue is at play (eg: a bug in the table
trigger system).

So, for the time being, at least in terms of the suggestion, a dummy table
is required.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

#9

Bob Barrows wrote:

Quote:

David-W-Fenton wrote:
"Albert D. Kallal" wrote

Now, build a query like:

SELECT tblDummy.ID, (fosUserName()) AS FUser
FROM tblDummy;

Save above in back end as qryUser

You don't need a dummy table at all. This should suffice:

SELECT (fosUserName()) AS FUser

You don't actually need a FROM clause in the SQL statement.

Oh really? That's no longer a requirement? Very interesting! It's about
time!

I'm not going to dump my OneRow table anytime soon. One
common usage is to add an ALL row to a combo box:

SELECT ID, Descr FROM lookuptable
UNION ALL
SELECT 0, " ALL" FROM OneRow

Leaving out the FROM OneRow provokes an error about at least
one table is needed.

--
Marsh

#10

Marshall Barton wrote:

Quote:

Bob Barrows wrote:

David-W-Fenton wrote:
"Albert D. Kallal" wrote

Now, build a query like:

SELECT tblDummy.ID, (fosUserName()) AS FUser
FROM tblDummy;

Save above in back end as qryUser

You don't need a dummy table at all. This should suffice:

SELECT (fosUserName()) AS FUser

You don't actually need a FROM clause in the SQL statement.

Oh really? That's no longer a requirement? Very interesting! It's
about time!

I'm not going to dump my OneRow table anytime soon. One
common usage is to add an ALL row to a combo box:

SELECT ID, Descr FROM lookuptable
UNION ALL
SELECT 0, " ALL" FROM OneRow

Leaving out the FROM OneRow provokes an error about at least
one table is needed.

Oh! So they fixed it in the query builder but not in the data source editors
....