Add, Edit, or Delete a Function() using VBA

[Cotswold]

Is there a way of adding, editing, or deleting one or more Functions to a Form, or a Module in an Access FE using VBA?
The deleting a selected Function would be by name using VBA from a Form or Module.
Preferably using an external program, or another Access database. If not, then making those changes with VBA
from the same Access FE.

Maybe there is an example out there?

 

[Pat Hartman]

This isn't something a normal application would do. Are you trying to make some utility? Take a look at MZ tools and see if it has what you need.

 

[ebs17]

Normally the frontend is simply replaced. The developer makes the necessary changes and provides the user with the new version. If the frontend is compiled (delivered as ACCDE), there is no alternative.

Otherwise:

1) Reference to Microsoft Visual Basic for Applications Extensibility 5.3. This can be programmed in the VBE.

Willkommen bei Team-Moeller.de - Rund um die VB-IDE

Homepage von Team-Moeller.de Access

team-moeller.de

2) The developer creates text files with the changed object definitions using SaveAsText and loads them into the user's frontend using LoadFromText.

But as I said: swapping is the easiest and best, because development also includes testing, and that always works best on site.

 

[theDBguy]

Permanently updating the custom function is probably not possible when using an ACCDE file. However, I imagine it might be possible to override it at runtime though. Just a thought...

 

[Pat Hartman]

@Cotswold You haven't replied yet. If you are looking at this as a way to update production FE's - forget it. That is a really bad way to update a FE. Use either one of the distribution databases you can find here or use one of the batch files or VB scripts. Replace the entire FE.

 

[Cotswold]

@Pat
I don't see any of this on my phone, only on the PC. Plus instant response isn't usually something I usually do after 5pm, particularly on a Sunday.
My mobile phone is still a 2G model, as I'm a bit of a luddite on the phone front. People can call and text me and I think that is enough.
I've only just switched the PC on to check if something worked, otherwise I wouldn't have seen the replies until early morning.

I recall seeing a program that checked and fixed the 32bit to 64bit update for Access on AEUG. It clearly scanned the database objects and then changed the code for the application to work in 64bit. It was an automatic operation, so it must have gone in and edited existing settings/objects.

There was something I have been looking at for a few days and it occurred that it would be handy (and interesting) to do be able to that.

On MZ Tools. I'd actually seen a demo on an AEUG video recently. Although back then it didn't strike me to be of interest. But it was more about "things it can do that you may not know it can do" type of message presentation. As I didn't know much about MZ, then it presumably went in one and out the other. I've now taken a look at the MZ site and will give it some time. I will need to go through their documentation and then maybe download the demo to see exactly what it will do that could be helpful.

Just like to say, many thanks to you all for your suggestions.
(p.s. switching off again 'till Mon am!)

 

[Pat Hartman]

I recall seeing a program that checked and fixed the 32bit to 64bit update for Access on AEUG

The only conversion from 32-bit to 64-bit would be fixing any API calls. And then removing any ActiveX controls that had no 64-bit version. I would be very surprised if this code existed.

If you want to convert to 64-bit, you can go cold turkey. You can build a database that reads .accdb's and compiles them. If there is a compile error, you will need to manually fix the problem. But you should have some idea of whether you have used API's or ActiveX controls because those are the only databases that would ever have a problem with 64-bit.

 

[isladogs]

Hi @Cotswold
Whatever your reasons for wanting to do this, it is certainly possible. I have several apps that add module code using VBA. For example:

• My JSON Analyzer tool creates transform functions used to populate tables from JSON data: https://www.isladogs.co.uk/json-analyse-transform
• My SQL to/from VBA converter add-in creates example code from converted VBA strings in the host application: https://www.isladogs.co.uk/sql-to-vba-back-again

As for converting 32-bit to 64-bit code, I believe you are referring to tools available from Peter Cole which were covered in the Aug 2022 session of AEU: https://www.isladogs.co.uk/aeu-6
These can be downloaded from Peter's website: https://www.thememydatabase.co.uk/access32to64.html but the free version of his tools do not write the modified code for you