SQL connection string for microsoft access 2010 .accdb

Question

I am doing a simple login form using winforms and access 2010 database (.accdb) in C#.

I have the following code and it seems that the connection string is wrong. I have tried searching and found that .Jet is for access 07?? but this doesnt seem to work too. i am an amateur at databases (code referred from msdn). I am having trouble understand which should i use for this example too.

access table name: haha

ID (PK)  |   password
-----------------------
   1     |   testing

        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BC207\test.accdb");
        System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();

        comm.CommandText = "SELECT HAHA(*) FROM password";
        comm.CommandType = CommandType.Text;
        comm.Connection = conn;

        conn.Open();

        Object returnValue = comm.ExecuteScalar();
        conn.Close();

        MessageBox.Show((string)returnValue);

edited: the table's name is password, and the field that i want to get the value is ID.

SQL statement i wrote it as : SELECT ID FROM password

and yes, only one record in only one field in the table as the primary key.

anyway the problem is that the program hangs upon execution on the first line
-> Keyword not supported: 'provider'.

so i figured that I have a wrong connection string..

score 9 · Answer 1 · answered Oct 14 '11 at 08:23

9

For Acces databases (.mdb, .accdb, etc...), you want to use OleDbConnection, not SqlConnection (SQL Server), like this:

conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BC207\test.accdb")

answered Oct 14 '11 at 08:23

Simon Mourier

132,049
21
248
298

I don't think the connection type in this case matters. It is SQL Server that the command will be parsed to reguardless... – MoonKnight Oct 14 '11 at 08:32
1

This is the only correct answer so far. @Killercam: `SqlConnection` works with SQL Server only, and here SQL Server is not involved at all, so your comment is wrong. `SqlConnection` doesn't work with MS Access! – Christian Specht Oct 14 '11 at 08:45
1

@Killercam Where do you get that idea? – Fionnuala Oct 14 '11 at 08:52
OK. But the OleDbConnection object represents a unique connection to a data source - fine, but in this case this is equivalent to a network connection to the server that is the native OLE DB provider (in this case SQL Server). This is what I thought was the case? That is, when using the OleDbProvider, this is a sub-class of the SqlConnection? – MoonKnight Oct 14 '11 at 09:58
2

@Killercam - OleDbConnection opens ... an OLE DB Connection using an OLE DB provider. It does not presume anything in terms of network, or the underlying system. It is not in any way related to SQL Server (there is in fact an OLE DB provider for SQL Server but we prefer ADO.NET SqlConnection when coding in .NET). Access files (.mdb and alike) are accessible through the JET driver which is also an OLE DB Provider. That's why the connection string requires the Provider key. There is no ADO.NET Access provider. OleDbConnection is not a sub class of SqlConnection (OleDbProvider does not exists). – Simon Mourier Oct 14 '11 at 11:27
1

@Simon Mourier +1. Cheers for the explanation. – MoonKnight Oct 14 '11 at 12:03
@SimonMourier I ask a related Question here: http://stackoverflow.com/questions/14475968/read-access-file-accdb-from-stream , would you please check it – Saeid Jan 23 '13 at 09:28

Davide Piras · Answer 2 · 2011-10-14T11:48:21.410

Edit: as pointed out, for access OleDbConnection should be used, not SqlConnection...

you can use a much more compact way and also be sure connection is closed and disposed in any possible case even when exceptions are thrown, by using the using statements:

your query text was also, probably wrong as others have suggested...

using (var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BC207\test.accdb"))
using (var comm = conn.CreateCommand())
{
    comm.CommandText = "SELECT password FROM HAHA";
    comm.CommandType = CommandType.Text;

    conn.Open();

    var returnValue = comm.ExecuteScalar();

    MessageBox.Show(returnValue.ToString());
}

Edit: are you sure the table HAHA only contains one row? Because the ExecuteScalar returns only one value, if you want to get 1 column but from many records you could use a DataReader or a DataSet...

ah yes, my entire table only consists of one record in a single field, to store a general password for my program to refer to. I have since changed the table name to "password" and field name to "ID" which is also the primary key. — BOOnZ, Oct 14 '11 at 08:18

score 0 · Answer 3 · answered Oct 14 '11 at 08:06

0

comm.CommandText = "SELECT HAHA(*) FROM password";

It´s wrong.

"SELECT password FROM HAHA"

answered Oct 14 '11 at 08:06

mggSoft

992
2
20
35

ah yes, my SQL statement was wrong and I have since changed it. but the problem is on the first line of the code and not the sql statement(did not execute till that far yet haha) – BOOnZ Oct 14 '11 at 08:23

KV Prajapati · Answer 4 · 2011-10-14T08:40:14.117

0

Your SQL statement should be,

SELECT * from HAHA

OR

 SELECT [Password] From HAHA

EDIT:

You should change the ConnectionString.

edited Oct 14 '11 at 08:40

answered Oct 14 '11 at 08:07

KV Prajapati

93,659
19
148
186

ah yes, my SQL statement was wrong and I have since changed it. but the problem is on the first line of the code and not the sql statement(did not execute till that far yet haha) – BOOnZ Oct 14 '11 at 08:23
@user776914 - You have to set appropriate connection string. I've edit my post. – KV Prajapati Oct 14 '11 at 08:40

SQL connection string for microsoft access 2010 .accdb

4 Answers4

Linked