Update query does execute as a query but not via ADO connection.execute ?

[Jürgen Germonpré]

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

 

[Douglas J. Steele]

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

 

[Jürgen Germonpré]

Been out for a while..

That was the problem,
Thanks

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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