2

Is it possible to call C# code in some way from an MS Access database file (not the Access application)?

My scenario is when something will happen in some table I must call C# code (for example a web service).

I read that MS Access doesn't support triggers, but it has procedures or something called Data Macros.

I don't yet know the Access version.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
templaris
  • 221
  • 5
  • 11

2 Answers2

3

Data Macros was introduced in MS Access 2010 and yes, you can call dll functions created in C# from VBA code. Data Macro calls VBA function in Access, VBA calls C# dll function or run external application using shell commands.

UPDATE

Unfortunately it's impossible to run any VBA code from DataMacro if used non-Access frontend, Access needs environment for code execution, if table changed thru ODBC by non-Access application, environment doesn’t exist, and execution will fail.

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • If Access database before 2010 used just as backend for non-Access frontend, you can call .net code in frontend only, there is no way to trigger any code execution on backend. – Sergey S. Sep 17 '16 at 08:30
  • Ok i can create AfterInsert macro inside database. But how can i run external dll from this event? I dont see any Action like RunCode or something. – templaris Sep 17 '16 at 12:55
  • See example, for instance, [here](https://stackoverflow.com/questions/39224308/non-blocking-toast-like-notifications-for-microsoft-access-vba) - the same as you can call Windows API functions, using `Declare` – Sergey S. Sep 18 '16 at 05:44
  • Ok, but I do not have Access Forms. I have possibility only to create AfterInsert macro. RunCode is available only from some buttons events etc. I have only Access backend. When I am creating AfterInsert macro there is no option to choose RunCode function – templaris Sep 18 '16 at 06:55
  • Yes, `RunCode` is not available, but you can call your custom VBA functions everywhere where it's possible, for instance in `If` commands or `SetField`actions. – Sergey S. Sep 18 '16 at 10:44
  • SetField = MyVBAFunction(parameters) ? Can you provide me some documentation about how to use VBA in Access AfterInsert event? I am so confused looking for this, cant find anything about this – templaris Sep 18 '16 at 11:08
  • 1
    Just made some tests, looks like it's not possible to run VBA code without Access frontend. I use VBA calls from Access backend, but VBA code located in Frontend though. If I try to change backend data directly on backend, it requres VBA function code on backend, if I change table data using ODBC from non-access frontend, it generates error. So, looks like my answer is not relevant for non-access frontends and cannot be used in your case, sorry. Access is desktop database, it has no environement for code execution. – Sergey S. Sep 18 '16 at 11:22
  • Correct - a data macro can call VBA code, but only if you using Access as the front end and application development tool. The data engine without Access does and can have store procedures and procedural code run (a data macro), but those macros are engine level code - and that code does not have the ability to call external code routines (with the VBA exception noted). So while the Access "ACE" database engine does now have triggers and store procedures - such procedures cannot call external code or .dll's. – Albert D. Kallal Sep 19 '16 at 16:44
2

Further to Sergey's answer, event-driven Data Macros in Access can only call VBA functions if

  1. the table is being updated from Microsoft Access itself (MSACCESS.EXE), and
  2. the VBA code is available to the Access database file where the table update is invoked.

So, for example, if a Before Change data macro calls the following function to retrieve the current user's name (so it can insert the name into a field in that table, e.g. [CreatedBy] or [UpdatedBy])

Public Function GetUserName()
    Dim wshNet As Object  ' WshNetwork
    Set wshNet = CreateObject("WScript.Network")  ' New WshNetwork
    GetUserName = wshNet.UserName
    Set wshNet = Nothing
End Function

then the following conditions apply:

Updating the table from a non-Access application

A non-Access application (e.g., a .NET application using System.Data.OleDb or System.Data.Odbc) simply cannot update the table. It will throw an error saying

The function 'GetUserName' is not valid for expressions used in data macros.

 

Updating a linked table from an Access front-end

An Access front-end can update the table provided that the VBA code for the function is available to the front-end file (.accdb, .accde, etc.). The front-end file cannot directly run VBA code that is stored in the back-end file (where the table with the data macro is located). We need to either

  • copy the VBA module(s) from the back-end file to the front-end file, or
  • use a VBA reference in the front-end file to include the code from the back-end:

Reference.png

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Ok one more question to this. In expression builder is section called Web Service - I cant find any documentation. Name suggests that macro can connect to the Web Service. But I am in mistake of course? – templaris Sep 18 '16 at 19:07
  • That seems to be one of the Access features that is tied into its Web/SharePoint integration and is not well documented. There is a "teaser" Microsoft Support article [here](https://support.office.com/en-us/article/Create-a-Web-Service-data-connection-5ce2738f-bf36-4490-a015-d1745d102bb8) that is very short on details, and there is a thread [here](https://social.msdn.microsoft.com/Forums/office/en-US/45e4904a-64af-40bf-bcd0-ec7e5df175a9/access-2010-linking-to-data-services-what-is-a-data-services-connection-definition-xml?forum=accessdev) that shows how not-well-understood the feature is. – Gord Thompson Sep 19 '16 at 00:20
  • 1
    I should also add that the web service reference is NOT some kind of ability to consume a web service definition WSDL, but a specific data web service centered around sharepoint. So Access as a tool does not have tools or features to consume a web service in the general IT sense of the term. – Albert D. Kallal Sep 19 '16 at 16:42