Connecting C# win32 app to Access Database

Question

I've been searching around to no avail for a supposedly simple connection to an (MS)Access database (.mdb).

I use Office 2007, Visual Studio 2010, it is a C# Winform application, on a x64 (64 bit) OS, with the app running in x86 (32 bit) mode.

I'm already able to easily access a SQL-server database but on (MS)Access I'm completely stuck. I’m trying to connect to a database that is placed in my Dropbox.

My connection code:

//(string SQL = "select * from Quickmem";)

    public static DataTable SelectALL(string SQL)
    {
        var appDataPath = Environment.GetFolderPath(
                               Environment.SpecialFolder.ApplicationData);
        var dbPath = System.IO.Path.Combine(appDataPath, "Dropbox\\host.db");

        var lines = System.IO.File.ReadAllLines(dbPath);
        var dbBase64Text = Convert.FromBase64String(lines[1]);
        string folderPath = System.Text.ASCIIEncoding.ASCII.GetString(dbBase64Text);

// Actual path (which he correctly finds): C:\Users\<username>\Dropbox
        folderPath += "\\Agenda.mdb";

        string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + folderPath;

        string strAccessSelect = SQL;

        DataSet myDataSet = new DataSet();
        OleDbConnection myAccessConn = null;

        myAccessConn = new OleDbConnection(strAccessConn);


        OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

        myAccessConn.Open();


        myDataAdapter.Fill(myDataSet, "QuickMem");


        myAccessConn.Close();

        DataTableCollection dta = myDataSet.Tables;
        DataTable DT = dta[0];

        return DT;
    }

The current exception I’m getting is

“System.Data.OleDb.OleDbException occurred Message=Data type mismatch in criteria expression. Source=Microsoft Office Access Database Engine ErrorCode=-2147217913 StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) InnerException: .”

In my experimenting, I’ve come across many issues all breaking on the same line:

myDataAdapter.Fill(myDataSet, "QuickMem");

Quickmem being a table in my database.

Any help or input will be appreciated.

Please provide the entire stack trace, not just the message. — Arran
– Arran, Commented May 15, 2013 at 8:26
Please show the text of your sql command (strAccessSelect or SQL string) — Steve
– Steve, Commented May 15, 2013 at 8:31
OK, just finished installing MS Access on my machine, will play with it now... Also, why is your file saved as a .MDB when 2007 saves it as a .accdb — Dave
– Dave, Commented May 15, 2013 at 9:23
I used this page from MSDN: msdn.microsoft.com/en-us/library/aa288452%28v=vs.71%29.aspx, So I manually saved it (using Save As) as MDB (2002/2003 DB) to avoid complications, as it doesn't matter anyway for the DB itself. — SpiritBH
– SpiritBH, Commented May 15, 2013 at 9:29

Dave · Accepted Answer · 2013-05-15 09:51:32Z

3

I made the following console app using a near identical copy of your code (I did comment out some lines which I've left for you to see)

using System;
using System.Data;
using System.Data.OleDb;

namespace AccessDb
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = SelectALL("SELECT * FROM QuickMem");
            Console.WriteLine("Done");
            Console.ReadKey();
        }

        public static DataTable SelectALL(string SQL)
        {
            //var appDataPath = Environment.GetFolderPath(
                                   Environment.SpecialFolder.ApplicationData);
            //var dbPath = System.IO.Path.Combine(appDataPath, "Dropbox\\host.db");    
            //var lines = System.IO.File.ReadAllLines(dbPath);
            //var dbBase64Text = Convert.FromBase64String(lines[1]);
            //string folderPath = System.Text.ASCIIEncoding.ASCII.GetString(dbBase64Text);

            string folderPath = "C:\\Users\\drook\\Documents\\Database1.accdb";    
            string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + folderPath;    
            string strAccessSelect = SQL;    
            DataSet myDataSet = new DataSet();
            OleDbConnection myAccessConn = null;    
            myAccessConn = new OleDbConnection(strAccessConn);    
            OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

            myAccessConn.Open();    
            myDataAdapter.Fill(myDataSet, "QuickMem");    
            myAccessConn.Close();

            DataTableCollection dta = myDataSet.Tables;
            return dta[0];    
        }
    }
}

It compiles fine. It runs fine. There are no errors. Please note, I did comment some lines as I can't replicate them.

Therefore, the fault is either with your code before you even get to the database (where you are creating the folderPath) but since you've said you've now hard coded, it means it is unlikely.

The fault is either with the database itself (and please note I'm using the .accdb file extension, not the .mdb extension and also I only have 2 fields, ID and Text fields) or a configuration with your machine.

I'm sorry, this answer is probably better as a comment, but I can't place this much detail as such.

EDIT

Based upon your comments, please create a brand new WinForm and use this code - it will execute on load.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
        DatabaseCheckTest();
    }

    private void DatabaseCheckTest()
    {
        DataTable dt = SelectALL("SELECT * FROM QuickMem");
        MessageBox.Show("All done");
    }

    public DataTable SelectALL(string SQL)
    {
        string folderPath = "C:\\Users\\drook\\Documents\\Database1.accdb";    
        string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + folderPath;    
        string strAccessSelect = SQL;    
        DataSet myDataSet = new DataSet();
        OleDbConnection myAccessConn = null;    
        myAccessConn = new OleDbConnection(strAccessConn);    
        OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

        myAccessConn.Open();    
        myDataAdapter.Fill(myDataSet, "QuickMem");    
        myAccessConn.Close();

        DataTableCollection dta = myDataSet.Tables;
        return dta[0];    
    }
}

edited May 15, 2013 at 9:51

answered May 15, 2013 at 9:33

Dave

8,50213 gold badges69 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SpiritBH Over a year ago

Tried implimenting in what I already had, Didn't work, I'll try recreating your code.... Confirmed your code works, even when linking to Dropbox.

Dave Over a year ago

When you say it works, do you mean it works using the hard coded location of the database or the dynamically created version

SpiritBH Over a year ago

Both, In fact, the console app works PERFECT, but EXACTLY the same code, does not work in my win32 app.

Dave Over a year ago

Hold on, will fire up a WinForm... And it works fine. I'm sorry, the fault you have is more complex. Please create a new WinForm and paste the code in my new edit

SpiritBH Over a year ago

it indeed does work, thanks, it must then be somewhere else. thats good enough for now, I'll find the bugger ;)

|

Collectives™ on Stack Overflow

Connecting C# win32 app to Access Database

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related