1 Correct answer
OK. That was that then. Let's move on.
I do believe our hopes now lie with JDBC. Since ColdFusion is a Java application, finding a 32-bit and 64-bit JDBC driver for MS Access will help many developers.
My search brought me to HXTT's JDBC driver for MS Access. The only problem is, I couldn't tell whether their driver is for 32-bit or 64-bit Access. Could it (hopefully) be universal, automatically detecting the system?
(After the customary virus scan) I unpacked their access.zip file, copied the
...
60
Replies
Completely agree it's not always so easy, and I'm sure no developer would ever be the one to choose Access. Take this thread as an example though - how many hours have you burned trying to get it to work? It's Access 2010, so someone somewhere is still actively upgrading your Access database, presumably still thinking it's the right thing to do.
I appreciate it's not always easy, but Access is not made for web applications. It never will be, and every time you come to change/upgrade you'll go through this much pain and then some.
If you can, I'd look at moving away from it before you find support is pulled completely 
OK. That was that then. Let's move on.
I do believe our hopes now lie with JDBC. Since ColdFusion is a Java application, finding a 32-bit and 64-bit JDBC driver for MS Access will help many developers.
My search brought me to HXTT's JDBC driver for MS Access. The only problem is, I couldn't tell whether their driver is for 32-bit or 64-bit Access. Could it (hopefully) be universal, automatically detecting the system?
(After the customary virus scan) I unpacked their access.zip file, copied the essential file Access_JDBC40.JAR from /access/lib/ to ColdFusion's lib directory. That's all there is to installing the driver. I then restarted ColdFusion.
I went to the ColdFusion Administrator and configured a datasource as follows:
Data Source Name: myAccessDSN
Driver: Other
JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb
Driver Class: com.hxtt.sql.access.AccessDriver
Driver Name: com.hxtt.sql.access.AccessDriver
It worked flawlessly. But then again, though I installed everything you did, I am on 32-bit Access.
ok, I finally did it!
Fortunately, my server is running as a VM on ESX. So I took a snapshot, deinstalled the AccessDatabaseEngine_x64.exe and installed AccessDatabaseEngine.exe.
Then I configured in SysWOW64\odbacd32.exe a DSN with the v14.0 Provider.
I think this is exactly what you meant by .. ?
In any case, that error message also tells me that your ACCDB file may be 64-bit. Again, for the purposes of creating Microsoft Access DSN in ColdFusion, you will need to create a 32-bit System DSN using this 32-bit application: c:\windows\SysWOW64\odbacd32.exe.
Next: In CFadmin I added two datasources: one via odbc (movo2odbc), one directly with acces (movo2accdb):
Well, my data-coffin says:
Do you have this running under the same configuration ( CF9 under Win2008 R2 64bit ) ?
----------------------------
hi, we just crossed posting .. this one is done without reading the previous one. I will do so instantly ..
hi there
I would follow up on this thread since it perfectly documents the background.
Let's summarize: with http://www.hxtt.com/access.html and BKBK's support we now have a working environment.
One thing puzzles us:
The following steps work fine:
- SELECT qry from CF9 to the DS
- UPDATE DS from CF9
- SELECT qry from CF9 to the DS (this reflects the change made in the step before)
But this procedure does not work
- SELECT qry from CF9 to the DS
- UPDATE the underlying table from within MS Access
- SELECT qry from CF9 to the DS (this does NOT reflect the change made in the step before)
What we already tried:
- CFOBJECTCACHE does not help.
- Setting "" in CFadmin does not help.
- Disabling "Maintain Connections" in the Datasource does not help (see below).
Anybody a helpful idea? (A recommendation to change the database is NOT regarded as helpful 
)
-Didi
A recommendation to change the database is NOT regarded as helpful
Dammit, that's me out then.
BKBK:
good idea to isolate the problem! -Thanx for the input!
HXTT Support:
Even better - now it works - triple Thanx for the solution!
For those who recommend to change the DB (Owain, are you still there? 
😞
We made some performance tests and compared 'accdb plus HXTT' with SQL 2008 Express.
'accdb plus HXTT' is incredibly fast. Regarding our application you cannot measure a difference in response time ..
-Didi
When you have one user and all the files are already cached, maybe 
Wait for a second user to hit your site, it'll be like a DDOS.
Owain North wrote:
When you have one user and all the files are already cached, maybe
Wait for a second user to hit your site, it'll be like a DDOS.
I absolutely agree, that accdb is not intended for a huge multiuser environment.
However, ColdFusion is the only user.
Since more than a decade I am told to trash MDBs since they will not perform.
On the other hand I have applications with half a million hits per day that do not even twinkle between two requests. Otherwise students learning for exam would rub through my doormat
As soon as performance drops, I definitely change ..
Besides some legacy stuff I can't get rid off on the fast lane (we have applications running from the 90s), I somehow have started to regard this discussion about the lousy performing mdb as a kind of intellectual challenge
-Didi
> When you have one user and all the files are already cached, maybe
First, according to one customer's random access( which needn't cached for recycle) report for big file on CD-R, HXTT Access engine is faster than MS Access ODBC engine.
Secondly, on lockType=ACCESS mode, HXTT Access won't use cache, since MS Access and HXTT Access can't share cache for concurrent according to old MS Access lock mechanism. HXTT Access has a faster data format parser/loader, and can utilize existent index fully for most sql.
Thirdly, only without lockType=ACCESS mode, HXTT Access can use multi-level cache, for physical file, object query reslut, session, transaction. All core is smaller, and HXTT Access can run on mobile platform.
Forth, only with assigned delayecClose connection property, HXTT Access can choose to build temporary index for big table without existent index.
According to performance test, HXTT drivers is faster than most of popular databases.
HXTT Support wrote:
> When you have one user and all the files are already cached, maybe
First, according to one customer's random access( which needn't cached for recycle) report for big file on CD-R, HXTT Access engine is faster than MS Access ODBC engine.
Secondly, on lockType=ACCESS mode, HXTT Access won't use cache, since MS Access and HXTT Access can't share cache for concurrent according to old MS Access lock mechanism. HXTT Access has a faster data format parser/loader, and can utilize existent index fully for most sql.
Thirdly, only without lockType=ACCESS mode, HXTT Access can use multi-level cache, for physical file, object query reslut, session, transaction. All core is smaller, and HXTT Access can run on mobile platform.
Forth, only with assigned delayecClose connection property, HXTT Access can choose to build temporary index for big table without existent index.
According to performance test, HXTT drivers is faster than most of popular databases.
aha!
So,
jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb?lockType=ACCESS
is ok for a developer environment.
So, what configuration would you (HXTT Support, not you Owain 
) recommend for a productive installation?
jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb? ... delayedClose .. maxCacheSize .. lockTimeout.. etc. ???
Is this for CF9 still valid as for CF6.1 (as stated in your FAQ)?:
?delayedClose=15;maxCacheSize=6144;lockTimeout=2000
-Didi
PS: hey Owain, do not misunderstand me, it's not personal, just the challenge I mentioned above
>So, what configuration would you (HXTT Support, not you Owain ) recommend for a productive installation?
It's only a jdbc url demo. You need only lockType=ACCESS since you need MS Access concurrent access. You can add other connection property if you need some special feature, for instance, ODBCTrimBehavior or caseInsensitive.
AdChoices