Module Function from Access SQL Query

[cj92713696]

I created a function in a module and subsequently use the function in SQL Queries I have built in Access. The SQL query works fine inside of Access however when I try to run the query via a front end application I've written in Visual Basic, I get an error message from Access that says the following

"Run-time error '-2147217900 (80040e14)':

Undefined function 'getQuantity' in expression."

Any help is greatly appreciated.

Thanks,
CJ

 

[DonQuichote]

Seems logical, doesn't it? The query uses a function that was written in MS-Access and MS-Access is not running when you use the front end. Only the database engine (DAO, ADO or whatever) is running. So there are two solutions:

1. do not use the getQuantity or any other VBA functions in your queries. Instead, use the front end to do calculations on the data.

2. If that is not an option somehow, run MS-Access. In VB, you can make a reference to MS-Access and create a session by assigning a

new Access.application

to an object variable. Think twice before trying this option.

Best regards

 

[cj92713696]

It does seem logical, and quite frustrating. Why would I be able to call and use built-in Access functions to manipulate the data from Visual Basic? I can use Val or Format functions w/o problems. It just seems a bit strange I can't create my own function to have the same effect. Is there any other workaround?

Thanks,
CJ

 

[jbelina]

With Access 97 I was able to create a public function to do just what you described with an Access table referenced in a query or a linked table.

I would recommend avoiding using a custom function in Access with a linked table if you are able to do so. There is extra overhead involved that could be put directly into your query.

Here is the sample function I used:

Public Function TestAdd1(Optional iInputValue As Integer = 0) As Integer
TestAdd1 = iInputValue + 1
End Function

Is it possible that your function is not listed as a public function in your module?

Here is the function call in the query:

SELECT TEST, testAdd1([TEST]) AS TEST2
FROM qryGetTables;

TEST was the "original" column I added to my table, and TEST2 was the same column with 1 added to it.

I hope this helps!
Jeff M. Belina

 

[cj92713696]

Hmmm... I just want to make sure I havn't confused everyone.

I'm calling the Access query from a Visual Basic application. If I run the query w/my custom function inside of Access it works great. If I run my query from Visual Basic it returns the above error.

I'm going to try your example w/a simple custom function to see what happens.

Thanks,
CJ

 

[cj92713696]

Yes, I just added a public function "getStr()" to my Visual Basic form w/o any luck.

I also created a "getStr()" public function in my Access module w/o any luck.

My function:

public function getStr() as string
getStr = "efg"
end function

Every possible scenario Access would report the above error unless I ran the query local to Access.

CJ