(Solved) – Use ADODB.Parameter to get values between two dates

(solved)-–-use-adodb.parameter-to-get-values-between-two-dates

(1) Table DB-Fiddle:

CREATE TABLE logistics (
    id int primary key,
    Product VARCHAR(255),
    insert_timestamp Date
);

INSERT INTO logistics
(id, product, insert_timestamp
)
VALUES 
("1", "Product_A", "2020-02-24 18:15:48"),
("2", "Product_B", "2020-02-24 20:30:17"),
("3", "Product_C", "2020-02-24 23:54:58"),
("4", "Product_D", "2020-02-25 08:09:30"),
("5", "Product_E", "2020-02-25 10:17:15");

(2) VBA

Sub Get_Data()

Dim conn As ADODB.Connection, _
cmd As New ADODB.Command, _
param As ADODB.Parameter, _
rs As New ADODB.Recordset

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
    conn.Open

    Set cmd.ActiveConnection = conn

            cmd.CommandText = " SELECT Product " & _
                            " FROM logistics " & _
                            " WHERE DATE(insert_timestamp) BETWEEN ? AND ? GROUP BY 1"

    Set param = cmd.CreateParameter(, adDate, adParamInput, , "2020-02-24 00:00:00")
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter(, adDate, adParamInput, , "2020-02-24 23:59:59")
    cmd.Parameters.Append param

    Set rs = cmd.Execute

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    heet1.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close

End Sub

With the above script I am trying to get data from the table logistics.
I want to use ADODB.Parameter as suggested in one of the answers here.
However, with the above code I get runtime error '-2147217900 (80040e14)' on Set rs = cmd.Execute.

What do I need to change in my VBA to make it work?

Leave a Reply

Your email address will not be published. Required fields are marked *