Excel VBA - Calling a sql function.

[gregmonay]

Hi all,

I want to call a sql function to return data to my recordset but it is not working in Excel.
I tried calling the sql function in Visual Foxpro query and it is returning data.

Here is my excel code:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "Select ibhand,lastrcvqty(250,3780683) as lastqty from mm4r6lib.invbal where inumbr = 3780683  and istore = 250 ", cn_SystemA, adOpenForwardOnly, adLockReadOnly 

My query is working if I remove the function in the sql statement so I am sure it is the problem. Any thoughts why it is not working? Do you have some working excel snippets calling sql function?

Thanks in advance,
Greg

[Mercury]

Re: Excel VBA - Calling a sql function.

I think that the function should be stored in the same library as the table.

[MichaelCatalani]

Re: Excel VBA - Calling a sql function.

I'm having a hard time seeing the code within the php box. What sql function are you referring to?

[Mercury]

Re: Excel VBA - Calling a sql function.

"lastrcvqty(250,3780683)" in the select clause is obviously the function the OP is referring to.

[gregmonay]

Re: Excel VBA - Calling a sql function.

Yes, the function is the lastrcvqty which is a user defined function I created in the QGPL library. The function is working when I use it in STRSQL query and outside iseries using VFP ODBC.

Anyway, I already resolved my issue. I have figured out that it is not working with the DSN-less connection (IBMDA400). I resorted to using the ODBC (DRIVER=Client Access ODBC Driver (32-bit)) specifying my DSN in the connection string and it is working now. I dont know why it is not working with OleDB DSN-less connection.

Thanks,
Greg