Hi guys,
I am totally lost, on getting around with parameterized stored procedures on a MySQL Server and binding the result of them to a Form recordset.
On the net, I found dozens of "approaches" but they did not yield an working solution.
So, here is my setup:
I am using a MySQL Server which is connected via the MySQL ODBC Driver (Version 8). This server hosts a schema which consists of the following stored procedure:
PROCEDURE `mapCompetenceToPerson`(IN pID Integer)
BEGIN
select * from `ma-profile`.skill as s left join (select PersonID, SkillID from `ma-profile`.person_skills where PersonId = pID) as ps
on s.ID = ps.SKillID;
END
On the Access Site I have build up some routines like
Public Function RDAOGet(ByVal procCall As String) As ADODB.Recordset
Dim rs As New ADODB.Recordset
openConn ' prepares the global ADODB.Connection 'conn'
Set rs = conn.Execute("Call " & procCall & ";") ' not a one liner for debug purposes
Set RDAOGet = rs
closeConn
End Function
Yepp, I am currently using ADO to retrieve the data. I played around with DAOs, but I could not bring parameters to work.
The calling routine looks as follows
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = RDAOGet("mapCompetenceToPerson(1)") ' not a one liner for debug purposes
Set Me.Recordset = rs
End Sub
While this actually results in a recordset holding data from the SP Execution. The 'rs' could not be assigned to the form's recordset, since its not a "valid" recordset.
InvalidRecordset.jpg
Now, the big question is: Am I on the right track, or did I made some wrong turns to get here? Because right now, I have no clue what the correct way may look like.