andyMcCabe
Programmer
Long time coder ... first time poster
I have three access databases:
<ol 1)>
<li>Local Development Database - local tables</li>
<li>Production Database - over 300 tables linked from an ODBC data source (ServiceNow CMDB</li>
<li>QA Database - over 300 tables linked from an ODBC data source (ServiceNow CMDB)</li>
</ol>
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:
<ol 1)>
<li>select * from foo where foo is an access query that contains "select count (*) from table"</li>
<li>select count(*) from table</li>
<li>select count(*) from table as a pass-through query</li>
</ol>
<b><u>Observations/Results:</u></b>
<ul>
<li>Pass-Through Queries are VERY much faster
</li>
<li>Pass-Through Queries (in this code example get the correct answer every time
</li>
<li>Iteration #1 has correct answers for all three query types
</li>
<li>Using Access queries uncovers some strange caching effect
</li>
</ul>
<b><i>Question:</i></b>
<b>How do I run queries against an ODBC Database over and over again and always get the correct results?</b>
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)
<b><u>Code:</u></b>
<code Access VBA>
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
</code>
<b><u>Results:</u></b>
The results from executing debugDriver() - runs debugCaching 3 times are:
<code Text Results>
-------------------------------------------------------------------------------------
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
</code>
I have three access databases:
<ol 1)>
<li>Local Development Database - local tables</li>
<li>Production Database - over 300 tables linked from an ODBC data source (ServiceNow CMDB</li>
<li>QA Database - over 300 tables linked from an ODBC data source (ServiceNow CMDB)</li>
</ol>
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:
<ol 1)>
<li>select * from foo where foo is an access query that contains "select count (*) from table"</li>
<li>select count(*) from table</li>
<li>select count(*) from table as a pass-through query</li>
</ol>
<b><u>Observations/Results:</u></b>
<ul>
<li>Pass-Through Queries are VERY much faster
</li>
<li>Pass-Through Queries (in this code example get the correct answer every time
</li>
<li>Iteration #1 has correct answers for all three query types
</li>
<li>Using Access queries uncovers some strange caching effect
</li>
</ul>
<b><i>Question:</i></b>
<b>How do I run queries against an ODBC Database over and over again and always get the correct results?</b>
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)
<b><u>Code:</u></b>
<code Access VBA>
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
</code>
<b><u>Results:</u></b>
The results from executing debugDriver() - runs debugCaching 3 times are:
<code Text Results>
-------------------------------------------------------------------------------------
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
</code>
