ADO Command object in msaccess

Question

I don't understand why the following code is failing. Unfortunately to be able to reproduce the problem you must create a valid mysql connection object, or at least another ODBC compatible database.

The following code basically calls a stored procedure like for example CALL my_procedure(12345);

The code:

' Create ADODB Connection object
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
Const MYSQL_DRIVER = "{MySQL ODBC 5.1 Driver}"
Const MYSQL_SERVER = "10.32.27.6"
Const MYSQL_DATABASE = "sales"
Const MYSQL_USER = "root"
Const MYSQL_PASSWORD = "xxxxxx"
oConn.ConnectionString = "DRIVER=" & MYSQL_DRIVER & ";Server=" & MYSQL_SERVER & ";Database=" & MYSQL_DATABASE & ";Uid=" & MYSQL_USER & ";Pwd=" & MYSQL_PASSWORD & ";"
oConn.Open


' parameter value
Dim lngAssemblyId As Long
lngAssemblyId = 12345


' Create ADODB.Command
Const adCmdText = 1
Const adParamInput = 1
Const adInteger = 3

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")

With cmd
    Set .ActiveConnection = oConn
    .CommandType = adCmdText
    .CommandText = "CALL my_procedure(?)"
    .Prepared = True
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, , lngAssemblyId)

End With


' execute command
Call cmd.Execute
Set cmd = Nothing

The code above will generate the error "Parameter Type is not supported." at the line Call cmd.Execute

I have found a couple of fixes but I don't understand why they work!

Fix 1
a) Add a reference to "Microsoft ActiveX Data Objects 2.8 Library"
b) Then Change this part of the code:

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")

with this code:

Dim cmd as New ADODB.Command

Fix 2
Change the value of adInteger from 3 to 20. The value of 20 represents adBigInteger from the ADODB.DataTypeEnum instead of adInteger. adBigInteger is an 8 byte data type whereas the 'long' datatype in vba and the 'INT' datatype in mysql are both 4 byte data types so this doesn't make sense.

Fix3
Change the variable type of lngAssemblyId from Long to Integer. This is strange because 1) I don't need to limit the variable to an Integer in fix 1.
EDIT: changing the data type to 'Variant' also works without the drawback of limiting to an integer.

Answer 1

I've since found a solution to this problem. Still unsure as to why this works and the original code doesn't though! Basically I use a subtly different way of binding the parameters:

Rather than this:

.Parameters.Append .CreateParameter(, adInteger, adParamInput, , lngAssemblyId)

use this instead:

.Parameters(0).Direction = adParamInput
.Parameters(0).Type = adInteger
.Parameters(0).Value = lngAssemblyId