Participating Frequently

Question

MS Access database queries don't retrieve latest data

Forum|Forum|17 years ago
January 28, 2009
9 replies
1990 views

Hello,

This is a shared host environment I'm trying to support. No access to Coldfusion control panel.

The datasource is an Access mdb. If we upload a new .mdb file to the website, the new data or new tables within the database are not reflected in the cfquery requests until usually several hours later. There is no caching being done within the cfquery tag. The browser is not caching the page, we've flushed the cache and used Fiddler to confirm it was retrieving a new page from the website.

Does the Access datasource do some type of caching of the old .mdb file, or does the jdbc driver underlying it? Or is there a setting somewhere I don't know about that would explain this?

Thanks for any pointers
Jason

Database access

This topic has been closed for replies.

M

morrisjAuthor

Participating Frequently

For the architecture, it is an app I've inherited, but it's a dead-simple app. Just a <cfquery> with a <cfoutput> following it. No objects, no architecture like fusebox, absolutely nothing other than a plain .cfm page. Very basic. Not even any application.cfm or application.cfc that I can see.

N

Newsgroup_User

Inspiring

But I have no idea how that would apply to the ability to change the
database name without changing the DSN settings and not have every thing
break.

N

Newsgroup_User

Inspiring

Going back to the beginning...

How familiar are you with the Application architecture? Is this
something you have just inherited.

The only idea I have left is that the application itself is caching
data? It is not an uncommon development practice to create data objects
that read common information from a database at application start and
store this in application scope variables so that database reads do not
need to be done with every request.

M

morrisjAuthor

Participating Frequently

Yes, it was a different change, I wanted to make sure of that myself.

It is just plain old ftp. That's about the only access I have to the server.

It is a hosting provider, but a very poor one. They basically have someone who can reboot the server. Whoever set it up originally isn't around anymore, and I can't get any kind of access beyond ftp because of 26 layers of "security" as it is shared hosting. Argh.

I knew it was strange, but thought maybe it was a common problem with Access.

Thanks for suggestions, and any other ideas are always welcome.

N

Newsgroup_User

Inspiring

yeah, this sure IS strange... i have never seen anything like this...
i would, again, go with Ian and contact the host to find out what sort
of magic they are using to power their servers...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

N

Newsgroup_User

Inspiring

morrisj wrote:

> So, to make sure, I again rename db.mdb to db2.mdb. Then I run a new cfquery
> update query, thinking that will fail. Works fine. Download db2.mdb and it
> has the change in it. So the datasource seems to be following the .mdb, even
> if I rename it.

Just to make sure, this was a different change correct? Something
different from the first time you downloaded the file?

>
> wtf? Super-powers access files?
>

This sure is strange behavior. Is this with a hosting provider? Have
you contacted their support to see how they may have this set up. If
this is a web host, this has got to be some kind of extra-normal set up!

If so, do you just directly upload the .mdb file via file transfer, like
FTP? Or do you do it through some kind of web portal application? I
can only imagine this behavior if it some type of portal application
that doesn't just upload the .mdb file. But rather exports the database
schema from the Access file into some other database management system.
I have never heard of this, but I could imagine some sophisticated
system that could export Access into MSSQL, they actually do understand
each other pretty well.

M

morrisjAuthor

Participating Frequently

I tell you, this host is psychotic.

If I change the name of the from db.mdb to db2.mdb, the site still works fine.

BUT, I uploaded a test cfm page with a cfquery that updated an existing row. Rename the database back to db.mdb, then run that cfm page and immediately download the db.mdb, the change is in the db.mdb. So the changes are getting to the db.mdb.

So, to make sure, I again rename db.mdb to db2.mdb. Then I run a new cfquery update query, thinking that will fail. Works fine. Download db2.mdb and it has the change in it. So the datasource seems to be following the .mdb, even if I rename it.

wtf? Super-powers access files?

F

Fetch

Participating Frequently

Hmm...just out of curiousity, can you setup a test page in CF that writes a single value to the database in a test table, then does a query on that table to return all the results? After the upload run your test page and see if it writes then reads correctly...then check your other pages to see if they read the new database correctly after that.

M

morrisjAuthor

Participating Frequently

I know this sounds psychotic, but this is what it's doing. :)

Insert a new record in cfquery, retrieve same record - no problem. Then again try to query existing records that were changed in the newly uploaded file - no luck, still shows the old data.

Delete the new records via cfquery, they go away.

Update a value on an existing record in cfquery, the new value appears in the query. But other records that were only changed in the uploaded .mdb, not through the website, again don't pick up any changes.

So I tried a query in cfm that did "UPDATE IC SET address=address", thinking that might flush whatever cache there is and pick up the data from the uploaded mdb. Nope, it's smarter than me and that the cache didn't change.

It's like the datasource driver is caching the entire table and any changes done. It also writes out the changes, but once it's done an initial query of the base .mdb it doesn't re-query it again. Thus changing the mdb doesn't cause the records to change because the cache causes the mdb to be ignored. Weirdest thing I've ever seen, but I've only ever done CF + SQLServer in the past.

Jason

N

Newsgroup_User

Inspiring

morrisj wrote:
>
> Does the Access datasource do some type of caching of the old .mdb file, or
> does the jdbc driver underlying it? Or is there a setting somewhere I don't
> know about that would explain this?
>
> Thanks for any pointers
> Jason
>

There is a little know caching potential in queries if SELECT * has been
used. Somewhere between the driver and the database, the schema of a
database is cached with the * is used.

This usually causes errors, but I suppose it could manifest as you have
described.

HTH
Ian

M

morrisjAuthor

Participating Frequently

Thanks for the idea, but that doesn't fix it.

I changed the query from a SELECT * to a SELECT col1, col2 and the same behaviour persists. Anyone have other ideas?

Thanks

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded