INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access 2013 - Query Result Caching Problem

Access 2013 - Query Result Caching Problem

(OP)
Long time coder ... first time poster

I have three access databases:

  1. Local Development Database - local tables

  2. Production Database - over 300 tables linked from an ODBC data source (ServiceNow CMDB

  3. QA Database - over 300 tables linked from an ODBC data source (ServiceNow CMDB)



I execute queries in the morning against PROD and QA and store the results in Local Database.
Works great!

However, I was recently making some changes to the code and discovered that if I ran the queries in rapid succession (queries take between 15 and 60 seconds to run and return results), I discovered that on the first run, I get accurate result, but on subsequent runs, I get erroneous results. This is very consistent.

In my testing, I found that if I waited 12 minutes between executions (could be shorter - I went 1,3,6,9,12 minutes between runs), the problem went away - BUT only for the first iteration - starting with the second run, the results went back to incorrect.

I have boiled this down to some test code (no error checking) and would like some advice on how to work around what appears to be a caching problem with Access

The code runs "select count(*) from table" three different ways:

  1. select * from foo where foo is an access query that contains "select count (*) from table"

  2. select count(*) from table

  3. select count(*) from table as a pass-through query



Observations/Results:

  • Pass-Through Queries are VERY much faster

  • Pass-Through Queries (in this code example get the correct answer every time

  • Iteration #1 has correct answers for all three query types

  • Using Access queries uncovers some strange caching effect



Question:
How do I run queries against an ODBC Database over and over again and always get the correct results?
Note: I have done some additional testing where I mix pass-through queries and access queries and the erroneous result/caching problem crops up there as well

Your help would be greatly appreciated in understanding how to work around this problem (in this scenario)


Code:



Sub debugDriver()
Dim counter As Long

For counter = 1 To 3
debugCaching
Next counter

End Sub

Sub debugCaching()
Dim startDate As Date
Dim endDate As Date

Dim pwd As String

Dim prod As database
Dim prodDatabaseName As String
Dim prodRecords1 As Recordset
Dim prodRecords2 As Recordset
Dim prodRecords3 As Recordset
Dim prodAnswer1 As Long
Dim prodAnswer2 As Long
Dim prodAnswer3 As Long
Dim prodExpected As Long

Dim qa As database
Dim qaDatabaseName As String
Dim qaRecords1 As Recordset
Dim qaRecords2 As Recordset
Dim qaRecords3 As Recordset
Dim qaAnswer1 As Long
Dim qaAnswer2 As Long
Dim qaAnswer3 As Long
Dim qaExpected As Long

Dim query1 As String
Dim query2 As String
Dim query3 As String

Static iteration As Long

iteration = iteration + 1

startDate = Now()

pwd = CurrentProject.Path
prodDatabaseName = pwd & "\PROD.accdb"
prodExpected = 2546
qaDatabaseName = pwd & "\QA.accdb"
qaExpected = 2439

query1 = "select * from foo" ' foo contains "SELECT count(*) from OAUSER_cmdb_ci_appl" (ServiceNow ODBC)
query2 = "SELECT count(*) from OAUSER_cmdb_ci_appl" ' (ServiceNow ODBC)
query3 = "select * from fooPassThruCreds" ' fooPassThruCreds contains "SELECT count(*) from cmdb_ci_appl" (ServiceNow ODBC Pass-Through Query)

Set prod = DBEngine.OpenDatabase(prodDatabaseName)
Set qa = DBEngine.OpenDatabase(qaDatabaseName)

Set prodRecords1 = prod.OpenRecordset(query1)
If prodRecords1.RecordCount > 0 Then
prodAnswer1 = prodRecords1.Fields(0)
End If

Set prodRecords2 = prod.OpenRecordset(query2)
If prodRecords2.RecordCount > 0 Then
prodAnswer2 = prodRecords2.Fields(0)
End If

Set prodRecords3 = prod.OpenRecordset(query3)
If prodRecords3.RecordCount > 0 Then
prodAnswer3 = prodRecords3.Fields(0)
End If

Set qaRecords1 = qa.OpenRecordset(query1)
If qaRecords1.RecordCount > 0 Then
qaAnswer1 = qaRecords1.Fields(0)
End If

Set qaRecords2 = qa.OpenRecordset(query2)
If qaRecords2.RecordCount > 0 Then
qaAnswer2 = qaRecords2.Fields(0)
End If

Set qaRecords3 = qa.OpenRecordset(query3)
If qaRecords3.RecordCount > 0 Then
qaAnswer3 = qaRecords3.Fields(0)
End If

endDate = Now()

Debug.Print "-------------------------------------------------------------------------------------"
Debug.Print "Execution Iteration = " & iteration
Debug.Print "Start Date = " & startDate
Debug.Print "PROD Database = " & prod.name
Debug.Print "PROD Answer1 (foo) = " & prodAnswer1 & " (Should be " & prodExpected & ")"
Debug.Print "prodRecords1.Type = " & prodRecords1.Type
Debug.Print "PROD Answer2 (count) = " & prodAnswer2 & " (Should be " & prodExpected & ")"
Debug.Print "prodRecords2.Type = " & prodRecords2.Type
Debug.Print "PROD Answer3 (Pass-Through) = " & prodAnswer3 & " (Should be " & prodExpected & ")"
Debug.Print "prodRecords3.Type = " & prodRecords3.Type
Debug.Print "QA Database = " & qa.name
Debug.Print "QA Answer1 (foo) = " & qaAnswer1 & " (Should be " & qaExpected & ")"
Debug.Print "qaRecords1.Type = " & qaRecords1.Type
Debug.Print "QA Answer2 (count) = " & qaAnswer2 & " (Should be " & qaExpected & ")"
Debug.Print "qaRecords2.Type = " & qaRecords3.Type
Debug.Print "QA Answer3 (Pass-Through) = " & qaAnswer3 & " (Should be " & qaExpected & ")"
Debug.Print "qaRecords3.Type = " & qaRecords3.Type
Debug.Print "End Date = " & endDate

Set prodRecords1 = Nothing
Set prodRecords2 = Nothing
Set prodRecords3 = Nothing
prod.Close
Set prod = Nothing

Set qaRecords1 = Nothing
Set qaRecords2 = Nothing
Set qaRecords3 = Nothing
qa.Close
Set qa = Nothing

End Sub



Results:
The results from executing debugDriver() - runs debugCaching 3 times are:


-------------------------------------------------------------------------------------
Execution Iteration = 1
Start Date = 3/25/2016 7:59:37 PM
PROD Database = C:\FOLDER\PROD.accdb
PROD Answer1 (foo) = 2546 (Should be 2546)
prodRecords1.Type = 2
PROD Answer2 (count) = 2546 (Should be 2546)
prodRecords2.Type = 2
PROD Answer3 (Pass-Through) = 2546 (Should be 2546)
prodRecords3.Type = 2
QA Database = C:\FOLDER\QA.accdb
QA Answer1 (foo) = 2439 (Should be 2439)
qaRecords1.Type = 2
QA Answer2 (count) = 2439 (Should be 2439)
qaRecords2.Type = 2
QA Answer3 (Pass-Through) = 2439 (Should be 2439)
qaRecords3.Type = 2
End Date = 3/25/2016 8:00:31 PM
-------------------------------------------------------------------------------------
Execution Iteration = 2
Start Date = 3/25/2016 8:00:31 PM
PROD Database = C:\FOLDER\PROD.accdb
PROD Answer1 (foo) = 2439 (Should be 2546)
prodRecords1.Type = 2
PROD Answer2 (count) = 2439 (Should be 2546)
prodRecords2.Type = 2
PROD Answer3 (Pass-Through) = 2546 (Should be 2546)
prodRecords3.Type = 2
QA Database = C:\FOLDER\QA.accdb
QA Answer1 (foo) = 2546 (Should be 2439)
qaRecords1.Type = 2
QA Answer2 (count) = 2546 (Should be 2439)
qaRecords2.Type = 2
QA Answer3 (Pass-Through) = 2439 (Should be 2439)
qaRecords3.Type = 2
End Date = 3/25/2016 8:00:54 PM
-------------------------------------------------------------------------------------
Execution Iteration = 3
Start Date = 3/25/2016 8:00:54 PM
PROD Database = C:\FOLDER\PROD.accdb
PROD Answer1 (foo) = 2439 (Should be 2546)
prodRecords1.Type = 2
PROD Answer2 (count) = 2439 (Should be 2546)
prodRecords2.Type = 2
PROD Answer3 (Pass-Through) = 2546 (Should be 2546)
prodRecords3.Type = 2
QA Database = C:\FOLDER\QA.accdb
QA Answer1 (foo) = 2546 (Should be 2439)
qaRecords1.Type = 2
QA Answer2 (count) = 2546 (Should be 2439)
qaRecords2.Type = 2
QA Answer3 (Pass-Through) = 2439 (Should be 2439)
qaRecords3.Type = 2
End Date = 3/25/2016 8:01:19 PM


RE: Access 2013 - Query Result Caching Problem

Interesting.

My first thought would be the SPT Queries that you say work.

A quick Google of ServiceNow... It looks like that is a cloud service. Access does some really weird things with it's local engine. I can't help but wonder if you have some weird combination of ISP cache and Access requests. My normal expectation would be that ODBC queries would run ok but maybe slower than the SPT or even Access native data counterparts.

I know that isn't very helpful but the thread caught my eye and I am curious.

I wonder if your results are repeatable with say a SQL Express database.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close