[ACCEPTED]-Query Microsoft Access MDB Database using LINQ and C#-ms-access

What you want is a LINQ to ODBC provider, or 3 a LINQ to JET/OLEDB provider.

Out of the 2 box, MS doesn't make one. There may be a 1 3rd party who does.

Actually I recently (today) discovered that 14 you can access an Access database with LinqToSql. It 13 must be in the 2002 or newer format, you 12 will not be able to drag and drop the tables 11 to your datacontext so either manually create 10 the objects in your dbml or you can use 9 SQL Server Migration for Access to move 8 it to a sql server and then drag and drop 7 all you want. When you want to actually 6 create the context pass it an OleDbConnection. Use 5 your standard Jet.OLEDB.4.0 connection string 4 on the OleDbConnection and you are good 3 to go. Not sure of the limitation this 2 may incurr though. I just did a quick sample 1 and did an OrderBy without issue.

I wrote a small sample program to test this 5 out with David's answer. You'll need to 4 make an access database and manually create 3 the DBML for Linq-to-SQL, as you cannot 2 drag 'n drop them.

Inserts fail, citing Missing semicolon (;) at end of SQL statement. but 1 queries seem to work alright.

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using Linq2Access.Data;

namespace Linq2Access
{
    class Program
    {
        static readonly string AppPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
        static readonly string DbPath = Path.Combine(AppPath, "Data", "database.accdb");
        static readonly string DbConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + DbPath + "';Persist Security Info=False;";

        static void Main(string[] args)
        {
            if (!File.Exists(DbPath))
                throw new Exception("Database file does not exist!");

            using (OleDbConnection connection = new OleDbConnection(DbConnString))
            using (DataRepositoryDataContext db = new DataRepositoryDataContext(connection))
            {
                List<dbProject> projects = new List<dbProject>();
                for (int i = 1; i <= 10; i++)
                {
                    dbProject p = new dbProject() { Title = "Project #" + i };
                    for (int j = 1; j <= 10; j++)
                    {
                        dbTask t = new dbTask() { Title = "Task #" + (i * j) };
                        p.dbTasks.Add(t);
                    }
                    projects.Add(p);
                }

                try
                {
                    //This will fail to submit
                    db.dbProjects.InsertAllOnSubmit(projects);
                    db.SubmitChanges();
                    Console.WriteLine("Write succeeded! {0} projects, {1} tasks inserted",
                                        projects.Count,
                                        projects.Sum(x => x.dbTasks.Count));
                }
                catch(Exception ex)
                {
                    Console.WriteLine("Write FAILED. Details:");
                    Console.WriteLine(ex);
                    Console.WriteLine();
                }

                try
                {
                    //However, if you create the items manually in Access they seem to query fine
                    var projectsFromDb = db.dbProjects.Where(x => x.Title.Contains("#1"))
                                                        .OrderBy(x => x.ProjectID)
                                                        .ToList();

                    Console.WriteLine("Query succeeded! {0} Projects, {1} Tasks",
                                        projectsFromDb.Count,
                                        projectsFromDb.Sum(x => x.dbTasks.Count));
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Query FAILED. Details:");
                    Console.WriteLine(ex);
                    Console.WriteLine();
                }

                Console.WriteLine();
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
        }
    }
}

You can use a DataSet. There are linq extensions 3 that will allow you to query the data with 2 all that LINQ goodness we have become use 1 to :)

eICATDataSet.ICSWSbuDataTable tbl = new eICATDataSet.ICSWSbuDataTable();

ICSWSbuTableAdapter ta = new ICSWSbuTableAdapter();
ta.Fill(tbl);

var res = tbl.Select(x => x.ProcedureDate.Year == 2010);

I have seen this question a lot and in several 59 fora. I made a go at it and here is a complete 58 answer for those who have been looking at 57 it.

LinQ was not made for Access. However, many 56 of the queries will work with Access, including 55 delete procedure. So, according to me, there 54 are only 2 crucial deficiencies when working 53 with Access, which are:

1. not being able to save data.
2. not being able to drag and drop objects onto the dbml

Insert will fail 52 with the error "missing semicolon (;)". This 51 is because LinQ save procedure was made 50 to save data and retrieve the primary key 49 ID of the record saved in one go. We know 48 that you cannot execute multiple SQL statements 47 in Access, so that is the reason for that 46 failure.

Update will fail with the error 45 "record not found". An update 44 procedure will of cause look for the record 43 to be updated then update it. I cannot tell 42 why it wouldn't find it, when normal LinQ 41 query to find a record works fine.

Because 40 there is so much benefit to use LinQ, I 39 figured out how to work around the deficiency, while 38 enjoy the other benefits throughout my application. This 37 is how (NB: My codes are in VB.net, but 36 you can convert if required):

Create the 35 LinQ to SQL (.dbml) class to manage your 34 LinQ against the access database, and a 33 way to manager your save procedure. Below 32 is the full procedures of what I created 31 and I now work with LinQ to Access without 30 any problems:

Add a DataGridView on a form. Add buttons 29 for Add, Edit & Delete

Code to fill the 28 grid:

Private Sub ResetForm()

    Try

        Using db As New AccessDataClassesDataContext(ACCCon)

            Dim rows = (From row In db.AccountTypes
                        Where row.AccountTypeID > 1
                        Order By row.AccountTypeID Ascending
                        Select row).ToList()
            Me.DataGridView1.DataSource = rows

        End Using

    Catch ex As Exception
        MessageBox.Show("Error: " & vbCr & ex.ToString, "Data Error", MessageBoxButtons.OK)
    End Try

End Sub

DetailForm

Code to set control values

Private 27 Sub ResetForm()

    Try

        If _accountTypeID = 0 Then
            Exit Sub
        End If


        Using db As New AccessDataClassesDataContext(ACCCon)

            'Dim rows = (From row In db.AccountTypes
            '            Where row.AccountTypeID = _accountTypeID
            '            Order By row.AccountTypeID Ascending
            '            Select row.AccountTypeID, row.AccountType, row.LastUpdated).ToList()
            Dim rows = (From row In db.AccountTypes
                        Where row.AccountTypeID = _accountTypeID
                        Select row).ToList()

            For Each s In rows

                Me.AccountTypeIDTextBox.Text = s.AccountTypeID
                Me.myGuidTextBox.Text = s.myGuid
                Me.AccountTypeTextBox.Text = s.AccountType
                Me.AcHeadIDTextBox.Text = s.AcHeadID
                Me.DescriptionTextBox.Text = s.Description
                Me.LastUpdatedDateTimePicker.Value = s.LastUpdated

            Next

        End Using

    Catch ex As Exception

    End Try

End Sub

LinQToSQLClass

You will have 26 to add the data objects to the dbml manually 25 since you cannot drag and drop when using 24 Access. Also note that you will have to 23 set all the properties of the fields correctly 22 in the properties windows. Several properties 21 are not set when you add the fields.

Code 20 to Save

Public Function SaveAccountType(Optional 19 ByVal type As String = "Close") As 18 Boolean

    Dim success As Boolean = False
    Dim row As New AccountType

    Using db As New AccessDataClassesDataContext(ACCCon)

        If _accountTypeID > 0 Then

            row = (From r In db.AccountTypes
                   Where r.AccountTypeID = _accountTypeID).ToList()(0)

            If String.IsNullOrEmpty(row.AccountTypeID) Then
                MessageBox.Show("Requested record not found", "Update Customer Error")
                Return success
            End If

        End If

        Try

            With row
                .myGuid = Me.myGuidTextBox.Text
                .AccountType = Me.AccountTypeTextBox.Text
                .Description = Me.DescriptionTextBox.Text
                .AcHeadID = Me.AcHeadIDTextBox.Text
                .LastUpdated = Date.Parse(Date.Now())
            End With


            If _accountTypeID = 0 Then db.AccountTypes.InsertOnSubmit(row)
            db.SubmitChanges()

            success = True

        Catch ex As Exception
            MessageBox.Show("Error saving to Customer: " & vbCr & ex.ToString, "Save Data Error")
        End Try

    End Using

    Return success

End Function

Now replace these two lines:

            If _accountTypeID = 0 Then db.AccountTypes.InsertOnSubmit(row)
            db.SubmitChanges()

with 17 something like this:

        Dim cmd As IDbCommand

        cmd = Me.Connection.CreateCommand()
        cmd.Transaction = Me.Transaction
        cmd.CommandText = query

        If myGuid.Trim.Length < 36 Then myGuid = UCase(System.Guid.NewGuid.ToString())
        cmd.Parameters.Add(New OleDbParameter("myGuid", row.myGuid))
        cmd.Parameters.Add(New OleDbParameter("AccountType", row.AccountType))
        cmd.Parameters.Add(New OleDbParameter("Description", row.Description))
        cmd.Parameters.Add(New OleDbParameter("AcHeadID", row.AcHeadID))
        cmd.Parameters.Add(New OleDbParameter("LastUpdated", Date.Now))
        If AccountTypeID > 0 Then cmd.Parameters.Add(New OleDbParameter("AccountTypeID", row.AccountTypeID))

        If Connection.State = ConnectionState.Closed Then Connection.Open()

        result = cmd.ExecuteNonQuery()

        cmd = Me.Connection.CreateCommand()
        cmd.Transaction = Me.Transaction
        cmd.CommandText = "SELECT @@IDENTITY"
        result = Convert.ToInt32(cmd.ExecuteScalar())

The last part of the 16 code above is what gets you the ID of the 15 record saved. Personally, I usually make 14 that an option, because I don't need it 13 in most of the cases, so I don't need to 12 add that overhead of fetching back data 11 every time a record is saved, I am happy 10 just to know a record was saved.

That is 9 the overhead added to LinQ, which causes 8 Insert to fail with Access. Is it really 7 necessary to have it? I don't think so.

You 6 may have noted that I normally put my Update 5 and Insert procedures together, so that 4 saves me time and has address both the Insert 3 & Update procedures in one go.

Code for 2 Delete:

Private Sub DelButton_Click(sender As Object, e As EventArgs) Handles DelButton.Click
    Using db As New AccessDataClassesDataContext(ACCCon)

        Dim AccountTypeID As Integer = Me.DataGridView1.CurrentRow.Cells(0).Value
        Dim row = From r In db.AccountTypes Where r.AccountTypeID = AccountTypeID

        For Each detail In row
            db.AccountTypes.DeleteOnSubmit(detail)
        Next

        Try
            db.SubmitChanges()
        Catch ex As Exception
            ' Provide for exceptions.
            MsgBox(ex)
        End Try

    End Using

End Sub

Now you can enjoy LinQ to Access! Happy 1 coding :)

LINQ to SQL only works for SQL Server databases. What 3 you need is the Microsoft Entity Framework. This 2 makes object oriented access to your mdb. From 1 this you can run LINQ queries.

http://msdn.microsoft.com/en-us/library/aa697427(vs.80).aspx