MS Access and CFMX locking issues

I am looking to start an account with JodoHost within the next month or so. I am already lining customers up to make this switch. One customer has an issue that I'd like to get input from you on:

He runs a site that does about 8-12GB of transfer a month. It is written in CFMX and is driven by an Access Database. The DB is about 4.5MB.

The issue is that every so often (probably once every week or two) the site will no longer serve the database driven portions (which includes the menu'ing system on the site, as well as most pages). Essentially a thread lock has occurred between CF and Windows. In order to get the service back, I have to stop all IIS and CF services (usually with an end process on the ODBC CF services), and then restart them.

Has anyone seen this before and know of any fixes? I did notice that one post mentioned the "keep connections open" but I'm not sure if that applies here or not. I don't want to switch them to me until I can be sure that this will work, or find an alternative solution for them.

Thanks,
-Ben
 
This is very common and why we recommend people not use MS Access, it is just not an efficient database for websites that have much traffic.
 
That makes sense, and I'm glad to hear that it wasn't something I was doing wrong on the servers that I built.

So essentially, even with a 4.5MB Access DB, we are stuck with the site locking every so often, or not allowing a customer to make use of Access DB's after a certain amount of traffic?
 
If it were that easy, I would. But it is a CFMX driven site, and trying to get it to work 100% due to field changes has proven quite difficult.
 
what fields need to change?

There are only a few reserved table names and I have found using the MS Upsizing Wizard in MS Access works without much code change at all unless you have reserved table names.

on the acces subject, I've seen as small at a 318kb Access DB cause a lock. But the larger it gets the more chance it will happen and try to read/write at the same time.
 
I'd have to look at the code again, which I no longer have access to. I guess it is a moot point unless I get him to switch to me and I can start working on recoding the site with him. I think the main field issues were moving from a boolean "yes or no" to a "1 or 0" field. Perhaps that's all it takes is proper code there to get it working with the new code.

From a business perspective, it would make more sense to have him change his code first, and then move to me. Otherwise, I'm going to be "forced" into working on his code somewhat for free.
 
kujo2123,

My response isn't directly related to the "locking" issue, but you should still proceed with caution. One of my clients had a fairly large Access database that was causing problems on the CF server, so Jodo support kindly converted it to MSSQL (using DTS) and now the website is broken really bad & I'm catching alot of heat.

As a hosting reseller, I don't know how to correct MSSQL & CFMX errors. The webmaster is scrambling to correct the issues ASAP because his client is upset about the broken webpages. I will be posting a request for help in the MSSQL server portion of this forum today. Wish me luck. ---68shelby

P.S. The errors include:

ERROR MESSAGE: The Primary key for table "xxx" was not found in the datasource "xxx". This error may be caused by not specifying the table name in the format that the database server expects.

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword " "

Jodo recommended a database tool called Aqua Data and I am trying to figure out how to actually use it to connect to the MSSQL server. So far, no luck, but that's because I have no experience with DB's, so I'm a little out of my league right now.
 
kujo2123,


Jodo recommended a database tool called Aqua Data and I am trying to figure out how to actually use it to connect to the MSSQL server. So far, no luck, but that's because I have no experience with DB's, so I'm a little out of my league right now.

Also might want to try the free SQL Manager 2005 Lite for SQL Server at http://www.sqlmanager.net/en/products/mssql/manager

I find it easy to use and does pretty much everything that I really NEED done around a MSSQL DB.
 
Thanks Penhall.

Do you recommend the "full installation package" or the "executable-file only"? Remember, I'm not a DBA, so I need something for dummies. The original DB was Access and was converted to MSSQL. :D
 
Back
Top