I created a Database (MS Access) and a frontend with JavaFX.
In one table, I implement a data macro, which trigger INSERT Events in a different column (CurrentUser()-Function). I tested the data macro in different ways (created a row directly in MS Access, created a VBA-Code with an SQL-Insert-Query), and any time my Trigger works.
Also my Java-Frontend is doing fine (SQL INSERT to MS Access). But the Problem is: The data macros doesn't work on this way.
I suppose, there's a correlation between the backend-update from MS Access and the data macros, because any time when I do an SQL-Insert-Command with my JavaFX-Frontend, MS Access didn't show me the new Row in the table (Refresh of the table can't fix this issue). I need to close MS Access first to see my new table-inserts (certainly without a Trigger-insert :-)).
Is there any possibility to fix the problem?
I would appreciate any help! Thanks
The so called data macros are of course much like table triggers. They can and should work without having to launch or even have a copy of Access running on the computer.
However, if the data macro uses “any” VBA function, then such a macro will ONLY work if you launched Access.
So you have to remove the use of =CurrentUser() since that seems to be VBA function.
So the INSTANT you start using VBA functions, that is the instant that those table triggers, events and code you write will NOT work. (ie: not work unless run from Access). So data macros DO work JUST FINE if used by other systems (FoxPro, Vb6, or whatever). It just that you can't introduce VBA code when you do this.
While you “can” call or use VBA code from those table triggers, it is STRONG recommended that you do not do this.
So if you open the Access database say with Vb6 vb.net, FoxPro etc., you are able to update data, and the proceeidal macro code you have WILL run and execute.
However, that Vb6, FoxPro (your java) etc. does NOT have use of VBA code (Visual Basic For Applications), and thus is not available to the data engine.
If your table events code (data macros) uses any external VBA code, then you can ONLY use Access as the means to update such table, since any other system will not have VBA libraries and code available.
In fact, you can install the jet (now called ACE) as a 100% separate install. So the Access database engine (ACE) does not require VBA to be installed on the target computer for that procedural table code you write. Nor does a copy of Access have to be installed.
So if your data macros reference, or use any external VBA function, then the ONLY client that can make updates to the tables will be Access, since no other interface can provide the “expression service” that will resolve to the VBA code library and system.
And there is no data macro function that gets the current user. The only way to achieve this would thus to have some code on startup set the “current user” into a table (say 1 record). The data macro could then read that 1 record from the table to get the “current user”. Of course, this setup would not work in a multi-user setup, but for a local file, this could be a “kluge” type of soliton.
This also means that your startup code (VB6, Java etc). you are writing code in would have to update that “user” table with 1 row that has the current user – you can’t get this information from a data macro to my knowledge.
However, while current user is NOT available in a data macro, it is available in a standard non table macro.
However, data (table) macros ONLY have local vars. If a data macro could call user macros, then you could do this.
So you can’t call or use VBA code from data macros – they introduce an external dependency to VBA which is not available unless Access is loaded.
Data macros are stand alone routes – and they do run even with updates occur outside of the Access front end application. However the data engine does not load or have use of VBA when used outside of Access front ends.