Search code examples
asp-classic32bit-64bitadodbwindows-server-2012

Microsoft Access Database Engine 2010 Redistributable 64 bit only works if Enable 32-bit set to true

I have an ASP classic application that I'm migrating from a Windows 2000 to Windows 2012 Server.

It uses the following code to import xls files

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; Excel 8.0; DBQ=" & Server.MapPath("\Imports\") &"\"&fn& "; "

and the following for csv files

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoCSVFile & ";Extended Properties=""text;HDR=YES;FMT=Delimited"""

As per the answer on this question Excel ODBC and 64 bit server I downloaded and installed the Microsoft Access Database Engine 2010 Redistributable.

I've tried both the 32bit and the 64bit version of the above but neither of them work unless I change the application pool setting Enable 32-bit applications to True. If it is set to False I get the below error.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Unfortunately setting it the True breaks PDF rendering in another part of that website.

The only viable option I have come up with so far is to set this part up under it's own application pool that is 32-bit.

How can I run this in 64-bit Application Pool?

EDIT: And before someone comes along with a dupe hammer, a related question that does not resolve the issue is ASP running in 64 bits environment with Access database

Solution

  • First, make sure you install the 64-bit package AccessDatabaseEngine_x64.exe successfully.

    After that, use the following connection strings.
    You should no longer have problems working with your 64-bit application pool.

    'Excel 97-2003
Set Connection = Server.CreateObject("ADODB.Connection")
    Connection.Open _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\my\path\to\tables.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
Set Recordset = Connection.Execute("Select * From [Sheet1$]")

'CSV / TXT
Connection.Open _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\my\path\to\csv_dir\;Extended Properties=""text;HDR=YES;FMT=Delimited"";"
Set Recordset = Connection.Execute("Select * From table.csv")

    connectionstrings.com has always been a good reference for connection strings.