B
Barry-Jon
Hi,
I have as saved access update query called 'qupdEnvironmentActivate'.
The SQL for the query is as follows:
PARAMETERS [Activate] Text ( 5 );
UPDATE stblEnvironments SET stblEnvironments.Selected =
IIf([Activate]=[Environment],True,False)
WHERE IIf([Activate] In (SELECT Environment FROM stblEnvironments
,
Environment Like "*", Environment Is Null);
The query runs correctly when I run it from the access UI. The query
accepts one parameter. The where condition evaluates the passed
parameter and if it is a valid [Environment] value it allows the query
to update all rows, otherwise it updates no rows. The update changes
the true/false value of the [Selected] field on the row where the
parameter equals the [Environment] field to TRUE and sets all other
[Selected] values to FALSE.
When I try to execute the query using ADO the query never updates any
rows (even though I know the value being passed is a valid environment)
and always returns 0 rows as being updated (as you would expect becuase
the query is not running correctly. I get no errors in the code and I
am very confident the syntax is correct (it works for other update
queries). For the record here is the VBA code (using ADO):
Dim cmd As ADODB.Command
Dim strParam As String
Dim lng As Long
strParam = "DEV"
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qupdEnvironmentActivate"
cmd.Execute lng, Array(strParam)
MsgBox lng
For completeness the table structure of 'stblEnvironments' is as
follows;
Field Type
----- ----
Environment Char (5)
EnvironmentDesc Char (255)
Selected Boolean
I don't understand why this isn't working given that the code works for
other queries and the query itself works outside the code. Any
help/guidance would be much appreciated...
Barry-Jon
I have as saved access update query called 'qupdEnvironmentActivate'.
The SQL for the query is as follows:
PARAMETERS [Activate] Text ( 5 );
UPDATE stblEnvironments SET stblEnvironments.Selected =
IIf([Activate]=[Environment],True,False)
WHERE IIf([Activate] In (SELECT Environment FROM stblEnvironments
,Environment Like "*", Environment Is Null);
The query runs correctly when I run it from the access UI. The query
accepts one parameter. The where condition evaluates the passed
parameter and if it is a valid [Environment] value it allows the query
to update all rows, otherwise it updates no rows. The update changes
the true/false value of the [Selected] field on the row where the
parameter equals the [Environment] field to TRUE and sets all other
[Selected] values to FALSE.
When I try to execute the query using ADO the query never updates any
rows (even though I know the value being passed is a valid environment)
and always returns 0 rows as being updated (as you would expect becuase
the query is not running correctly. I get no errors in the code and I
am very confident the syntax is correct (it works for other update
queries). For the record here is the VBA code (using ADO):
Dim cmd As ADODB.Command
Dim strParam As String
Dim lng As Long
strParam = "DEV"
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qupdEnvironmentActivate"
cmd.Execute lng, Array(strParam)
MsgBox lng
For completeness the table structure of 'stblEnvironments' is as
follows;
Field Type
----- ----
Environment Char (5)
EnvironmentDesc Char (255)
Selected Boolean
I don't understand why this isn't working given that the code works for
other queries and the query itself works outside the code. Any
help/guidance would be much appreciated...
Barry-Jon