Many Frontends/Users with same set of functions

[OPMCoordinator]

Hello!

Is it possible to point an access frontend to a remote set of custom functions?

I have many (18 frontends with about 50 users and counting) frontends built in access that all hook into the same SQL Server backend. They are mostly still under development and all are constantly changing due to requests from users and as I learn better ways of doing things. Thefore my set of custom functions is increasing and improving all the time. I wish these functions to be available to all front ends as well.

What I dont want to have to do is copy an ammended set of functions into each of the frontend originals and then redeploy them accross the plant.

Do you have any suggestions?

Eventually the functions will be converted to SQL stored procedures and functions on the server as these are obviously better, but I am not ready to do that yet.

Besides, I want to know how to do this. I could probably figure out how to automate a module import on a frontend startup event, but this would still require users to jump in and out of the frontends when something changes. I was hoping for a more elegant solution than this.

Cheers

Mike

 

[Travis]

I'm not aware of a way to do this with just MS Access (without the copy and paste of modules)

1. You could create an Update process using Cloning on the Front End
2. You could create a DLL (C++, VB or .NET)
3. DCOM or COM, COM+ Application (C++, VB or .NET)
4. You could create a Web Service


The DLL approach would allow you to create a Business Rule Layer giving you greater control and lower ongoing maintance.

 

[MarkK]

You can set a reference to an .mdb file, so you might consider putting your functions in the back end and,
1) link to it, as you must currently do, and
2) set a reference to it.
Then you can call functions in modules in the back end.

 

[MarkK]

Use caution when debugging too, since if you break into the debugger in your referenced database, and you edit that file, those changes don't stick. It seems to behave like a non-default instance of a form.

 

[OPMCoordinator]

Thank you all for responding!

Now the tricky questions...

I understand that user defined functions in access can slow down data crunching from SQL server because of the need to bring accross the whole dataset. Will linking to an .mda slow it even further?

When the frontend then calls a function (pretending that this particular task does not visit the server) from the .mda, would that be slower than if the function was in a module of its own?

Id really like to hear that the functions are loaded into memory or something on starting up the front end!

A plan is starting to form in my mind...

Cheers

Mike

 

[MarkK]

I don't know, but you can time different strategies on test data using "Timer"