ado.execute problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a function in an ADO module that generates query text to run using the
execute method. Here's the generated query text:

"UPDATE TOPPS_CLAIMS SET AU_KEY = 101110 WHERE PAID_YYYYMM = '200704' AND
COR LIKE 'L*' AND COR NOT IN ('LM', 'LN') AND TRIM(MEMBER_SUB_PROGRAM) IN
('IEHA', 'IESR', 'IESS', 'IESU') AND (GROUP_NUMBER NOT LIKE '4?????' AND
GROUP_NUMBER NOT LIKE '5?????');"

This query works properly when I copy the code into a new Access SQL query
and run it. This particular one does not work when run with the execute
method in my function. Here is the portion of code from the function that
runs the query:

Dim strSQL as String
Dim cnnLocal As New ADODB.Connection
Set cnnLocal = CurrentProject.Connection
cnnLocal.CursorLocation = adUseClient

strSQL = <THE QUERY TEXT ABOVE>
cnnLocal.Execute strSQL, intAffected


I've tried several options on the line with intAffected that produce the
same results.
Please let me know if you have any ideas.

Thanks!
 
Does not Work ??? Wrong results, no updates, errors?

You might try changing the wild cards from ? to _ and * to %

"UPDATE TOPPS_CLAIMS SET AU_KEY = 101110
WHERE PAID_YYYYMM = '200704' AND
COR LIKE 'L%'
AND COR NOT IN ('LM', 'LN')
AND TRIM(MEMBER_SUB_PROGRAM) IN
('IEHA', 'IESR', 'IESS', 'IESU')
AND (GROUP_NUMBER NOT LIKE '4_____'
AND GROUP_NUMBER NOT LIKE '5_____');"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
It doesn't do any updates. If I run the same code as a query, I get 400+
rows updated. I tried switching from ? to _ and I still don't get updates
with this query when run from code. If I run it as a query, I get too many
rows updated. I'll try switchin * to %.
 
Thanks, I tried the first suggestion and didn't get any updates when run
from code and too many when run from a query. I'll try the second suggestion.
 
I tried the second suggestion and got similar results. I have an alternative
solution: I can put all of my generated code into new queries and call them
from a macro. I was trying to make this application more self-maintaining
than that though.
 
Back
Top