Has anyone ever seen this error

[Jatrix]

We just launched a vaccation tracking app with a coldfusion front end and an access 97 backend (dont ask). The app was tested for over a month and passed all tests. However we never tested for volume, or amount of users hitting the app. On the backend, we have an access table linked to a text file. Again, this tested out perfectly over the past month. This morning after release to the entire company of about 2,000 people, users began reporting this error:

The text file specification 'EmployeeList Link Specification6' does not exist. You cannot import, export, or link using the specification.


Some users logged in to the app successfully and some recieved this error. For those that recieved the error, we had them back out and re-login and then the app worked just fine.

Has anyone ever seen this or have a guess as to what is going on?

[Newsgroup_User]

Jatrix wrote:
>
> Has anyone ever seen this
>

NO

>
>have a guess as to what is going on?
>

MS Access, being a desktop application, is not designed or supported as
an enterprise, server based, multiple concurrent user application. You
then compound this limitation by tying it to a file. I suspect under
load somewhere between the file IO and Access read and writes, limits
are being reached. Then errors are thrown.

Access can be used, even for high use web applications. But its
limitations must be understood and mitigated.

[Jatrix]

yes, that is what I was affraid of.

Thank you Ian.

[fober1]

Hi,
The right way would be to use a "real" database server.

But you can test if db-caching can improve the issue.
With each <cfquery> you can set a cachedwithin parameter. Using that should allow you to query the Access db once, and then have CF keep the query results in memory (for the DB as well as the text-file).
You could query the complete database with no filtering this way, and then filter the data using QueryofQuery to output only records for specific users/groups/regions.

You also need to make sure the settings in CF admin are configured right for this.

The more often the database content is updated, the slower this solution will get though.

cheers,
fober

ps. for the cachedwithin parameter in the query set a timeframe of forever, so the recordset never gets updated, and then actually execute a new query to update the recordset at the end of your "save a vacation" or "updated a vacation" functions. This way the app would only read the db when a real change to the records occurred.
But like I said with 2000 users, even that may not be enough to get the app performing.