Odd Access db issue - the query that won't update from fresh data

[WolfShade]

Hello, all,

I am just fiddling around, a bit, trying to learn something new:  setting up and using an MS Access db in CF10.  Simple, right?

No.. not really.  But I didn't start this thread to give you the details and minutiae regarding getting it to work.  It works.

The issue I'm having is that I wrote a simple query to fetch data from the FIRST table I created (Games) and am retrieving six columns of data.

I'm running a query, using CFDUMP to display it.

I went into the Access database and added three rows of info.  When I refresh the page, the CFDUMP shows only the first three rows.

I deleted those three rows from the Access db, refreshed the page, and still see those three rows of data - nothing else.  Not the new rows I entered.  Nothing changes.

Not sure what to do next.  Please help?

V/r,

^_^

[BKBK]

Cache perhaps. To rule out page cache, force a refresh by adding an arbitrary argument to the URL, for example, http://...testpage.cfm?myVar=x

To rule out database connection cache, go to the Access datasource settings in the ColdFusion Administrator. Click on 'Show Advanced Settings', uncheck the checkbox 'Maintain Connections' and submit the changes.

[WolfShade]

Hi, BKBK​,

I was kind of tired when I wrote my post, and didn't include everything.  Sorry.  I'm perpetually tired.

When I first noticed it, I tried your suggestion of using an arbitrary URL parameter; no change.  Even used Ctrl-F5; no change.

Logged on to CFAdmin, and made sure that nothing is being cached - all 'cache-related' checkboxes unchecked.  No change.  Cleared cache, just in case; no change.

Altered the query, slightly, to force a fresh query (added " , 'hey' as joe " to the select - it appeared in the refresh.. next to the three records that had been deleted, instead of the three new records that were in the database; otherwise, still getting the deleted records.)

Added attribute " cachedwithin='#CreateTimeSpan(0,0,0,0)#'" to the CFQUERY tag; no change.

And then I took it one step further.  I use FireFox for my development.  This page had never been opened in IE.  I opened it in IE; no change.

This is clearly not a cache issue - neither the server-side, nor the client-side.

This has me totally baffled.

V/r,

^_^

PS:  In CF10, CFAdmin Datasources, when creating an Access DSN, "Maintain Connections" is defaulted OFF.  I never checked it on.

[EddieLotter]

I'm assuming you're doing this on a dev box and you have debugging information turned on. In the debugging information is the text of all queries run for the page. Copy the text of the query and paste it directly into Access and run the query locally. What result do you get?

Cheers

Eddie

[WolfShade]

Hi, Eddie Lotter​,

Yes, this is on my local machine, isolated from the Internet.  this.showdebugoutput is set to false. 

I am using CFDUMP to display the data, which also contains the text of the query.  I have copied that, and pasted it into an Access 2007 query analyzer, and that will bring up the current records, not the deleted ones.  So, I'm going to go out on a limb and guess that the issue is in CF, not the DB nor the client.  Could be a driver issue?  Maybe.

V/r,

^_^

[BKBK]

Perhaps something went wrong during the manual insert. Wat happens when you insert the 3 rows by means of an INSERT query?

[WolfShade]

Hi, BKBK​,

I haven't gotten that far, yet.  I just created the db, manually inserted some arbitrary data, and ran a SELECT query against it so I could see what was being returned so that I can set format, etc. on the display page.  I'll give your suggestion a shot, tho.

I'll report back with my findings.

V/r,

^_^

[WolfShade]

Okay, now I'm REALLY confused.

On the page that runs the SELECT query and CFDUMPs the data, prior to the SELECT query, I added an INSERT query.

Every time I run the page, data is added and the CFDUMP shows it.  HOWEVER - looking at the database in Access, no data is being added.  I still have only the second set of rows that I added, earlier; none of the INSERT query data.

WTF???

[EddieLotter · Accepted solution]

I'm beginning to suspect that the Access file that you are using and the Access file that CF is using, are not one and the same.

Try renaming the Access file and see if CF magically continues to work.

Cheers

Eddie

[WolfShade]

I double checked both the odbcad32.exe and Datasource in CFAdmin.  You were right.  I have no idea how it happened, but the ODBC was pointing to a 64bit (accdb) db file, but CF was pointing to the 32bit (mdb) db file.  Could have sworn that they were both pointing to the same file.  I have no idea why I have both 32- and 64bit versions.

Sadly, I cannot use the 64bit version with CF.  I would have to completely uninstall the 32bit MS Office that is on my DEV system and then install the 64bit MS Office (which we don't have) in order to do so.  Grrr.. argh.. (for Joss Whedon fans).

Thanks for the suggestion, Eddie Lotter​.

V/r,

^_^

[WolfShade]

A co-worker suggested that the ODBC DSN that I created in Windows might be the culprit.  It might be caching the query.  However, I have opened odbcad32.exe (where I created the DSN) and cannot find any setting for caching.  Windows 7. 

Could that be it?  Windows is caching the query, not CF or the browser?

V/r,

^_^