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