Link MS Access to Visual Studio Code with Custom made plug in

Mathias Z 1 Reputation point
2022-02-16T22:18:41.207+00:00

Hello,

I'm exploring the possibilities of connecting MS Access , the VBA backend of the application , with VS Code. The intention would be to create modules, classes and use existing form controls from within the MS Access application in VS Code. My main purpose would be to use VS Code as a modern IDE instead of the current standard VBA Ide in MS Access. I have to be honest and admint that i don't know where to start. I know that tools exist like MZTOOLS and RUBBERDUCK, and these 'connect' as a COM add-in with MS Access. I wonder if it would be possible to build an add in that lets me communicate with VS Code. I also know this is not the main intention of MS Access, and so one... butt i'm looking to gain more knowledge about how the MS Access front end is connected to the Microsoft Visual Basis for applications IDE.

Possible Scenario's would be :

1)

  • Create a new form in Access throught the standard front end in the application
  • Copy / link the form back end with VS CODE in a seperate file
  • Modify the code in VS CODE and return it back to MS Access thought the to be made ADD-IN
  • Create a new module in MS Access throught the standard front end in the application
  • Copy the empty module to an intermediate file , edit it with vs code and return it back to MS Access with a push of the button.

It would be great if you could guide me in a starting direction or give away some tips and tricks about how to start a project like this.

Thanks in advance

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
849 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Fabrice CONSTANS 0 Reputation points
    2024-01-25T20:18:39.1066667+00:00

    Bonjour, il existe 2 commandes vba access non documentées : savetotext et loadfromtext. Elles exportent les objets au format texte (forms, report, moduje vba). Sauf les tables et les requêtes. Il existe des compléments ms access sur github (msaccess vcs par exemple, décliné en plusieurs forks) cette partie ne sera pas à faire. Reste l'intégration à vscode et l'autocompletion à realiser. Concernant vbe, depuis des années beaucoup d'utilisateur de la communauté demande des évolutions mais sans aucune réponse de l'equipe microsoft en charge. Cordialement

    0 comments
  2. Albert Kallal 5,226 Reputation points
    2024-01-29T17:36:09.77+00:00

    Hum, I don’t think this would work well even if you did get this to work.

    Have you ever pasted VBA code into the Visual Studio editor? The results are VERY poor. You don’t’ get VBA syntax checking, you don’t get intel-sense (autocomplete), and the VS IDE has no awareness of any of the other functions and code you have. So, even typing in the name of some sub or function? You don't get in the editor any intel-sense like you would in the VBA editor.

     So, I fail to see any kind of benefit here?

    If VS and it’s IDE could be made aware of VBA code and functions, and do syntax checking, then this would be a great idea. However, all you going to get is a nice text editor, and NOT a code editor.

      So, my view on this matter is to use VS for extending Access, and VBA. So, such types of code can and should be written in .net code.

    A good example?  Access out of the box can produce PDF files (from reports etc.). However, Access does not have the ability to take several PDF’s and merge them.

    While a few utilities have floated around for years in the Access community (such as Stephan Laban's pdf merge), that library only works for Access x32 bits. With office installs now defaulting to x64 bits, then that library does not work.

    So, how about writing the PDF merge code in .net?

    And with .net, then that code can run as x32 bits, or x64 bits and work with both versions of Access.

    And oh, just wow, google PDF merging in .net, and you get 100's and 100's of options and examples!!! So, once again, being able to leverage the .net eco system in VBA provides huge benefits. And the other bonus is that .net code can be used by Access x32, or Access x64 bit version!

     So, you can find my free PDF merge files for Access written in .net here: [http://www.kallal.ca/Articles/Pdf/Merge.html]

      So, for above, I used PDFSharp, and now I have a easy to use pdf merge from my VBA code. (and is while we are at this? Is not NuGet in VS one of the most amazing features of VS?). So, this sharing, and super ease of pulling new code libraries into VS is among top reason for using VS.

    In other words, not much benefits from trying to edit VBA code in VS. You only gain a text editor, and one without any syntax error checking, or autocomplete while you code.

    So, I tend to flip the approach around.

    The goal THEN becomes this:

    How can I easy, and I mean REALLY easy consume and use .net code in ms-access?

    How can I do this without having to register .dll’s, and doing all kinds of setup or isntqller code to register the .net code to be used from Access?

     Well, the answer is you don’t have to do anything more then just drop the .net .dll in the same location as the Access front end. Use my provided .net class “side loader” in the above example. This will allow you with ZERO installing, and ZERO registering of .net .dll's to be called from and used from Access.

    So, don’t try to consume or edit VBA code in Visual Studio (VS), but go the other direction.  (consume  + use .net code in VBA).

    Thus, you gain near unlimited abilities in your Access applications.  

    0 comments