Why are custom functions inaccessible outside of Access?

[Shizok]

I have defined several functions using VBA to help limit duplication of code
throughout my queries. Specifically, I am referencing DateSerial in order to
calculate first and last days of previous months. However, I cannot mail
merge or integrate into my accounting system any queries that call my custom
functions. Why not? How can I make these functions public (the 'public'
keyword doesn't help)? Any ideas will be sincerely appreciated.

 

[Douglas J. Steele]

I'm assuming you're trying to use these functions from outside of Access
(from within Word, for instance). When you're running queries from outside
of Access, your only connection to the database is through the Jet Engine,
which doesn't know anything about user-defined functions. There's no way
around this limitation.

 

[Etienne]

I was afraid you would say that. Do you know if Microsoft has plans to add
the ability to call user-defined functions from other Office apps any time
soon?

 

[Douglas J. Steele]

I would be very surprised. It would require a major change to the Jet
Engine, and I don't believe that's in the cards.

 

[david epsom dot com dot au]

the ability to call user-defined functions from other Office apps

any time soon?

SQL Server 2003 Express Edition supports something like this.
You will need to write your UDF in VB.Net, but that is better
than the previous situation, where SS UDF's need to be written
in T-SQL or in Win DLL's.

Unfortunately, it does not come pre-installed with windows (as does
most of the Jet engine, which requires installation of only a few
extra library files), and is not as well integrated with Windows
Explorer (although the integration is noticeably better on WinXP).

In fact, what with re-writing, downloading, installing, permissioning
etc, it's not a very attractive proposition at all, but it's
here, and it's probably what you will be using in a couple of
years from now.

(david)

 

[Albert D. Kallal]

You can still integrate this data with word.

I have a word merge example that allows you to use queries, and functions in
the sql..and it still merges with word.

You can give the sample a try here:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html