How to connect .net API to MS Access database

Question

Dear Experts : I have an existing MS Access database , and I have created a web API using Visual Studio 2019. I am trying to create an ADO.net entity data model or even Code first . The problem is that I cannot find in the data source when I create new data source , the oledb . Any idea how to connect to Access via API Thanks in-advance

Answer 1

Do you have the Access database engine installed?

I would install the Access database engine from here:

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

Make sure you choose the correct bit size. If you running the web server as x32 bits (the default), then install Access x32 (x86) version. If you are running your .net application as x64 bits, then install the x64 bit version of Access. Keep in mind that Visual Studio is a x32 bit application. So, if you choose "any" CPU, it will launch your application as x32 bits.

If you force your project to x64, then you need to have installed the x64 bit version of the Access database engine. Keep in mind that running as x64 bits will work for debug, and running and testing. However, while you can use the connection builders in VS, the final test connection will ALWAYS fail if you using the x64 bit version of Access. This is because VS is a x32 bit application. So, the test connection button will not work, and if you going to use the dataset designer (or now the newer version (entity frame work), then you best develop with the x32 bit version of Access.

If you just developing local, then not a problem, but most hosted web sites (in fact near all) are x64 bits). If you not using the IIS express local, then you have to force your project to x64 bits. And testing connections to the database will be a challenge (your code, or debug code will work, but actual test connections from VS will fail if your project is forced as x64 bits.