Pass-Through ODBC query *to* (not from) MS Access

[Wabiloo]

I need to connect (via ODBC) to a MS Access DB (.mdb file) and run a
query that makes use of stored queries and functions in an embedded
VBA module.
Whenever I try that, I get an error message saying "undefine function
xxx in expression", where xxx is one of the functions I wrote in that
module, and is either directly in the SQL query I send, or used in the
query stored in the DB.

ODBC is supposed to allow for pass-through queries, and this is widely
used when connecting FROM access TO a different DB. However, no
matter where I looked (properties of the DSN, ODBC driver options,
etc.) I cannot find any way to specify that I want to send a pass-
through query TO access FROM another application.

Am I missing something obvious, or is that some sort of limitation of
the Access ODBC driver?
If so, is there another driver I could use that would offer me that
facility?

Many thanks

 

[strive4peace]

that depends on how you are using the query ...

if you open the database and run the query in that object, it should work

'~~~~~~~~~~~~~~~~~~~~~~

Function OpenOtherDatabase(pFilename as string) as boolean

'NEEDS REFERENCE
'Microsoft DAO Object Library

OpenAnotherDatabase = false

On Error GoTo Proc_Err

Dim db As dao.Database
Set db = OpenDatabase(pFilename)

'now do something in the database
'
' more stataments
'

OpenAnotherDatabase = true

Proc_Exit:
On Error Resume Next
db.close
Set db = Nothing

Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " OpenOtherDatabase"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function
'~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Brendan Reynolds]

I need to connect (via ODBC) to a MS Access DB (.mdb file) and run a
query that makes use of stored queries and functions in an embedded
VBA module.
Whenever I try that, I get an error message saying "undefine function
xxx in expression", where xxx is one of the functions I wrote in that
module, and is either directly in the SQL query I send, or used in the
query stored in the DB.

ODBC is supposed to allow for pass-through queries, and this is widely
used when connecting FROM access TO a different DB. However, no
matter where I looked (properties of the DSN, ODBC driver options,
etc.) I cannot find any way to specify that I want to send a pass-
through query TO access FROM another application.

Am I missing something obvious, or is that some sort of limitation of
the Access ODBC driver?
If so, is there another driver I could use that would offer me that
facility?

Many thanks

As I understand it, it is Access, not the JET database engine, that
evaluates custom VBA functions in a query. For this reason, they can only be
used when executing the query in an Access application. When you try to use
those queries from your non-Access application, they are executed by the JET
database engine, not by Access, and the custom VBA functions can not be
evaluated. I'm afraid using another driver won't help. The problem is that
evaluation of custom VBA functions in a query is a feature of Access, not of
the JET database engine, and therefore the feature is available only within
an Access application.

 

[strive4peace]

hi Brendan! Nice to see you

Warm Regards,
Crystal


*
have an awesome day
*

 

[Brendan Reynolds]

hi Brendan! Nice to see you

Thanks Crystal, nice to see you too!