How can I get a mysql stored function result in VBA code

Question

I have a function in my mysql database. I am trying to call the mysql function in my VBA inside a VBA funtion to get the result by passing parameter from the form. I cannot quite figure out how to do it. This is what I have so far

function getval(Id As Long) As String
Dim strSQL As String
Dim rsTch As DAO.Recordset

strSQL = "SELECT getname(" & Id & ")"
Set rsTch = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

End Function

But I am getting this error

Undefined function getname()

getname() is my mysql function. I am getting result when i run the query in mysql workbench though.

getval() is my VBA function from which I execute the query id is the parameter for the mysql function please help me out

https://stackoverflow.com/questions/13558921/how-can-vba-connect-to-mysql-database-in-excel — Tim Williams, Mar 02 '18 at 18:06
@TimWilliams Its not about connecting to mysql. I can execute my sql queries now except for functions — Anandhu Nadesh, Mar 02 '18 at 18:08

ComputerVersteher · Answer 1 · 2018-03-05T05:10:57.590

It's the MDAC-Engine(Jet) which parses the SQL before it is send to MySQL. The engine see's the function getname() and looks for it in vba-code, but it is not there, so error Undefined function getname() is raised.

To prevent this, you have to bypass the MDAC parsing by using a passthrough-query where sql-code is send to MySQL as it is.

There are two options.

Use ADO instead of DAO as Tim suggested. This is the recommended option, used by the Pros (I am still mostly on DAO, but ADO is on top of my list of skills-next-to-gain).
or stay on DAO and create a Query-Def as described in sql-server-passthrough-query-as-basis-for-a-dao-recordset-in-access from Gord Thompson.

Function getval(Id As Long) As String

Dim strSQL As String
Dim qdf As DAO.QueryDef, rst As DAO.Recordset

strSQL = "SELECT getname(" & Id & ")"


Set qdf = CurrentDb.CreateQueryDef("")
With qdf
    .Connect = "ODBC;Driver={MySQL ODBC 5.3 ANSI Driver};" _ 
                        & "SERVER=Your Server;PORT=3306;DATABASE=Your Database;" _ 
                        & "USER=Your User;PASSWORD=1234;OPTION=3"
    .SQL = strSQL
    .ReturnsRecords = True
    Set rst = .OpenRecordset
End With
Debug.Print rst(0)
rst.Close
Set rst = Nothing
Set qdf = Nothing
End Function

How can I get a mysql stored function result in VBA code

1 Answers1