Adobe Community: accdb with CF9 under Win2008 R2 64bit

accdb with CF9 under Win2008 R2 64bit

Jan 27, 2012 8:50 AM

Latest reply: drdagwood, Jul 27, 2012 6:30 PM

Replies 1 2 Previous Next

Currently Being Moderated

40. Owain North,

Mar 24, 2012 1:43 AM in reply to Didi

Report

A recommendation to change the database is NOT regarded as helpful

Dammit, that's me out then.

|
Mark as:
Currently Being Moderated

41. BKBK,

Mar 24, 2012 3:10 AM in reply to Didi

Report

Procedure 1:
SELECT qry from CF9 to the DS
UPDATE something in DS from CF9
UPDATE something else in the underlying table from within MS Access
SELECT qry from CF9 to the DS

Procedure 2:
SELECT qry from CF9 to the DS
UPDATE something in the underlying table from within MS Access
UPDATE something else in DS from CF9
SELECT qry from CF9 to the DS

Does these procedures work? That is, does step 4 reflect both changes made in steps 2 and 3?

|
Mark as:
Currently Being Moderated

42. HXTT Support,

Mar 24, 2012 5:17 AM in reply to BKBK

Report

>>JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb
>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)
You need lockType=ACCESS connection property, then HXTT Access and MS Access can see each other.
For instance, JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb?lockType=ACCESS

|
Mark as:
Currently Being Moderated

43. BKBK,

Mar 24, 2012 5:26 AM in reply to HXTT Support

Report

HXTT Support wrote:

>>JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb
>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)
You need lockType=ACCESS connection property, then HXTT Access and MS Access can see each other.
For instance, JDBC URL: jdbc:access:/C:/Users/BKBK/Documents/myDB.accdb?lockType=ACCESS
Wow! HXTT Support! What a privilege!

|
Mark as:
Currently Being Moderated

44. Didi,

Mar 24, 2012 7:59 AM in reply to Didi

Report

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

|
Mark as:
Currently Being Moderated

45. Owain North,

Mar 24, 2012 8:10 AM in reply to Didi

Report

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.

|
Mark as:
Currently Being Moderated

46. Didi,

Mar 24, 2012 8:12 AM in reply to Didi

Report

@ HXTT Support: are you still with us?

As instructed by BKBK I installed the driver-library to

"C:\ColdFusion\lib\Access_JDBC40.jar"

However, when I replace Access_JDBC40.jar with the newer version Access_JDBC41.jar that was included with the package I received from you, it does not work.

Do I need to configure a different Driver Class or JDBC URL ?

Well, Access_JDBC40.jar works fine for me, but I guess, 41 has some advantages?

-Didi

|
Mark as:
Currently Being Moderated

47. Didi,

Mar 24, 2012 8:28 AM in reply to Owain North

Report

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

|
Mark as:
Currently Being Moderated

48. HXTT Support,

Mar 24, 2012 5:24 PM in reply to Didi

Report

>Well, Access_JDBC40.jar works fine for me, but I guess, 41 has some advantages?
You cannot use Access_JDBC41.jar, since JDBC 41 API is for JDK1.7.X, and your ColdFusion is using JDK1.6.X. Both of Access_JDBC40.jar and Access_JDBC41.jar are released as v5.1 for different JDBC API standard.

|
Mark as:
Currently Being Moderated

49. HXTT Support,

Mar 24, 2012 5:40 PM in reply to Didi

Report

> 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.

|
Mark as:
Currently Being Moderated

50. Didi,

Mar 24, 2012 11:43 PM in reply to HXTT Support

Report

HXTT Support wrote:

You cannot use Access_JDBC41.jar, since JDBC 41 API is for JDK1.7.X, and your ColdFusion is using JDK1.6.X. Both of Access_JDBC40.jar and Access_JDBC41.jar are released as v5.1 for different JDBC API standard.
Thanx!!!
-Didi

|
Mark as:
Currently Being Moderated

51. Didi,

Mar 24, 2012 11:59 PM in reply to HXTT Support

Report

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

|
Mark as:
Currently Being Moderated

52. HXTT Support,

Mar 26, 2012 4:20 AM in reply to Didi

Report

>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.

|
Mark as:
Currently Being Moderated

53. itisdesign,

Jul 19, 2012 12:22 PM in reply to Didi

Report

Hi Didi,

For MSAccess in 64-bit CF, you need the 32-bit drivers.

Please follow these exact steps on the Win2008 R2 64bit CF9 machine:
1) Install the 32-bit AccessDatabaseEngine.exe. Note: If 64-bit AccessDatabaseEngine_x64.exe or any 64-bit MS Office Apps are already installed, then AccessDatabaseEngine.exe must be installed via the command line with the "/passive" argument (ex: AccessDatabaseEngine.exe /passive).
2) Create DSN via CF Admin and ignore this error: "Unable to update the NT registry. Variable DRIVERPATH is undefined."
3) Register a "System DSN" using same name as in Step 2, via 32-bit C:\Windows\SysWOW64\odbcad32.exe, and choose this driver: "Microsoft Access Driver (*.mdb, *.accdb)"

Thanks,
-Aaron

|
Mark as:
Currently Being Moderated

54. drdagwood,

Jul 22, 2012 7:20 AM in reply to itisdesign

Report

Aaron,
I followed your instructions above however I don't see the MDB db driver (at least that's what I named it when I created it in the SysWOW64) so how do I make the "MDB" driver show in my CF Administrator DSN dropdown?

Connection verification failed for data source: MBD
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Data source name not found and no default driver specified
The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Data source name not found and no default driver specified

Thanks,
Daren

|
Mark as:
Currently Being Moderated

55. itisdesign,

Jul 22, 2012 4:57 PM in reply to drdagwood

Report

Hi Daren,

Step 2 was: 'Register a "System DSN"' (not "User DSN"). And that drop down on "CF Admin > Data sources" displays drivers, not data sources. Here are the steps to correct the MSAccess/x64CF issue:

1) Open C:\Windows\SysWOW64\odbcad32.exe
2) Click the "User DSN" tab
3) Select your data source (which you've named "MDB") and click "Remove"
4) Click the "System DSN" tab
5) Important: If your "MDB" data source exists here as well, then select it and click "Remove"
6) While still on the "System DSN" tab, add the "MDB" data source

Basically, your data source must be deleted from SysWOW64's odbcad32.exe on both the User DSN tab and the System DSN tab. THEN, once it doesn't exist on either tab, it can be added on the System DSN tab.

Thanks,
-Aaron

|
Mark as:
Currently Being Moderated

56. drdagwood,

Jul 27, 2012 6:30 PM in reply to itisdesign

Report

Aaron,
I'm still having an issue...apparently I'm not smarter than the equipment I'm using. Here's what I did:

I opened the C:\Windows\SysWOW64\odbcad32.exe and removed my old DSN under the "User DSN" tab
Opened hte "System DSN"
DSN = MBD
System Database I selected "Database“ and mapped it to my MBD.mdb database
Opened CF Administrator
Under "Ädd New Data Source" I typed MDB
Under "Driver" selected "MS Access"
Clicked "Add"
In "Database File" I mapped to my MBD.mdb (still receive error) and received the following error: (Connection verification failed for data source: MBD
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]Unable to retrieve error message from backend ODBC Driver.
The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]Unable to retrieve error message from backend ODBC Driver.)

9a. In "System Database File" I mapped to my MBD.mdb (still receive error) and received the same error.
When I go to my CF pages that pull data from the database I receive the following error: [Macromedia][SequeLink JDBC Driver][SequeLink Server]Unable to retrieve error message from backend ODBC Driver.

Any idea what I’m doing wrong?

|
Mark as: