Getting MS Access Database working on Windows 2012 R2

After upgrading our Windows 2003 server to Windows Server 2012 R2, many clients have problem connecting MS Access database from ASP scripts. This was fixed by installing 64 Bit version of Access Database Engine (AccessDatabaseEngine_x64.exe) available for download from

https://www.microsoft.com/en-us/download/details.aspx?id=13255

You are only allowed to install 32 bit or 64 bit version, not both. MS Access with work with both of the versions. If you install 32 Bit version, you have to create a new Application Pool, Enable 32 Bit Application on this new Application pool, then set the site to use this new App Pool. It is better to install 64 Bit version as it work with Default 64 Bit Application Pool.

odbc_64bit

Driver string for MS Access is

Microsoft Access Driver (*.mdb, *.accdb)

One problem i found is many of the customer MS Access database do not work, this is because there are multiple versions of MS Access database, On Windows Server 2012 R2, you need specific versions of MS Access Database. To get OLD MS Access database working, these databases need to be saved in Win 2012 Server supported version of MS Access.

odbc_ms_access

Supported MS Access formats are Version 4.x and Version 12.x

Here is sample ASP Script to Create Table employee and insert some sample data

create_table.asp

<%

Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open  "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" &  Server.MapPath("../data/testing.mdb")

Dim strSQL
Dim rsResult
Set rsResult = Server.CreateObject("ADODB.Recordset")

strSQL = "create table employee ( emp_id number,emp_name varchar(20),emp_address varchar(30) );"
rsResult.Open strSQL, adoCon

strSQL = "insert into employee values(001,'Sam','Kochi');"
rsResult.Open strSQL, adoCon

strSQL = "insert into employee values(002,'Ram','Delhi');"
rsResult.Open strSQL, adoCon

rsResult.Close
adoCon.Close
Set rsResult = Nothing
Set adoCon = Nothing

%>

read_data.asp

<%

Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open  "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" &  Server.MapPath("../data/testing.mdb")

Dim strSQL
Dim rsResult
Set rsResult = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM employee;"
rsResult.Open strSQL, adoCon

Do While not rsResult.EOF
    Response.Write ("<br>")
    Response.Write (rsResult("emp_name"))
    Response.Write ("<br>")
    Response.Write (rsResult("emp_address"))
    Response.Write ("<br>")
    rsResult.MoveNext
Loop

rsResult.Close
adoCon.Close
Set rsResult = Nothing
Set adoCon = Nothing

%>

You need to place “testing.mdb” in “data” folder outside “wwwroot” folder.


Posted in Windows