1

I am using a Data Macro for one of my Access table's After Insert event. Inside this Data Macro, I am using SetLocalVar to call one of my functions written in vba to insert the same inserted record set into my SQL Database.

It works when I enter data inside Access, however If I insert a record from one of my C# based application, the after insert event wont call my Access function because I am not opening an instance of my Access application.

Might there be a way that, this After Insert event can somehow trigger my vba function even the record is inserted from out side of Access ?

0014
  • 893
  • 4
  • 13
  • 40
  • 2
    No, I don't think you can trigger the After Insert event when you insert a record with ADO.NET. The library involved (OleDB) acts directly on the database file without any knowledge of the existance of an Access application and what an Access macro is. – Steve Apr 21 '17 at 17:08
  • I just did a very simple test, connecting to and updating a table with a Before Update data macro using Visual Studio and the `Provider=Microsoft.ACE.OLEDB.12.0` and it did indeed trigger and run the data macro. This doesn't answer the question about the SetLocalVar, but at least the data macros do run using the ACE provider. – C Perkins Apr 21 '17 at 19:25
  • @CPerkins Thanks for taking your time testing it and yes I am of that it does run the data macro even connecting as you did. However If a function is called from the DataMacro via SetLocalVar the function is not called from it. If there is a way, that comes in your mind, doing it please let me know. – 0014 Apr 21 '17 at 19:29
  • I don't understand how even within Access you are running another function from a Data Macro. Documentation states that "Variables created by the SetLocalVar action can be used only in the macro in which they are defined." Unless you've leveraged some hidden feature or bug, I thought that it was impossible to call / trigger any function outside a Data Macro. That's why Data Macros already have a very limited set of available actions. – C Perkins Apr 21 '17 at 19:33
  • I take that back. I primarily use the Before Insert macros, rarely the After Insert / Update macros. I now see that it can indeed trigger other Named Data Macros, but they are still limited in what can be done. I disagree with Steve's comment that it is "without any knowledge of... an Access macro". Apparently the ACE database engine is indeed aware of and can execute at least some macro actions. Edit your question with more detail. Try exporting your data macro and posting the XML. (Use VBA, like 'SaveAsText acTableDataMacro, "TableName", "C:\PathToFile\DataMacro.xml") – C Perkins Apr 21 '17 at 19:52
  • Perkins - see my answer below. You can most certainly pass and return values between those "data" store procedures. And such data triggers can call saved data macro code NOT attached to any particular table event. (but in most cause such routines will be called from a table trigger - but you can also call such routines from VBA/Access (the client program allows you to call the stored procedures). – Albert D. Kallal Apr 22 '17 at 19:23
  • Kallal - But your answer didn't actually address specific details about the question. 0014 never explained what he meant by SetLocalVar calling a function from a Data Macro. I had already pointed out in my comments that ACE engine will run Data Macros as you repeated. I also had already pointed out doubts that VBA code could be called from Data Macros, but 0014 claims that he has done that, so I was waiting on more detail from the OP before answering a question without sufficient detail. – C Perkins Apr 22 '17 at 21:08
  • @CPerkins I belive Kallal's answer sums up that it is `not possible` to trigger a vba function call via a `DataMacro` outside Ms Access itself. The `DataMacro` details are not important unless its not able to run a VBA function which is what it is asked in the question. If you can help me on this question http://stackoverflow.com/questions/43548020/ms-access-data-macro-return-record-set you can see there the details of what I am trying to accomplish with the DataMacro – 0014 Apr 25 '17 at 15:52
  • @0014 Agreed, Kallal's answer was clear on that one point. But now saying that the details aren't important is strange when you asked about calling a VBA function from a Data Macro... using a "hidden" feature of SetLocalVariable that you didn't explain. I was simply wanting more detail so that your question was complete, especially to someone like me who didn't even realize that trick was possible since the Access UI and documentation hide that functionality of Data Macros. – C Perkins Apr 25 '17 at 17:17
  • @0014 You could use MS Office Automation from another language environment to create a hidden instance of Access. Inserting the record that way would then certainly be able to call any of your VBA functions. Perhaps it does not satisfy your questions depending on how strictly you interpret "out side of Access", but it would technically allow you to do it from another programming environment. – C Perkins Apr 25 '17 at 17:26
  • @CPerkins Yes you are right. That I didn't try that but might work. However as you said you still are opening an Access instance which brings tons of limitations. You need Access to be installed and since you are using `Access Interopt DLL`, probably wont work in any other languages besides C# or MS related language. Besides, this idea would be debatable using Access from outside, since you are opening the instance you technically are accessing VBA from inside. Thanks for your feedback though. – 0014 Apr 25 '17 at 18:14

1 Answers1

3

Data macros are at the “ace” database engine and thus work much like store procedures in SQL server. However if you call + use VBA routines (which is legal), then you introduce a VBA dependency and that setup ONLY works from Access. So if you open a table via ODBC or any other way from FoxPro, or even .net, the Access store procedures will STILL run, but the VBA dependency will not work and does require Access. So you are free to install the database engine (formally JET, now called ACE). Installing the data engine as we done for years in the past does NOT install VBA nor does the database engine use or rely on VBA.

So data macros, and data trigger code will work from say c#. However the calling of VBA code requires Access to be installed (which installs VBA). And more important is the instant you have such triggers call VBA code is the instant you MUST ONLY update the data from Access.

So the looping, and store procedures available in ACE can even call other stored procedures as long as they are only written in the DATA macro language.

Having such table triggers call VBA code thus means that in addition to installing the database engine you ALSO would have dependency on VBA. So while you can call VBA code, such a setup ONLY works when using MS-Access as the client program.

So you have to re-write the VBA code as data macro code if you want updating to occur from c# or other external languages.

You do have “most” VBA functions, but you don’t have recordsets (you use for each).

edit: I should also point out that since you can't call VBA when using ACE from c#, another road would be having the data macro operate on a linked table to SQL server, but data macros cannot operate on linked (external) tables. So your setup most certainly could work if the data was being updated from Access. However using FoxPro, VB, vb.net, c# etc. DOES allows and run data macro code be in these cases you cannot have the data macro code call VBA or any other kind of external code - and external code would be required to update SQL server since data macros ONLY operate on local native tables (quite much the same for SQL triggers also)

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • This code shows how to mimic a For/Next loop, but that has nothing to do with the question. It also says that Data Macros have "most" VBA functions, but it is actually a very, very small selection... definitely not "most". – C Perkins Apr 22 '17 at 21:07
  • @CPerkins I didnt select the answer because of the provided example. It is selected because it states that calling a VBA function via a `DataMacro` is not possible outside of Access. – 0014 Apr 25 '17 at 15:54