Same SQL statement but different results via VBA Query

[canuck]

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

 

[canuck]

Both return no results as expected. Were you testing for corruption?

 

[gemma-the-husky]

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.

 

[canuck]

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.

 

[gemma-the-husky]

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.

 

[canuck]

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
---------------------------

 

[canuck]

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?

 

[canuck]

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?

 

[LPurvis]

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.

 

[canuck]

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

 

[canuck]

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

 

[LPurvis]

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

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?

 

[canuck]

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.

 

[LPurvis]

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.

 

[LPurvis]

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.

 

[canuck]

Thanks LPurvis. I had read your earlier reply too quickly; thought it was from Gizmo again. Would have said hello had I noticed it was a new-to-this-thread-user!

 

[canuck]

Initially I did want to find the solution to this and move forward in ADO. However if the issue is ADO becoming involved in processing wildcards specified in the existing Access Query objects I am loath to change them as they have been tested.

So using DAO which either defers to access to provide the result set or processes the queries similar to access seems to be my only path short of creating intermediate tables from the queries and using ADO to query them.

I am still unsure why ADO (or DAO) becomes involved in the actuall processingin of the Access Query objetcts so I'll have to do some reading I guess!

Thank you again LPurvis.

Scott

 

[LPurvis]

It's a different provider that is providing the interface to the Jet data. The OLEDB provider for Jet has some differences to the DAO interface in syntax and implementation, for example wildcards and sensitivity to reserved words. (Conversely it has some abilities that DAO and Access don't!)

Changing your queries defintion to Alike "%... would be required (if it is what's required, I couldn't say without seeing all teers of the query request) and if you don't want to make such changes then you don't have a great deal of choice but to abandon ADO in this respect.
(You could code loading the query definition into a variable and alter it before executing it in ADO - but again, that's effort).

You'll know best how you want to proceed.

Cheers.

 

[dysert]

Is anyone still watching this thread? I hope so because I have a similar problem to the OP. I'm running Access 2013 under Windows 7. (And I'm an Access novice.)

When I run a report I get a wildly different result set versus using the same SQL in VBA. Can someone please help me figure out what's going on?

Thanks!

 

[gemma-the-husky]

I would start a new thread.

You can't be using the same query definition, or you would get the same result. Can you post the SQL for the queries?