Can I use LINQPad on MS Access data directly, or by passing args to a Web API Rest method?

Question

I want to test out the LINQ in my Repository code:

public IEnumerable<InventoryItem> Get(string ID, string packSize, int CountToFetch)
{
    return inventoryItems.Where(i => 0 < String.Compare(i.Id, ID)).Where(i => 0 < String.Compare(i.PackSize.ToString(), packSize)).Take(CountToFetch);
}

...and am trying to use LINQPad to do that.

The data comes from an MS Access database; seeing that LINQPad doesn't seem to support Access "out of the box" that way, I wonder if either there's a driver for that (couldn't fine one - don't have the DevExpress product needed to use their driver), or if I could query by attaching to my Web API Rest method inside LINQPad?

I tried the SQL Server driver, hoping that would work for Access, too; I tried the WCF driver, hoping maybe that would work with Web API, but neither worked; with the latter attempt, I got, "XmlException: Data at the root level is invalid. Line 1, position 1."

Emmittemmons

Answer 1

There is a 'Microsoft Access Data Context Driver' for LinqPad

You can download it here: MSAccessDataContextDriver.lpx

View a screenshot here: LinqPad Northwind.accdb

UPDATE (5 Nov 2014) The driver now supports password protected MS Access databases

Roshelle

I downloaded that; it's an "lpx" file; I don't know what that is; my machine didn't want to open it, also didn't know what it is. – Emmittemmons

Clay, download the .lpx-file, in LinqPad, click on Add connection, then click on 'view more drivers...' then click on 'browse' and select the .lpx-file the driver is added on the top list-box – Roshelle

Note: For anyone trying to use the .lpx file: Please make sure you're not using the 64bit (AnyCPU) version of LinqPad. Otherwise you'll get the strange error message "Error: InvalidOperationException - The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." The 32bit Version of LinqPad works fine with it. – Autochthon

lpx download link is dead. – Ursula

Answer 2

In addition to the answers given above, you can also do the following (which works not only for Access, but also for MySQL, Oracle, PostgreSQL, and a couple of other database systems):

1. Click "Add connection" in LinqPad
2. The "Choose Data Context" dialog opens
3. Click the button View more drivers...
4. Scroll down a bit until you see "LINQ to DB Driver"
5. Click on the link button "Download & Enable Driver".
6. Wait until you see "Driver successfully updated." Then click OK.
7. You should see the "Choose Data Context" dialog again, but this time with an additional entry "LINQ to DB". Select it and Click Next >
8. The "LINQ to DB Connection" dialog opens. As Data Provider select "Microsoft Access"
9. Enter a connection string^*) like for example Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\MYUSER\Documents\myAccessDB.accdb;Persist Security Info=False
10. Verify the connection by clicking Test. It should give you a "Successful!" message.
11. Close the dialog by clicking OK

Now you're ready using your Access database.

---

_{^*)Hint: To create a valid connection string, you can use the following trick:
Create a new, empty text file on the desktop and give it the extension .udl, for example Connection.udl. Then, double click on it and you will get a configuration dialog where you can select a provider, enter server, database etc. as required. Confirm it with OK and open the file Connection.udl with Notepad afterwards. You will see a valid connection string that you can copy and paste (copy just the line Provider=...).}

Autochthon

If you get error 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine try switching to 64-bit (AnyCPU) LINQPad or vice versa to the 32-bit version. The installed provider will likely depend on your Office version. See https://mcmap.net/q/64379/-39-microsoft-ace-oledb-12-0-39-provider-is-not-registered-on-the-local-machine. – Ursula

@HughW - That's a good hint. Luckily, LinqPad does ship with 32 and with 64 bit so you can use the right version matching to your Office version (32 or 64 bit). – Autochthon

Answer 3

Take a look at this LinqPad blog about how one person managed to connect and query MS Access.

how-to-connect-to-and-query-a-ms-access-database-mdb-and-accdb

One other possible solution is to use XPO persistent classes to connect to MS Access.

This topic describes how to install the driver and use it to query XPO persistent classes

LinqPad Data Context Drivers

There also might be a way to use the IQ Drivers. I am not sure if connections to MS Access have been added.

Hope this helps. Regards.

Gamaliel