Stored Procedure with Prepared Statements to build SQL
This is a stored Procedure workaround to the limitation that an ADODB recordset cannot be assigned as an ACCDB Access report recordsource.
Private Sub Report_Open(Cancel As Integer)
Dim cmdStr As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn, strServer As String
Set cmd = New ADODB.Command
strServer = TempVars![MySQLServer]
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PASSWORD=x; SERVER=" & strServer & ";FILEDSN=C:\AccessHdb\HDB\HDB.dsn;"
With cmd
.ActiveConnection = strConn
.CommandText = "CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', '%');"
.CommandType = adCmdText
End With
Set rs = cmd.Execute
' the following shows the recordset has data
Do While Not rs.EOF
Debug.Print rs!COMPANY
rs.MoveNext
Loop
' the following line will not work
Me.Recordset = rs
Set cmd = Nothing
Set rs = Nothing
End Sub
-----------------------------------------------------------------------------------
Work Around:
The following stored procedure is used to fill a MySQL table with records
for the Access report to use as a recordsource.
DELIMITER $$
CREATE PROCEDURE `sp_tempviewinvoicesummary`(invdatestart TEXT, invdateend TEXT, sproj TEXT)
BEGIN
DECLARE spj TEXT;
-- Note: '\'' below is 'single quote'
-- if sproj = '%', want to include records with sproj = null
IF sproj = '%' then
SET spj = CONCAT(' AND (v.sproj LIKE ', '\'', sproj, '\'', ' OR v.sproj IS NULL) ');
ELSEIF INSTR(UPPER(sproj), 'NOT NULL') > 0 then
SET spj = ' AND v.sproj IS NOT NULL';
ELSEIF INSTR(UPPER(sproj), 'NULL') > 0 then
SET spj = ' AND v.sproj IS NULL';
ELSE
SET spj = CONCAT(' AND v.sproj LIKE ', '\'', sproj, '\'');
end if;
SET @str = CONCAT('CREATE TABLE tempviewinvoicesummary
SELECT * FROM viewinvoicesummary AS v
WHERE v.invdate >= \'',invdatestart, '\'',
' AND v.invdate <= \'', invdateend, '\'', spj,
' ORDER BY v.invdate DESC, v.invoiceno DESC;');
-- SELECT @str; used while testing, to check the sql string that was built
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;
DROP TABLE tempviewinvoicesummary;
END;
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Call sp_tempviewinvoicesummary from Access:
Private Sub Report_Open(Cancel As Integer)
Dim cmdStr As String
Dim cmd As ADODB.Command
Dim strConn, strServer As String
Set cmd = New ADODB.Command
strServer = TempVars![MySQLServer]
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PASSWORD=x; SERVER=" & strServer & ";FILEDSN=C:\AccessHdb\HDB\HDB.dsn;"
'cmdStr is built in a criteria form [actual report has many criteria fields] see samples below
cmdStr = TempVars![rptCriteriaCommand]
With cmd
.ActiveConnection = strConn
.CommandText = cmdStr
.CommandType = adCmdText
.Execute
End With
Set cmd = Nothing
End Sub
Four example calls, sproj is a LONG but can be null:
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', '%'); show sproj non-null and null records
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', '3112162'); show sproj matching records
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', 'NULL'); show records with sproj IS NULL
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', 'NOT NULL'); show records with sproj IS NOT NULL
The table needs to be dropped when the report is closed. Only 1 person can run this report at a time
unless the stored procedure created table name is made unique for each Call.
It's easier and cleaner to just use a pass through query, but I'm trying to build functionality into MySQL
so that it can be used in another front end.
I entertain any comments about improvements.