IN Param/OUT Param / VBA-access / ODBC

[Carlos Fonseca]

Hi,

I'm trying to insert a name and age into a table and return an id parameter using ADODB. VBA - access.

******************************************************************
***************************mySQL Procedure************************
******************************************************************
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_contacts`(
out parm_id int(11)
,in parm_nome varchar(50)
,in parm_idade int(11)
)
begin

set autocommit=0;

start transaction;
insert into contactos(nome, idade)
values (parm_nome, parm_idade);


if(row_count()<>1) then
set parm_id = -1;
rollback;
else
set parm_id = last_insert_id ();
commit;
end if;
end


***************************************************************************
*******************************************************************************
Form with command button that allows to call "Connect_SQL" whe I insert a name and age into textBox --> name, and textBox ---> age.
*******************************************************************************
Option Compare Database
Private Sub salvar_Click()

Dim name As String
Dim age As Integer

nome = Me.name
idade = Me.age.Value

ligacao_SQL name, age
End Sub

*****************************************************
*****************************************************

Option Compare Database
Public Sub Connect_SQL(nome_access_param As String, idade_access_param As Integer)

Dim...

'*********** Connection to DNS ***********
Set adoRS = CreateObject("ADODB.Recordset")
Set myconnSQL = CreateObject("ADODB.Connection") 'Novo objecto de ligação
strconnect = "DRIVER={MySQL ODBC 5.1 Driver};DNS=Carlos_amostra; server=127.0.0.1; Database=carlos_teste_schema; UID=root; PASSWORD=123; port=3306; option=16384"
myconnSQL.Open strconnect 'Abrir "Domain Name System" = Carlos_amostra
'*****************************************

'*********** Parameters ******************
Set id_parm = CreateObject("ADODB.Parameter")
Set nome_parm = CreateObject("ADODB.Parameter")
Set idade_parm = CreateObject("ADODB.Parameter")
'*****************************************

'********* Stored Procedure *************
Set cmdconnSQL = CreateObject("ADODB.command")
With cmdconnSQL
.ActiveConnection = myconnSQL
.CommandType = 4 'comando necessário para stored procedure
.CommandText = "CALL inserir_contactos()"

'Parametros a serem utilizados pelo "insert_contacts"
Set nome_parm = cmdconnSQL.CreateParameter("parm_nome", 200, 1, 20, nome_access_param) 'insert name
Set idade_parm = cmdconnSQL.CreateParameter("parm_idade", 3, 1, 5, idade_access_param) 'insert age
Set id_parm = cmdconnSQL.CreateParameter("parm_id", 3, 2, 5, parm_id) 'output id
.Parameters.Append nome_parm
.Parameters.Append idade_parm
.Parameters.Append id_parm
.Execute

If parm_id = -1 Then
MsgBox "record insert Successfully" 'A kind of error message
Else
MsgBox "record not insert successfully"
End If
End With
'*******************************************

Set cmdconnSQL = Nothing
myconnSQL.Close
Set myconnSQL = Nothing
End Sub


When the command is executed , I get this error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[MySQL][ODBC 5.1 Driver][mysqld-5.5.8]OUT or INOUT argument 1 for routine carlos_teste_schema.insert_contacts is not a variable or NEW pseudo-variable in BEFORE trigger"

Hope someone can help me
Thanks

Best regards
Carlos