Update query does execute as a query but not via ADO connection.execute ? - ADO DAO RDO RDS

When using ADO, the wildcard character is %, not *.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Jürgen Germonpré" <jurgen.germonpre@wegdoengmail.com> wrote in message
news:uoaTOkIQIHA.4740@TK2MSFTNGP02.phx.gbl...
> Hello all,
>
> I have this problem.
>
> This is the query i want to run as part of a little batch routine I wrote:
>
> UPDATE tbl_SAP_PRICELIST SET MatchID =
> Left(Trim(MatchID),Len(Trim(MatchID))-2) WHERE (((Trim(MatchID)) Like
> '*AA' Or (Trim(MatchID)) Like '*AB' Or (Trim(MatchID)) Like '*AC' Or
> (Trim(MatchID)) Like '*BA' Or (Trim(MatchID)) Like '*CA' Or
> (Trim(MatchID)) Like '*BB' Or (Trim(MatchID)) Like '*CC'));
>
> This query runs just fine if I run it as an update query from within
> access, but it doesn't when I run it in this routine:
>
> '<CODE BEGIN-------------->
> Public Function BatchExecute()
> Dim db As ADODB.Connection
> Dim rs As ADODB.Recordset
>
> On Error GoTo errHndlr
> Set db = Application.CurrentProject.Connection
> Set rs = New ADODB.Recordset
> rs.Open "SELECT * FROM [tbl_SYS_Batch-Execute] WHERE [NotExecute] =
> False ORDER BY [Priority]", db
> rs.MoveFirst
>
> While Not rs.EOF
> Debug.Print "Executing: " & rs("SQLStatement")
> Call db.Execute(rs("SQLStatement"))
> rs.MoveNext
> Wend
> rs.Close
> db.Close
> Set rs = Nothing
> Set db = Nothing
>
> Exit Function
> errHndlr:
> If Err.Number = -2147217887 Then
> 'Field already exists
> Debug.Print "Error: " & Err.Number, Err.Description
> Resume Next
> Else
> Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,
> Err.HelpContext
> End If
> End Function
> '<CODE END-------------->
>
> First I thought it was the bracketing, quoting or double quoting in the
> query, so I changed about everything there, the result remains the same
> however. In a query, no problem, via ADO, no error, but no results.
>
> What is the nuance here ???
>
> Thank you for your advice.
>
> JG
>

Been out for a while..

That was the problem,
Thanks




"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23f%232Z7JQIHA.3516@TK2MSFTNGP02.phx.gbl...
> When using ADO, the wildcard character is %, not *.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Jürgen Germonpré" <jurgen.germonpre@wegdoengmail.com> wrote in message
> news:uoaTOkIQIHA.4740@TK2MSFTNGP02.phx.gbl...
>> Hello all,
>>
>> I have this problem.
>>
>> This is the query i want to run as part of a little batch routine I
>> wrote:
>>
>> UPDATE tbl_SAP_PRICELIST SET MatchID =
>> Left(Trim(MatchID),Len(Trim(MatchID))-2) WHERE (((Trim(MatchID)) Like
>> '*AA' Or (Trim(MatchID)) Like '*AB' Or (Trim(MatchID)) Like '*AC' Or
>> (Trim(MatchID)) Like '*BA' Or (Trim(MatchID)) Like '*CA' Or
>> (Trim(MatchID)) Like '*BB' Or (Trim(MatchID)) Like '*CC'));
>>
>> This query runs just fine if I run it as an update query from within
>> access, but it doesn't when I run it in this routine:
>>
>> '<CODE BEGIN-------------->
>> Public Function BatchExecute()
>> Dim db As ADODB.Connection
>> Dim rs As ADODB.Recordset
>>
>> On Error GoTo errHndlr
>> Set db = Application.CurrentProject.Connection
>> Set rs = New ADODB.Recordset
>> rs.Open "SELECT * FROM [tbl_SYS_Batch-Execute] WHERE [NotExecute] =
>> False ORDER BY [Priority]", db
>> rs.MoveFirst
>>
>> While Not rs.EOF
>> Debug.Print "Executing: " & rs("SQLStatement")
>> Call db.Execute(rs("SQLStatement"))
>> rs.MoveNext
>> Wend
>> rs.Close
>> db.Close
>> Set rs = Nothing
>> Set db = Nothing
>>
>> Exit Function
>> errHndlr:
>> If Err.Number = -2147217887 Then
>> 'Field already exists
>> Debug.Print "Error: " & Err.Number, Err.Description
>> Resume Next
>> Else
>> Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,
>> Err.HelpContext
>> End If
>> End Function
>> '<CODE END-------------->
>>
>> First I thought it was the bracketing, quoting or double quoting in the
>> query, so I changed about everything there, the result remains the same
>> however. In a query, no problem, via ADO, no error, but no results.
>>
>> What is the nuance here ???
>>
>> Thank you for your advice.
>>
>> JG
>>
>
>