Best way to do ORM with Access DB as datasource

Question

I've gone back and forth on this problem and can't seem to figure out the best way to do this.

Here's the situation:

• Access database (3rd party product) with data I need in it, from a good number of tables (18 tables)
• Ideally, need to try to get records into strongly-typed objects somehow so I can query around with LINQ
• LINQ to SQL classes don't support ODBC provider (this would have gotten me home free)
• I do NOT need to insert/update/delete. Only select/read.

I've toyed around with the idea of just exporting the tables to XML (it's not that much) but then I'm still faced with the problem of building the schema and generating the classes. Since it's an ODBC source there should be a way to ORM this, right?

How would you solve this?

Answer 1

You can do this using nHibernate, since it supports MS Access as a backend. Here are the details of using nHibernate with MS Access. It uses NHibernate.JetDriver.dll to access the Jet data engine (MS Access).

Just realize that MS Access isn't going to give you the same performance/support/etc as most other DB backends with an ORM.

Answer 2

I just answered my own question...

I can just establish the ODBC connection in Server Explorer, and drag the tables straight into a predefined DataSet and use a TableAdapter.Fill()

Answer 3

Hi The dll for using NHibernate to Acccess seems to be on sourceForge (just googling, not checking)
http://sourceforge.net/project/shownotes.php?release_id=460590
If you are just querying access, it might be worth defining views in a relationnal database This way you will have a solution for using a form of cache/snapshot later on(for example by converting your views into table that you refresh each hour/ 5min. etc depending on your expectations) if the performance degrade too much.