MS Access Datascources fail

[Duane McGuire]

After a period of inactivity, all MS Access datasources on our development
server fail. A restart of coldfusion clears the error condition. By
inactivity, I mean a period in time where the Access datasources are not
accessed by apps on the server. Since most of our development is Oracle based,
the Access datasources are infrequently used.

When the datasources go to the error state, a query on any of the Access
datasources returns "Error Executing Database Query.
Unspecified error".

We are using: Microsoft Access with Unicode as the datasource driver.

Other aspects of our install are:

Server Product ColdFusion MX
Version 7,0,1,116466
Edition Enterprise
Operating System Windows 2003
OS Version 5.2
Java Version 1.4.2_05
Java Vendor Sun Microsystems Inc.
Java VM Version 1.4.2_05-b04
Java VM Vendor Sun Microsystems Inc.
Java VM Name Java HotSpot(TM) Server VM
Java Specification Version 1.4
Java Specification Vendor Sun Microsystems Inc.
Java Specification Name Java Platform API Specification
Java Class Version 48.0

Any insights would be appreciated!

[paross1]

Back when I used to use Access DSNs (on ColdFusion 4.5), we used to have to
cycle ColdFusion every night because of the datasources consuming resources. I
found this tech note that mentions this, but I'm not sure to which versions of
CF it still applies.

[url]http://www.macromedia.com/go/tn_17034[/url]

Microsoft Access and ODBC
The Microsoft Access ODBC driver dynamically allocates as much memory as it
needs in order to process a database request. In certain situations, however,
the driver needs and attempts to allocate more memory than the maximum that it
is allowed to use. When this occurs the driver can come to a halt, and stop
responding to ColdFusion requests. ColdFusion then begins to pile up all of the
subsequent incoming requests until it also uses up all of its resources
available. Stopping and starting ColdFusion releases all of the connections to
the database, which in turn releases all of the memory taken and resolves the
problem momentarily.

Phil

[Duane McGuire]

Thank you for your quick reply and reference to the technote.

It's not entirely clear that this applies to MX, but the I did enjoy the
strong language recommending against the use of MS Access for production
systems!

If anyone has experience with the problem I've described in an MX
environement, I'm surely interested.

[paross1]

By the way, make sure that Maintain Database Connections is unchecked in your advanced data source settings, as this is strongly recommended for Access data sources.

Phil

[JMGibson3]

Look in your Windows temp folder (or wherever, on our NT 4.0 machine they wound
up in c:\WINT40) for JETxxx.tmp files. Typically everytime an Access DB is
opened a pair (or more) of these is created. I never did find out where you
can specify how many are allowed, but on our machine as we approached 130 of
them we'd get the "unspecified error". Solution (in addition to unchecking
Maintain which didn't really help us) was to use the Connection Timeout
parameter. Set it to a low value, a minute or so. This tends (not perfectly
by any means) to have the system remove idle JETxxx files. Unchecking Maintain
might disable the connection but wouldn't actually (for us anyway) remove the
JETxxx's.

[Duane McGuire]

Thank you for your response. Interestingly enough, it appears that our
problem is related to a lack of activity. I checked our DEV server, where the
issue pops up, and found just 4 of the JETxxx you describe. On the other
hand, our production server has 96 of those guys. We have not experienced an
issue on our production server.

Some time ago, I put a scheduled process to work on our dev server, hitting an
MS Access datasource every 5 minutes. The script was designed to send an
email to me if the connection failed. (So I could attempt to define: What
changed?) During the 5 weeks that the scheduled script was running no failures
of access datasources occurred. However, after the "monitor" was disabled, a
failure did occur. A reasonable conclusion is, that the "monitor" kept the
datasources from failing.

I'm asking questions here, because this unexplained behavior makes me nervous.

If anyone has additional insights, I'm interested! Thank you all.

[ksmith]

Please tell us a little more about the access datasources in development. Are
the mdb files stored on a local drive or on a network drive. Are other
applications outside of CFMX using these files? Does anyone edit these files
directly in MSAcess itself? Have you disabled the maintain connections for
your access datasources?

Also, if these are older access files you should back them up. Then open the
ODBC dsn you have in windows and have the datasource refresh the file. Finally
if these are old access files you should probably import the data into a
current version of access. This will allow your Access2000 file to be
recreated as a AccessXP or 2003 file.

[Duane McGuire]

Q Are the mdb files stored on a local drive or on a network drive?
A Local drive

Q Are other applications outside of CFMX using these files?
A No

Q Does anyone edit these files directly in MSAcess itself?
A Yes

Q Have you disabled the maintain connections for your access datasources?
A Yes

All of these database files have been imported into the current MS Access
version.

Thank you.

[ksmith]

Access is a single user desktop database. It will create a locking file once
someone connects to it - databasename.ldb. Perhaps the database is locked by
an access user. Look for a lock file in the database directory and delete it.