Calling a standard "utility".

[sts023]

Hi guys....

When I used to be a proper programmer - i.e. on a real Computer (a Mainframe (is anyone else old enough to remember them???)), I used to build up a variety of general purpose subroutines which could be called from other programs.

Is there an equivalent methodology in VBA?

For example, I have (inherited) a set of Tables which hold a person's full name in various different formats, e.g. "Fred Flintstone", "Flintstone, Fred", "Mr. Fred Flintsone" "Sir Fred Flintstone", "Flintstone, Mr. Fred".

I'm writing code to reformat the names into a standard format, and instead of including this code in each Access Project, it would be nice if there was a way of calling a utility routine (perhaps living in a Project called Utilities?) which could be invoked by something like

Call Utilities.ReformatName(CurrentNameString, BetterNameString)

where ReformatName is the code, and the "Utilities." bit is some way of pointing Access to the address of the Project containing the code.

I know in Excel you can bung standard code in "Personal.xls" which I have used in a similar way, but does anyone know if this can be readily achieved in Access?

Thanks in advance....

 

[DCrake]

Yes you can do that in Access, You would create a module and inside the module you would place the Function that reformats the string, however you would have to write the function yourself.

If it was me I would use the Split() function to cast the individual words into an array and work with the words to rearange them.

 

[sts023]

Thanks David,
I've already got the code (and yes I do use the Split function!), but my question is how to "Call" it from an unrelated Project?

 

[DCrake]

What do you mean by unrelated project?

If the tables are in Access you can create a new database and link the tables

 

[sts023]

In simple terms I'm writing code in "MyCode.mdb", and want to execute a Utility where the utility code lives in "Utility.mdb".

Sort of like -
Call "C:|Access|Utility.mdb(SomeUtility(param1, param2))"

The more I look at it, the more unlikely it seems, unless I can sort of compile the utility and then invoke it, rather like a DLL - or perhaps with a form of DoCmd???

 

[DCrake]

Why do you need to have it in a seperate mdb?

You can reference other functions in external mdb's Have done it myself in the past but can't remember how I did it though.

Which mdb contains the tables MyCode.mdb or Utility.mdb?

 

[vbaInet]

You can reference other functions in external mdb's Have done it myself in the past but can't remember how I did it though.

Application.Run perhaps.

 

[sts023]

Q - Why separate?
A - I would like such utilities to be in their own mdb ('cos I'm perverse, and HATE duplicated code).

Q - Which has the Tables etc.
A - In the earlier example, MyCode.mdb

The only "data" available to the Utilities would be that which is passed to it, either directly via a parameter, or indirectly where the passed parameter indicates a location on disk where that data is stored.
Similary, the output from the Utility would be returned either via a passed parameter area, or an indication of where the "result" data exists.
For example -

Dim RawDataHere As String
Dim PutResultHere As String
'
RawDatHere = "Smith, John"
PutResultHere = ""
Call Utility(RawDataHere, PutResultHere)
Debug.Print PutResultHere '===> "John Smith"
' or
RawDataHere = "C:\Access\UtilityInput.txt"
PutResultHere = "C:\Access\UtilityOutput.txt"
Call Utility(RawDataHere, PutResultHere)

This is probably well beyond my ability level, but one can always dream....

 

[DCrake]

What you are asking for is very memory intensive. By having all your code in one mdb and referencing it from another causes Access to retain in memory all code found in each module once it is opened. Lets say you had one module with 50 seperate functions in it but in your seperate mdb you only reference one of them. Access when opeing a module reads all 50 functions for errors but won' report on them unless you use the offending function. Now these 50 functions remain in memory along with anything else it needs.

This is true for all modules, this is why you should only have modules that contain related functions within them.

Then you also have the issue of multiuser access and file corruption to consider. If you have several front ends all of which point to the utility mdb you are in effect temping collisions between users. Thus defeating the reason to have split databases.

Also what happens if the utility mdb corrupts or gets deleted you will not be able to run any application that has references to it.

Food for thought

 

[Rabbie]

What I do is to have a standard module with the code in it and import that into any new DB I create where it is relevant.

 

[sts023]

Thanks to all contributors!

It would seem that code duplication is the optimum way (I can't believe I said that!).

However, I've already written some code (translation - found someone else's code and butchered it) to export a Module to another mdb, so perhaps the best way is to have my Utilities mdb as a sort of "standard code" mdb, and export the individual utilities to other mdbs as and when they are required.

And about the "what happens when Utilities.mdb gets corrupted?" - well, of course, I simply restore it from the last backup (which I probably didn't bother taking!).

 

[DCrake]

Buts it's Monday morning and you are on holiday and cannot be reached, so how are you going to do a restore from the latest backup. Let alone how is anyone using the system going to know what the root of the problem is when it falls over.

 

[gemma-the-husky]

you can have an external database of utilities

you just need to then set a reference to it, within your code. open a module, and then use tools/references, and add a link to your standard utilities.

what gets a bit tricky is maintaining the link to that library, if you relocate the main code database, and installing and updating the utilities database, if you need to do this on remote installations. there are also issues if the utilities database/main database
need to make use of tables, which may be in one or other of those databases, and of potentially resolving name conflicts if you use similar function names in your library and code databases.

therefore practically, it may be easier to copy the utilities into every database.


if you have the knowhow, of course you can compile your utilities into a dll, rather that a library databases - and achieve the same thing. access/vba really is a grown-up product.

try it and see.

 

[sts023]

OK David, I give in!
When I was a REAL Programmer (see previous posts) we backed up nightly, and had an Operations Support Group to restore after crashes.
(Trouble is, OSG took regular backups, but rarely tested restores, "with hilarious consequences").

and Dave/Gemma
If I knew enough to be able to compile code to generate DLLs, do you really think I'd be asking such "novice" type questions ???

(Aspirations outstripping Abilities!, Again!)

 

[vbaInet]

As already mentioned, Application.Run() is what you're after. Yes, I believe running code from the utilities db will be slower than having it in the db if you have to open the utilities db before you run the function. Unless you keep an open database object that references the utilities db that might just help to improve performance.

Here's the Application.Run method:

    Dim accApp As Object, myResult As String

    Set accApp = CreateObject("Access.Application")
    
    accApp.OpenCurrentDatabase "C:\Users\Administrator\Desktop\DatabaseName.accdb"

    myResult = accApp.Run("DatabaseName.FunctionName", "Arg1", "Arg2"... etc)

    Set accApp = Nothing

In any case, I will stick to copying a utilities module from the utilities db and paste it into the db.

Edit: Forgot to mention that the myResult line assumes that the function returns the value.

 

[MarkK]

What I've done is created a library database that I reference from my application just like I might reference ADO. All code I write that doesn't belong specifically to the application goes in the library, so generic utilities and code tools, but also simplified methodologies for creating Listviews and Treeviews, progress bars, a generic search form, Shell functions, API calls, Sound Players, CDO EMail tools, and so on and so on.
I don't assert copyright on application code or processes, but I do assert copyright on the Library.
Here's a Object Browser view of some of the classes exposed ...

 

[sts023]

Thanks for your support and advice to all those helpful people who have contributed to this thread.

I now have lots of entries in my CodeSamples database (both actual code and textual notes).

If I can't progress after all your posts, I need shooting!