Using Access functions

[Susan]

I am accessing data in a MSAccess database through the
following code:

Dim DB As DAO.Database
Dim DBRecordSet As DAO.Recordset
Set DB = OpenDatabase(GlobalValues.DatabaseFilename)
Set DBRecordSet = _
DB.OpenRecordset _
("SELECT * FROM Document WHERE DocId='" & _
Product & _
"' ORDER BY SortParagraph(Paragraph) + ' ' _
SortParagraph(Sentence)")

where SortParagraph is a function defined in MSAccess.
When I try to run the code, I get the run-time error
3085 "Undefined function 'SortParagraph' in expression."
I have tried several things including moving the function
into a module that is visible to my VB code.

The only way that I've been able to get the function to
be visible is to open the Access application. However,
this gives undesirable results ... for instance, the
MSAccess window is briefly visible (i.e. flashes).

Any clues as to how I can get the semantics that I'm
looking for without opening the MSAccess application?

Thanks!
Susan
(e-mail address removed)

 

[Douglas J. Steele]

Sorry, but user-defined functions in Access are not available outside of
Access. When you try to get at the data from outside of Access, your only
connection is through the Jet engine, which doesn't know anything about
user-defined functions. (In fact, it doesn't even know about many of the VBA
functions)

 

[Susan]

Drats! Well, thanks for answering my question.

Susan

 

[Dirk Goldgar]

I am accessing data in a MSAccess database through the
following code:

Dim DB As DAO.Database
Dim DBRecordSet As DAO.Recordset
Set DB = OpenDatabase(GlobalValues.DatabaseFilename)
Set DBRecordSet = _
DB.OpenRecordset _
("SELECT * FROM Document WHERE DocId='" & _
Product & _
"' ORDER BY SortParagraph(Paragraph) + ' ' _
SortParagraph(Sentence)")

where SortParagraph is a function defined in MSAccess.
When I try to run the code, I get the run-time error
3085 "Undefined function 'SortParagraph' in expression."
I have tried several things including moving the function
into a module that is visible to my VB code.

The only way that I've been able to get the function to
be visible is to open the Access application. However,
this gives undesirable results ... for instance, the
MSAccess window is briefly visible (i.e. flashes).

Any clues as to how I can get the semantics that I'm
looking for without opening the MSAccess application?

Thanks!
Susan

Uh oh, Susan, it looks like you danced the happy dance too soon. In
order to reference a function defined in the Access database in a query
you *will* have to open an instance of Access and run the query from
that instance. That's because only Access can provide the service to
Jet that will allow the function to be evaluated. I'm still thinking
about how to work through automation without "popping" the screen.

BTW, Susan -- I strongly advise against posting your real e-mail address
in the newsgroups. You will receive a boatload of spam and viruses if
you do.

 

[Dirk Goldgar]

The only way that I've been able to get the function to
be visible is to open the Access application. However,
this gives undesirable results ... for instance, the
MSAccess window is briefly visible (i.e. flashes).

Susan -

Although you may have to resort to opening the Access application, I've
found that you don't get the screen flash if you remember to set your
Recordset object to Nothing before quitting the Access application.
This test code worked fine for me, with no screen flash:

'----- start of test code run from VB -----
Private Sub Command1_Click()

Dim appAccess As Object
Dim rs As DAO.Recordset

Set appAccess = CreateObject("Access.Application.10")
With appAccess
.OpenCurrentDatabase ("C:\Documents and Settings\Dirk\My
Documents\test.mdb")

Set rs = .CurrentDb.OpenRecordset( _
"SELECT fncQuoted(Table1.Desc) As F1 FROM Table1")

Debug.Print rs(0)

rs.Close
Set rs = Nothing
.Quit
End With

Set appAccess = Nothing

End Sub
'----- end of test code run from VB -----