Search code examples
asp.netconnectionodbcweb-config

ODBC connection to ACCDB in web.config

Want to connect to ACCDB file as an ODBC (rather than OleDb) data source in ASP.NET but struggling with the connection string. As I plan to deploy the web app from Visual Studio 2019 to Azure, I need something that can make the journey.

Here's what I tried from connectionstrings.com (I discovered not all of those strings are up to date or necessarily convenient for insertion to web.config):

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;Pwd=;

This returns keyword not supported: driver from IIS Express where I'm doing development.

Solution

  • Hum, that looks ok:

    I would suggest this:

    Driver={Microsoft Access Driver (*.mdb, *.accdb)};
dbq=C:\test\test44.accdb;driverid=25;fil=MS Access;
maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;
threads=3;uid=admin;usercommitsync=Yes

    the above is what the connection builder in Visual Studio spits out. and you have Dbq - carefull - caps I believe matters - so try dbq.

    Next up: HUGE HUGE elephant in the room.

    are you using a x32 bit driver, or the x64 bit one? YOU MUST match your project bit size (code compile) settings to the driver (YOU MUST!!!).

    So this:

    enter image description here

    you MUST set and force your project to run as x32 bits. (or perhaps install the x64 bit version of Access drivers. AND BE careful. If you are running the x64 bit versions of the drivers, and use Test connection with Visual Studio? It will always fail since VS runs as a x32 bit applcation. you MUST RUN the applcation to test the connection - NOT use test connection with VS connection builders (you might not be using them, but if you are, just trying to save you a day of work wasted).

    so, create a x32 config project. And thus this now:

    enter image description here

    And I assume your sample connection using c: or "root" of the drive is a JUST a example, since most windows now don't allow a file to be used directly from c: root location.