Same SQL statement but different results via VBA Query

canuck

Registered User.
Local time
Today, 12:01
Joined
Apr 25, 2009
Messages
11
Hello,

I've often found answers using this forum's search function but am stumped and am hoping you folks can help!

I am running Access 2007 and have a nested query (3 levels) which I use VBA to format and export results to Excel. I am using ADO and have added the reference to the MS ADO 6.0 Library.

I have a SQL select statement which when used to populate an ADODB recordset returns a different result set then when run directly in the MS Access Query tool. It may be a caching problem as I believe the value it is returning used to be there.

Anyway, where is the SQL:

---
SELECT TransactionDate, Description, [Transaction ID], GrossAmt, FeeAmt, NetAmt, ID
FROM [VW-DIY Paypal Debits]
WHERE [Transaction ID] = '5K494903EW901800E' OR [Transaction ID] = '87T44061HP454861M'
---

This returns [Transaction ID] = '5K494903EW901800E'

And here is the VBA code:

---
Dim strSQL As String
Dim rsData As New ADODB.Recordset

strSQL = "SELECT TransactionDate, Description, [Transaction ID], GrossAmt, FeeAmt, NetAmt, ID FROM [VW-DIY Paypal Debits] WHERE [Transaction ID] = '5K494903EW901800E' OR [Transaction ID] = '87T44061HP454861M'"
rsData.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rsData.MoveFirst
---

This returns [Transaction ID] = '87T44061HP454861M'

To test further I created a c# application which connected to the database and ran the SQL. It returned the same result as the VBA. I am at wits and and would be very grateful for some advice!

Cheers,

Scott
 
What happens if you use AND instead of OR?
 
Both return no results as expected. Were you testing for corruption?
 
both results are included in both queries.

the order in which they are read is of no significance to access

if you need a particular order, you need to incliude the order in your select query.
 
I don't think my original post was clear.

When the SQL is run in VBA I get a single row where transaction ID is 87T44061HP454861M

When the SQL is run direct as a query I get a single different row where transaction ID is 5K494903EW901800E

So both results are not included in either query; only one row (but different ones!) is retrieved.
 
why do you think you are only retrieving 1 record

you are using the statement rst.movefirst

surely you just need to do rst.movenext until you reach the end of the file.
 
Change your SQL :-

From:-
rsData.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rsData.MoveFirst

To:-
rsData.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
MsgBox " >>> " & strSQL
rsData.MoveFirst

Copy the Text from the MsgBox (CTRL C) and post it here.
 
First off, thank you for the input!

Gemma - I cut off the EOF looping bit; sorry for the confusion. I should also have mentioned that the row returned by VBA does not exist in the query's result set; it was filtered out by the query! It is almost like VBA is referencing the wrong query object!

Gizmo - Here's the result:

---------------------------
Microsoft Office Access
---------------------------
>>> SELECT Year([TransactionDate]), Month([TransactionDate]),Day([TransactionDate]), [Description], [Transaction ID], [GrossAmt], [FeeAmt], [NetAmt] FROM [VW-DIY Paypal Debits] WHERE [Transaction ID] = '5K494903EW901800E' or [Transaction ID]= '87T44061HP454861M' ORDER BY [TransactionDate] ASC
---------------------------
OK
---------------------------
 
Try this:

SELECT TransactionDate, Description, [Transaction ID]
FROM [VW-DIY Paypal Debits]

what do you get?
 
Without the WHERE clause?

Here is something strange. If I change my Access Query to a make table query and run the same SQL statement but target the resulting table I get the same result from Access Query and VBA.

Are there some limitations to running SQL against an Access query that I am not familiar with?
 
If you don't answer my questions I can't help you.

I need to know what results you get when you run the query without the where clause.
 
Gizmo, in my reply I asked you if that was what you wanted. Sorry if I was not clear.

Without the where clause 130 records were returned from VBA and 116 records were returned from Access Query. The individual rows previously returned still exist in the larger result sets.

What are you testing for? Have you seen something like this before?
 
Given that you refer to using a query as the source, then I presume [VW-DIY Paypal Debits] is just that, a query rather than a table.

It seems very likely that this query is the problem - not the query with which you're wrapping it.
ADO being the method of execution is entirely consistent with this (and that using a resultant table yields the same results only further confirms it).. The usual suspect would be that your child (core) query is performing a match using the Like operator - as the wildcard would need to be "%" using ADO.
Even if that's the case you're, presumably, unable to change it (as you want to use the query in Access and perhaps DAO code too)?
You can use the "Alike" operator instead and use "%" as a wildcard in both scenarios to make the query consistent.

Of course - if that's not the issue at hand, then feel free to post the source SQL of [VW-DIY Paypal Debits] - as it's highly important to know what it actually is beyond a name.

Cheers.
 
Yes, as I stated in my first post I am retrieving data from a nested Access query. The SQL for [VW-DIY Paypal Debits] is:

SELECT [VW-DIY Paypal All Processed].Date AS TransactionDate, [VW-DIY Paypal All Processed].Description, [VW-DIY Paypal All Processed].[Transaction ID], Abs([Gross]) AS GrossAmt, Abs([Fee]) AS FeeAmt, Abs([Net]) AS NetAmt, [VW-DIY Paypal All Processed].ID
FROM [VW-DIY Paypal All Processed]
WHERE ((([VW-DIY Paypal All Processed].Description)<>"Withdraw Funds to a Bank Account (Bank Account)") AND (([VW-DIY Paypal All Processed].[Balance Impact])="Debit") AND (([VW-DIY Paypal All Processed].Type)<>"Refund")) OR ((([VW-DIY Paypal All Processed].Description)<>"Withdraw Funds to a Bank Account (Bank Account)") AND (([VW-DIY Paypal All Processed].[Balance Impact])="Credit") AND (([VW-DIY Paypal All Processed].Type)="Refund"))
ORDER BY [VW-DIY Paypal All Processed].Date;

This query is based on the query [VW-DIY Paypal All Processed], which is based on another query.

When you reference a MS Access Query object using ADODB does ADODB invoke the SQL associated with the Query object or does it simply request the results from Access? I had expected it to simply reference is as a normal DB view.

Thanks,

Scott
 
[VW-DIY Paypal All Processed].Date

Looks like you have a field named "Date" date is a reserved word and can cause problems. Change it to something else, like myDate or fldDate.
 
Hi Gizmo,

I converted my code to use DAO and it worked fine returning the result set I expected. Obviously DAO and ADODB access and/or process references to Access Query objects differently. Can you point me in the right direction to get up to educate myself on this?

I have always used ADODB as opposed to DAO thinking that the only advantage to DAO is it allowing database definition modifcations. Obviously the differences are more profound!

Thank you both for you assistance; while I don't understand why ADODB returned a different result set at least I can get what I need with DAO.

Kind regards,

Scott
 
Not that it necessarily matters (why will become clear later...) but I'd write the query as:

Code:
SELECT 
  P.[Date] AS TransactionDate, 
  P.[Description], P.[Transaction ID], 
  Abs(P.[Gross]) AS GrossAmt, 
  Abs(P.[Fee]) AS FeeAmt, 
  Abs(P.[Net]) AS NetAmt, 
  P.[ID] 
FROM 
  [VW-DIY Paypal All Processed] P
WHERE 
  (P.[Description]<>"Withdraw Funds to a Bank Account (Bank Account)")
    AND
  ( 
   (P.[Balance Impact]="Debit"
    AND 
   P.[Type]<>"Refund")
  OR 
   (P.[Balance Impact]="Credit"
    AND 
   P.[Type]="Refund")
  )
ORDER BY 
  P.[Date]

And if this was purely as a source for other queries to use - I'd dump the Order By clause without question.

However I can't help but notice that this query of yours selects from an object named [VW-DIY Paypal All Processed]. Another query??
What's its source?
 
That is another query.

I have tried to explain that I have three levels of nested queries.

At any rate I am now on the right track; thank you for your help.
 
Using DAO with the correct results merely re-enforces what I was saying earlier.
The Date name Tony raises is important (reserved words are really found out by the OLEDB provider - as well as the different syntax and wildcards etc).
I combatted that same thing in my example making sure each such reference was delimited with square brackets (e.g. P.[Date]).

However, as I mentioned, I'm of the opinion that the actual problem still lies yet another layer deep.
 
Well - I guess I must have missed that reference in your initial question to the 3 nested levels then huh?

If you feel you're on the right track by switching modes then great.
There must be a solution though - this isn't some great mystery. The differences between ADO and DAO are quite quantifiable.
 

Users who are viewing this thread

Back
Top Bottom