access 2007 and cfmx7

[khang]

Hi All,

We're getting request to convert our access databases to 2007 so I am trying find out if cfmx7 supports access 2007 databases. Can anyone help? I found a post in the forum saying that cfmx8 supports access 2007 but no mention about 2007.

[Guest]

CFMX 7 pre-dates Access 2007 so it would not have been tested. However, ColdFusion just uses the existing ODBC driver to ultimately connect to Access. The regular "MS Access" datasources use the Sequelink JDBC-ODBC bridge to get to the existing access driver. The 'Unicode' datasources use a JDBC-OLEDB bridge to use the same driver.

So, I would suggest saving an Access 2007 file to the coldfusion machine and test it. You may need to update your access odbc driver. You may also need to setup the ODBC datasource using windows control panel. Then create an ODBC socket DSN in coldfusion.

[khang]

Hi ksmith,

Thank for reply. I tried the following:

1. Start ODBC Data Source administrator.
2. Select System DSN and Add Microsoft Access Driver (*.mdb).
3. Data Source Name: Test
4. Database: test.accdb (this is my test 2007 access database).
5. Start cfmx 7 admin.
6. Add new data source: name testdb and Driver = ODBC Socket
7. For ODBC DSN, I selected the 'test' and submit.

I got the following error after click on submit:

Connection verification failed for data source: testdb
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.
The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.

Do you know what I did wrong?

[Guest]

Test your odbc dsn outside coldfusion. You can use msquery, sqlcon32 or another odbc query tool. If it gives same error, you access odbc driver needs to be updated.

[JR__Bob__Dobbs-qSBHQ2]

Open the Data Sources (ODBC) section of administrative tools

Create a System DSN using the Microsoft Access Driver (*.mdb,*.accdb) that points your *.accdb file, note that Access 2007 uses a different ODBC driver DLL (ACEOBDC.DLL) than previous versions.

In the CFIDE create an ODBC socket datasource, note that if you did not password protect your database you will need to check "Trusted Connection". If you have password protected the file use "x" (or another string) since ODBC requires both a username and password if a password is used

[khang]

ksmith,

You said to upgrade access database driver, do you mean Microsoft Jet 4.0 Database Engine? I ran coldfusion on win2003 sp2 and it already has the latest driver.

JR "Bob" Dobbs,

When adding the ODBC, I don't have option for Microsoft Access Driver (*.mdb,*.accdb), just Microsoft Access Driver (*.mdb). How do I add Microsoft Access Driver (*.accdb) extension to the list?

I searched microsoft website and found "2007 Office System Driver: Data Connectivity Components". Is this the update that I should apply on the server?

Thanks for your help.

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

khang,

On my machine in the Data Sources (ODBC) setup I used the driver labeled "Microsoft Access Driver (*.mdb,*.accdb)". I have Office 2007 installed on my development PC and I assume the necessary DLL is included with Office.

I took a look at the 2007 Office System Driver: Data Connectivity Components download page you mentioned. I assume that downoload contains the necessary DLL and I suggest you install it on a development machine. Please post your results to the forum.


http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94...

[khang]

Hi JR "Bob" Dobbs,

I downloaded and installed the 2007 Office System Driver: Data Connectivity Components and I could select "Microsoft Access Driver (*.mdb,*.accdb)". Thanks for the help.