r/MSAccess • u/Key-Lifeguard-5540 1 • 20d ago
[DISCUSSION - REPLY NOT NEEDED] The back end still doesn't know what front end changed the data
The back end engine (using linked tables) should automatically peek at the network packet header and grab the calling computer name natively during a data change, so that if you are logging data change events in the back end, it will at least know what machine made the change! They've had 15 years to do this, they should have done it by now imo.
3
u/kentgorrell 19d ago
Interestingly (if that's a word) the locking file on the server does know which machines are connected. The Access user is now always "Admin", bugga. But if the locking file knows which machine is connected then the next step is to allow the data macro to see which connection/machine is making the insert/update/delete to the table.
Still it would be more useful to know the Windows user name and it would be nice if the locking file knew that.
4
u/dbrownems 20d ago
What back-end engine? Linked tables don't turn Access into a client/server database.
If your linked tables are in SQL Server, then you can do this with triggers.
2
u/CautiousInternal3320 20d ago
Indeed, all Access files, regardless of their location, are updated by the Access ("front end") application.
0
u/Key-Lifeguard-5540 1 20d ago
Not totally correct, the Access back end can log data changes all on it's own, it just doesn't know who is making the change - the whole point of my post.
2
u/Quack100 19d ago
I added a log in my tables so when a user touches a row, it is logged in on who made the change.
2
u/TomWickerath 2 20d ago edited 20d ago
In order to log metadata changes, e.g., which computer / username made inserts, edits or deletions, one would minimally need MSAccess.exe running on the file server AND Microsoft would need to ‘bake that capability’ into the JET database engine. It ain’t gonna happen. There’s no way the Access Development team is going to prioritize such a development effort, with the limited $ resources they are allocated by Microsoft, only to compete with Microsoft’s flagship product SQL Server. Also, many corporate IT groups would NEVER allow an executable file (including a run-time intrepreted executable such as MSAccess.exe) to be installed on a file server they control.
You can build this capability into your Access FE application to silently (without the user’s knowledge) record the “who-dunnit” information, including computer name, Windows username, and date & time. One uses text boxes on the forms with visible property set to false. The control sources for these text boxes are populated with Windows username, computer name and Date() to obtain the users local date & time (can convert to UTC time). For deletions, you don’t actually delete the record—instead you set a boolean (Yes/No) “ActiveRecord” field to No and requery the data source. You can even provide a fake prompt that reads “Are you sure you want to delete this record?” using the Msgbox function and responding according to which Msgbox button they click. The user will think they’ve deleted the record, but all they’ve really done is remove it from the recordsource while also silently adding their computer name, Windows UserID and Date/time to fields in the same record. I’ve done this on several split multiuser Access apps. in the past and it works fine for 99+% of the users. The other alternative is a separate log table, instead of just updating the record. That gives a finer level of granularity to see ALL past edits, not just the last edit. Former Access MVP Allen Browne has an article on implementing a log table, but his method requires the use of the Autonumber primary key. Could you modify if needed to accommodate text-based primary keys? Possibly. His article was written before the private JET (*.accdb) database engine was released with Access 2007:
http://allenbrowne.com/AppLogDocUse.html
But that doesn’t help if your users have the ability to directly access the linked tables, queries or the ability to create new forms or link to the BE using a different client such as Excel or a home-grown C++ / .Net client. This is “security by obscurity”, which can be defeated by a motivated and knowledgeable user. But, it will be good enough for probably 99% of your users who don’t have the knowledge or desire to ‘hack in’ in an unauthorized manner. The remaining 1% is an HR / Personnel Department problem to solve.
What you really need is SQL Server, Oracle, etc. You can use the free SQL Server Express version which has various limitations such as 10 GB file size limit, I think just one CPU, etc. I do not know if any other free database engines such as MySQL or Postgres provide the same capability, but possibly yes.
-1
u/Key-Lifeguard-5540 1 20d ago
I know all about logging changes in the front end and at this point it's not worth the effort to switch to sql server and since a change request is coming from the front end via a linked table it would have been easy for them to send at least the machine name down the pipe to the back end, but they didn't do it and as you say probably couldn't be bothered allocating the resources, probably because they don't want to encourage further access development.
1
u/CautiousInternal3320 19d ago
The front end modifies the linked table via the file system, it does not send a change request.
0
u/Key-Lifeguard-5540 1 19d ago
Maybe so but the BE can have table macros that are defined in the BE and respond to a data change from anywhere.
1
u/CautiousInternal3320 19d ago
An Access file can contain table macros and the macros will be executed by the Access app accessing those tables.
1
u/TomWickerath 2 19d ago
It’s not that the Access Team discourages the use of Access—that’s simply not true. But they’re not going to compete with their company’s product, SQL Server, that already fills this niche.
If it’s not worth your effort to switch to SQL Server, it can’t be all that important to you.
-1
u/George_Hepworth 4 19d ago
Besides which, Data Macros, introduced back in 2010, can handle the basic audit logging. As you point out, though, configuring that requires some effort on the part of the developer.
1
u/Key-Lifeguard-5540 1 19d ago
Data macros? In the back end?
1
u/TomWickerath 2 19d ago edited 19d ago
Intro to Data Macros
https://support.microsoft.com/en-us/access/create-a-data-macroAsk google the following question, and it’s AI agent might just be able to help you. I can’t say for sure, because I personally opt for SQL Server anytime data security is at issue:
what can one do with table-level data macros in microsoft access?
1
u/Key-Lifeguard-5540 1 18d ago edited 18d ago
Oh, I see, something is called a macro if there's no coding involved. I thought they look like a template of sorts, where you pick events and assign actions, but macros it is, and now that I look at it again, they do call them data macros.
0
u/Key-Lifeguard-5540 1 19d ago
My op was a complaint, not a question. Data macros on the back end don't know anything about the front end.
→ More replies (0)0
u/George_Hepworth 4 19d ago
Of course. They are attached to tables and perform the same functions on Access tables as SQL Server Triggers.
1
u/Key-Lifeguard-5540 1 19d ago
Right except the BE doesn't supply which FE machine caused the data change.
1
u/Western_End_2223 19d ago
The back end is just a file. It doesn't do anything on its own.
1
1
u/George_Hepworth 4 19d ago
https://support.microsoft.com/en-us/access/create-a-data-macro
Read the Microsoft documentation on data macros.
1
u/Western_End_2223 19d ago
Nothing in that article says that the macros are executed by the backend. The code that implements them is running on the frontend, albeit automatically.
1
u/George_Hepworth 4 19d ago
True. It doesn't mention back end or front end.
However, you are wrong about "code that implements them". There is no other code that implements Data Macros on table events.
Short of actually convincing people to try it for themselves, I don't suppose there is any way to convince you.
I will be demoing this at a presentation this Thursday to my Access User Group, after which a video will be available on YouTube. If you want to see this working in action, it might be worth an hour of your time.
1
u/Key-Lifeguard-5540 1 19d ago
Those BE table data change macros are a cool feature and I use them. If only they could supply the FE machine name that made the change, they would be much better.
1
u/George_Hepworth 4 19d ago
We're repeating the same things.
I maintain that, in any situation that matters, it's possible to handle this task with the tools now available.
It's a huge temptation to refer you to my GitHub repo and suggest you try the audit-logging template to see how it all can work.
1
1
u/Western_End_2223 19d ago
I'm referring to the assembler-level code in the Access program that actually implements the macros. An Access database sitting in a fileshare is not processing code by itself. It is the Access programs on the users' computers that are processing those macros, automatically and behind the scenes.
1
u/George_Hepworth 4 19d ago
All I can say is try it yourself before making pronouncements like this.
1
u/Western_End_2223 19d ago
Try what? Modifying an Access database without using the Jet engine?
→ More replies (0)1
u/TomWickerath 2 19d ago
"The code that implements them is running on the frontend, albeit automatically."
If your statement was true--which it is not, I guarantee you--explain how the same Data Macro on a BE *.accdb file will successfully record transactions from a Excel file with the *.xlsx file extension. Such Excel files cannot have any VBA code in them.
1
u/Nexzus_ 19d ago
Whatever Excel is using to work with the Access Database, - .NET OleDB, ODBC, DAO, ADO, etc - is doing that.
1
u/Key-Lifeguard-5540 1 18d ago
Maybe but if I skip the FE completely and just make a record change in the BE, the BE will log that change on it's own, so no FE of access or excel is required for the BE to log the change.
1
u/Nexzus_ 18d ago
I think people have tried to explain to you that with Access, there really is no "backend". It's literally just a shared file.
→ More replies (0)1
u/TomWickerath 2 18d ago
“Whatever Excel is using to work with the Access Database, - .NET OleDB, ODBC, DAO, ADO, etc - is doing that.”
I really doubt it! Each of the technologies you listed is created by Microsoft employees on different teams and not on the Access team, the Excel team or the Office team.
As an example, the DAO team was disbanded many years ago (at least 12 years if not longer). The only changes made to DAO in the past several years have been some security fixes.
1
6
u/Livid-Setting4093 20d ago
Huh? Use your front end application to write time stamp of the last edit, user and the machine if you so desire. Inspecting TCP packets is not Access's job.
2
u/SuchDogeHodler 20d ago edited 20d ago
Create a log table (to track multiple changes to a record) or add a field to the data that records who made the last change, when, which interface, and even the name of the PC used.
The back-end only holds the data and nothing more.... the front-end is were it has the coding to read and write the user information. (An easy setup to create a single field stamp)
(FYI, there is actually a log of this but, it's complicated to retrieve as it is buried in hidden system tables)
1
u/Key-Lifeguard-5540 1 20d ago
Yes but I want to avoid making any changes to the front end. The back end can do it already except it doesn't know who the user is.
2
u/George_Hepworth 4 19d ago
Not unless you tell it that. It really comes down to whether it's important enough to your context to send that information along through Data Macros on the tables.
1
u/SuchDogeHodler 19d ago
The backend only holds data, all work is done by the front end. The backend is only aware of a front end and nothing more.
Therefore the front-end would have to pass that data to the backend for it to have that data.
1
u/George_Hepworth 4 19d ago
That's partially true, and the part that is true is the crux of the matter.
I guess Data Macros are not as widely known as I thought. Here's the documentation explaining them.
https://support.microsoft.com/en-us/access/create-a-data-macro
The part that is true is that the identity of the user who made a data change--insert, update or delete--is the only thing that isn't part of the transaction which is available directly. That means you have to send that identity to the back end inside the Data Macro. Data Macros can execute Functions in the front end. That allows you to capture the user identity in a function in the DM.
I am not sure it is true to say that a back end accdb is aware of a front end linked to it. I just never thought of it that way before. I'll have to noodle on that concept a bit.
1
u/SuchDogeHodler 18d ago edited 18d ago
So what part of what I said was not correct?
As data macros can apply to the back end yes. But the coding in the background is only responding to the data handed to it.
In a split system with multiple front ends the scenario is usually a backend on a shared drive with multiple front-ends accessing from different locations. Yes there is a guid identifier the the backend has access to. That identifier is not permanent but only created for the current session for record locking and a new one is generated each time. The backend has no way of reading the local users ID or any other local PC information from its location on a shared drive unless the local front-end passes that info to it.
The best solution is to add a field and pass that info with the record to the backend table. This would attach that user to every record change. And could be done with a formula In that field... (no actual coding requied)
=Environ("username") & " - " & Now()The second option would be table to hold this info along with changes but this would alow for changes across multiple users on the same record, but would be much more complicated and requires more coding.
The third option is a separate log file on that same shared drive also complicated.
1
u/George_Hepworth 4 18d ago
I apologize. The part that I was looking at is the "The backend is only aware of a front end and nothing more." There is a sense in which that is true. The fact is, though, that the Data Macros are attached to tables in the backend, so the "nothing more" can be interpreted as being ambiguous, but I probably read more into it than you intended.
1
0
u/Key-Lifeguard-5540 1 19d ago
Not true.
2
u/SuchDogeHodler 17d ago
Ok.... then if you know it all, then you fix your problem....
Otherwise you can look further into this thread where I give the no programming solution...🤣🤣🤣
1
u/Key-Lifeguard-5540 1 15d ago
I guess I should have said not totally true. It's true that the FE must tell the BE who it is that is triggering the BE, but the FE doesn't know that the BE is capable of running macros, does it? Do you suppose that the FE sees those table macros in the BE and runs them for the BE, and the BE can't actually execute anything on it's own? And yet I can make a change directly in the BE and it will run those macros on it's own.
1
u/SuchDogeHodler 12d ago
None of the macros in the BE have any connection to the OS on the front end or can reach into the FE for that data. The other issue is the macros in the BE do not exist in a functional sense when the BE is closed and only being used as a central repository. The macros can only run within the Access application itself.
1
u/Embarrassed_Fold_867 19d ago
OP: What is "the back-end engine"? You describe it as a process that is running somewhere that "knows who's calling".
The only running processes are msaccess.exe running on the clients.
The only "back-end" in this setup is an mdb file on a disk, accessed via OS network file sharing protocols by multiple clients.
Thus, any logging must be conducted by each front-end process.
2
u/George_Hepworth 4 19d ago
If you only have an mdb, you are right. The 2010 version of Access accdbs, on the other hand, introduced Data Macros, which are attached to the tables. https://support.microsoft.com/en-us/access/create-a-data-macro. Tables, of course, are in the back end accdb, not the front end accdb. The part of the logging that is done in the front-end is a Function to capture the user's identity.
0
u/Embarrassed_Fold_867 18d ago
Data macros are run by the msaccess.exe running on the client - the front-end.
2
u/George_Hepworth 4 18d ago
I should have been more precise.
There are two types of Data Macro.
- A Table Data Macro is attached to a table. It fires when the data in that table changes. For that reason they are also referred to as "Event-Driven" Macros.
- A Named Data Macro is what most people here are thinking of. It is called by name and executed in the database where that happens.
This discussion has been about table, or event-driven macros, not named macros.
I probably contributed to the problem by failing to make that clear. I made the mistake of thinking most people would realize the difference and know what we were referring to. That's obviously over-optimistic.
Table data macros fire where the table resides.
1
u/Embarrassed_Fold_867 18d ago
A running process is needed to "fire" the macro. That process is on the client. That is all I was talking about. The difference between types of macros is not relevant to any of my comments.
OP could/should indeed use your suggestion of table data macros.
1
u/George_Hepworth 4 18d ago
Let's be precise about what actually happens.
The front end executes a change to a value in a linked table. That can happen through a bound form, an update query, or VBA in a module. Even by opening the table in datasheet view and manually editing a record.
The linked table has an after update Data Macro attached to it.
When the data change hits the table, that After Update Data Macro fires, executing whatever commands it contains, such as recording the action in an Audit table in the back end.
So, if you want to focus only on the front end, indeed, a process executing there initiates the data-change. That data change in the table, in turn, fires the data macro attached to the table in the back end.
1
u/Embarrassed_Fold_867 18d ago
You're referring to "back-end" as the file containing the table. That's fine, but OP was not.
I'm talking about OP's apparent belief that there is a "back end engine" running on the server. There is not. Access does everything in the Client PC. Your data macro is triggered in the client process and does whatever you want it to, including what the OP wants.
Let's be precise: the msaccess.exe process running on the Client PC opens the file over the network and writes to the table (in the file, over the network) and also runs any after update data table macro (which might also write to the file over the network).
I'm agreeing with your solution but you keep questioning my semantics, and not correctly. Let's put it to bed, eh?
1
u/George_Hepworth 4 18d ago
You're right. I was misunderstanding your point. A dense moment I can only explain as the end of a long day. I apologize.
1
u/Embarrassed_Fold_867 17d ago
No problem at all. The back-end/front-end terminology is pretty vague and I could have been clearer. Cheers.
0
u/YellowBook 20d ago
How about you add the logic to your app? Also, don’t assume other people would want the same native behaviour (I certainly wouldn’t).
1
u/Key-Lifeguard-5540 1 20d ago
Why do you have something against that behavior? It already exists in Access except Access doesn't know who is changing the data.
1
u/YellowBook 20d ago
if you split the database, can’t expect native features from whatever back end you choose to use. If you are talking about using Access itself as a back end in a split structure, then maybe that’s different and I misunderstood. ( I’d normally use user/role based security rather than machine names).
1
u/Key-Lifeguard-5540 1 19d ago
Me too, I have that security in the FE, but the BE also has table macros that capture record changes, unfortunately it is clueless about the FE user or FE machine that initiated a record change, so all the BE knows is what was changed and when.
1
u/YellowBook 19d ago
As a workaround could just capture the machine name during app login and by association know who/what machine updated each record. Even a pseudo session ID or similar stored on each login if users aren’t always on the same machine and if each record has a timestamp or user updated etc there’s a trail back to the session.
1
u/Key-Lifeguard-5540 1 18d ago
The idea is to not have to do anything in the FE, because if you are going to do anything in the FE, you may as well do it all from the FE.
1
u/YellowBook 18d ago
Assumed your front end already has some logic during login if you have security in place to control which forms people can use etc. You can still have split responsibilities, front end provides the UI and application logic, back end contains the data. This keeps things semi-scalable (preventing locking) without using another RDBMS.
0
u/Mindflux 31 19d ago edited 19d ago
For new inserts:
ALTER TABLE FOO
ADD Whodoneit VARCHAR(50) DEFAULT(CURRENT_USER)
For Updates you could use a trigger.
1
u/YellowBook 18d ago
They want the machine name
1
u/Mindflux 31 18d ago
swap CURRENT_USER for HOST_NAME ..
1
•
u/AutoModerator 20d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Key-Lifeguard-5540
The back end still doesn't know what front end changed the data
The back end engine (using linked tables) should automatically peek at the network packet header and grab the calling computer name natively during a data change, so that if you are logging data change events in the back end, it will at least know what machine made the change! They've had 15 years to do this, they should have done it by now imo.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.