Calling VBA functions via ODBC 2

[mersa]

I have a Public VBA function called GetAge which is used within a query within Access 2000.

This works fine when the query is run from within Access but fails with the following error message when the query is called via ODBC using ADO 2.5 from VB6:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GetAge' in expression.

Any thoughts would be greatly appreciated.

 

[vbajock]

Are you using a back-end database like SQL server?

 

[mersa]

no in this case Access 2000 is being used as the back-end database.

 

[vbajock]

I'll have to see the code on how your executing the query.

 

[MichaelRed]

Where does the FUNCTION reside? AFAIK, it MUST be available to the "FE". this is NOT accomplished via ADO, which is strictly "DB" access. If the function is in the "BE" this is 'impropper' app design, as NOTHING but the actual data / tables should be there for Ms. A. dbs. If this is your issue, you need to re-think / re-configure the overall app to palce the pieces and parts in the 'propper' locations.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mersa]

Function "GetAge" resides in VBA module. Query "qryChildren" is defined as a normal query object as follows:

SELECT h.Relationship, h.Gender, h.Carer_ID, GetAge([Date_of_Birth]) AS Age, c.Contact_ID, c.Forenames
FROM tblCarerHousehold AS h, tblContacts AS c
WHERE (((h.Relationship)="Child" Or (h.Relationship)="Foster Child&quot AND ((c.Contact_ID)=[h].[contact_id]));

This is called from a VB app using the SQL:
Select * from qryChildren where Carer_ID = 'abc_123'

I assume you are referring to FE as front-end. You will have to excuse my ignorance in Access as I am more use to the functionality which is available in SQL Server / Oracle where I would tend to locate stored procedures / functions.
In summary then it does not look like Access will allow this.

If there is nothing further to add then thank you for your time and guidance.

 

[vbajock]

Are you creating a record set from the 2nd sql? I don't know if that will work. For what you are trying to do, it looks like the best approach would be to create an ADO recordset from your 1st SQL and then loop through the records, calling your GetAge function as you move through the records.

 

[mersa]

You are quite right that is a solution but it can be quite clunky if there is a large recordset to parse. I think I accept that I am dreaming a dream too far for dear old Access!

Thanks for your help.

Karl

 

[vbajock]

NOT using a recordset is clunky. You just have to know how to manipulate the sql string that creates the recordset.

 

[MichaelRed]

When asking the 'where' of hte procedure, I am refering to the FE / BE. ALL procedures in MS. A. are 'in VBA'. Modules exist in an "application" or -more concicely- in a specific ",MDB" and are available ONLY to that "application" or one which includes it as a reference. The generic ADO functionallity DOES NOT provide this, and Ms. A. acts more like a file server than the 'industrial strength' database server. It only / always returns the entire 'table' to the calling functions, not the subset of qualified records. ALL recordset processing is done by the caling entity. AFAIK, the technique should work as originally implemented IF the procedure is 'available' to the FE.

Hence the question - WHERE is it located?





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mersa]

Hi, the function is in the back end. I have now moved it out to the VB.exe and is used to modify returned data prior to display in the Front-end app.

 

[MichaelRed]

So, then, I presume that moving the procedure resolved the issue for you?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mersa]

Not so much solved, as I could easily have put it there initially, but it has helped me to understand that putting it in the back-end in Access, whilst potentially more elegant, was not a goer.

Thanks again for your help.