Executing Update Query With Parameters As ADO Command

[Barry Jon]

Hi,

I am trying to run a saved access (2002/3) update query (SQL below) VBA code using ADO.

This is the SQL of the query:
PARAMETERS [FileKey] Text ( 255 ), [NewPath] Text ( 255 );
UPDATE tblFilePaths SET tblFilePaths.Path = [NewPath]
WHERE (((tblFilePaths.File)=[FileKey]));

The query takes two parameters which, if I want to execute the query in code, I need to pass to the query. I could achieve this in DAO using this code;

Function UpdateFilePath(strFileKey As String, strNewPath As String) As Long

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("qupdCommand")

qdf.Parameters("[FileKey]").Value = strFileKey
qdf.Parameters("[NewPath]").Value = strNewPath

qdf.Execute

UpdateFilePath = qdf.RecordsAffected

End Function

I know how to open a recordset based on a saved select parameter query use ADO. For instance the following query:

PARAMETERS [FileKey] Text ( 255 );
SELECT tblFilePaths.Path
FROM tblFilePaths
WHERE (((tblFilePaths.File)=[FileKey]));

Could be used as the basis of a recordset using code something like;

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdTable
cmd.CommandText = "qselFilePath"
cmd.Parameters("[FileKey]").Value = "DataBE"

Set rst = New ADODB.Recordset
rst.Open cmd

However I can't figure out out to run a saved action query use ADO and not DAO. I have to do it in ADO. You can't pass the parameters in the ways I have shown/used already. From what I gather you need to pass the parameters as the second argument in the execute method (Parameters Optional. A Variant array of parameter values passed with an SQL statement. (Output parameters will not return correct values when passed in this argument.). Does anyone know how to construct the variant array? For instance do you pass the name of the parameters and the values or just the values... Any help anyone can offer would be much appreciated.

Best Regards

Barry-Jon

 

[Barry Jon]

Hey, sorry for the hassle - just figured it out. For those of you curious/interested to know....
You only pass the paramter values in the parameter array. You pass them in the order they are declared in the query.

E.g.

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qupdCommand"

cmd.Execute lngRowAffected, Array(strFileKey, strNewPath)

Hi,

I am trying to run a saved access (2002/3) update query (SQL below) VBA code using ADO.

This is the SQL of the query:
PARAMETERS [FileKey] Text ( 255 ), [NewPath] Text ( 255 );
UPDATE tblFilePaths SET tblFilePaths.Path = [NewPath]
WHERE (((tblFilePaths.File)=[FileKey]));

The query takes two parameters which, if I want to execute the query in code, I need to pass to the query. I could achieve this in DAO using this code;

Function UpdateFilePath(strFileKey As String, strNewPath As String) As Long

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("qupdCommand")

qdf.Parameters("[FileKey]").Value = strFileKey
qdf.Parameters("[NewPath]").Value = strNewPath

qdf.Execute

UpdateFilePath = qdf.RecordsAffected

End Function

I know how to open a recordset based on a saved select parameter query use ADO. For instance the following query:

PARAMETERS [FileKey] Text ( 255 );
SELECT tblFilePaths.Path
FROM tblFilePaths
WHERE (((tblFilePaths.File)=[FileKey]));

Could be used as the basis of a recordset using code something like;

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdTable
cmd.CommandText = "qselFilePath"
cmd.Parameters("[FileKey]").Value = "DataBE"

Set rst = New ADODB.Recordset
rst.Open cmd

However I can't figure out out to run a saved action query use ADO and not DAO. I have to do it in ADO. You can't pass the parameters in the ways I have shown/used already. From what I gather you need to pass the parameters as the second argument in the execute method (Parameters Optional. A Variant array of parameter values passed with an SQL statement. (Output parameters will not return correct values when passed in this argument.). Does anyone know how to construct the variant array? For instance do you pass the name of the parameters and the values or just the values... Any help anyone can offer would be much appreciated.

Best Regards

Barry-Jon