2

I need to migrate a bunch of data from an Access database to a MySQL server. I wrote a migration in ColdFusion, but when I went to create the ODBC connection on the CF Administrator, I got an error. The exact error is:

Connection verification failed for data source: cpep_access
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: The specified DSN contains an architecture mismatch between the Driver and Application
The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: The specified DSN contains an architecture mismatch between the Driver and Application

When I tried to create the ODBC connection in the Windows ODBC management tool, I found that I didn't have the driver. I assume that the driver not being there is the root cause for the CF Admin error.

I am running Windows Server 2008 R2 x64, and I was wondering if anyone could point me to the ODBC driver for Windows?

Dave Long
  • 349
  • 1
  • 4
  • 15
  • I tried installing the Microsoft Data Access Components, but they are only available for x86 machines. – Dave Long Apr 20 '11 at 17:52
  • Would it be feasible to install a MySQL ODBC driver, and add the MySQL tables as linked tables in the Access database? Then you can move the data over from within Access (and if you're running the 32-bit version, it will use the 32-bit MySQL ODBC driver). – db2 Apr 20 '11 at 19:18
  • There's an old program called AccessDump that was made for this job. I don't believe it is supported any longer but you may be able to find an old copy somewhere. – John Gardeniers Apr 20 '11 at 21:30

1 Answers1

2

From the Data Access Technologies Road Map

Microsoft Jet Database Engine 4.0: Starting with version 2.6, MDAC no longer contains Jet components. In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC/WDAC releases do not contain Microsoft Jet, the Microsoft Jet OLE DB Provider, the ODBC Desktop Database Drivers, or Jet Data Access Objects (DAO). The Microsoft Jet Database Engine 4.0 components entered a state of functional deprecation and sustained engineering, and have not received feature level enhancements since becoming a part of Microsoft Windows in Windows 2000.

There is no 64-bit version of the Jet Database Engine, the Jet OLEDB Driver, the Jet ODBC Drivers, or Jet DAO available. This is also documented in KB article 957570. On 64-bit versions of Windows, 32-bit Jet runs under the Windows WOW64 subsystem. For more information on WOW64, see http://msdn.microsoft.com/en-us/library/aa384249(VS.85).aspx. Native 64-bit applications cannot communicate with the 32-bit Jet drivers running in WOW64.

Instead of Microsoft Jet, Microsoft recommends using Microsoft SQL Server Express Edition or Microsoft SQL Server Compact Edition when developing new, non-Microsoft Access applications requiring a relational data store. These new or converted Jet applications can continue to use Jet with the intention of using Microsoft Office 2003 and earlier files (.mdb and .xls) for non-primary data storage. However, for these applications, you should plan to migrate from Jet to the 2007 Office System Driver. You can download the 2007 Office System Driver, which allows you to read from and write to pre-existing files in either Office 2003 (.mdb and .xls) or the Office 2007 (*.accdb, *.xlsm, *.xlsx and *.xlsb) file formats

This means you can install the 32 bit versions but your applications must also be 32 bit to access them.

If you want to Configure the ODBC setting from the windows management tool you'll need to use the 32 bit version located at %systemdrive%\Windows\SysWoW64\Odbcad32.exe.

Conrad Frix
  • 136
  • 3
  • 1
    This is out of date. Jet was succeeded by the ACE, first released with Access 2007, and Access 2010 was released with both 32- and 64-bit versions. The ACE exists in both 32- and 64-bit versions, and thus nowadays you can use the 64-bit ACE to access Jet data (it's fully backward-compatible) from 64-bit applications. So the conclusion of this answer is basically WRONG because it depends on information that is 10 years out of date. – David W. Fenton Apr 26 '11 at 02:28
  • 1
    @David. That's good news. I'm always hate the "it can't be done" answers. If you post a link to the ODBC X64 drivers for windows server 2008 I'll happily delete my answer – Conrad Frix Apr 26 '11 at 14:29
  • I have no use for them, as I'm an Access developer and don't need them. There are plenty of links to the download in articles on this subject on Stackoverflow.com. – David W. Fenton Apr 29 '11 at 01:16
  • Umm, I don't think the answer to this is so clear cut as @DavidW.Fenton thinks. For example, I did try to find ODBC X64 drivers for Windows server 2008 just now on MSDN, and found this older non-answer http://social.technet.microsoft.com/Forums/zh/winserverTS/thread/3bcf51f0-28f9-42f4-b501-d90ef9b3ddd8 and this VERY recent, as in merely two weeks ago (not 10 years ago) non-answer http://social.technet.microsoft.com/Forums/en/winservergen/thread/7908ec12-93fd-41c4-ab8f-1ed08e5d5622 – Ellie Kesselman Apr 27 '12 at 19:01
  • thanks @Feral . The [Microsoft Access Database Engine 2010 Redistributable](http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255#overview) might actually work but I won't know until I try in front of an X64. – Conrad Frix Apr 27 '12 at 19:18
  • I found you here after that question on meta about EL&U, and lol'd at your comment (yes, genuine lol). As of 2010 there was no ODBC 64bit driver for windows for Access. I found another thread that refers specifically to Jet though (allllll the way toward the end) http://social.msdn.microsoft.com/Forums/en/netfx64bit/thread/dfbd0406-bc69-436e-998b-7fc454012f91 and it is funny as well. Possibly helpful... maybe. – Ellie Kesselman Apr 27 '12 at 19:20