SQL Connection String for Microsoft Access 2010 .Accdb

Linked table ms access 2010 change connection string

To print all connection strings:

Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next

To create a linked table:

With CurrentDb
    ''If the table does not have a unique index, you will need to create one
    ''if you wish to update.
    Set tdf = .CreateTableDef("LocalName")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.SourceTableName = "TABLE_NAME"
    .TableDefs.Append tdf
    .TableDefs.Refresh
End With

To change a link:

    Set db = CurrentDB
    Set tdf = db.TableDefs("MyTable")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.RefreshLink

C# - unable to connect to a local ACCDB file with new SqlConnection()

You are making a common mistake here. The classes defined in the namespace System.Data.SqlClient (SqlConnection, SqlCommand etc.) are able to talk only with a Sql Server database system (Full, Express or LocalDb). They cannot work with an Access database.

For this database you should use the classes in the System.Data.OleDb namespace (OleDbConnection, OleDbCommand etc.) These classes understand the connectionstring to reach an Access database and can open and work with it.

So your code should be:

....
using System.Data.OleDb;

private void search_db(object sender, EventArgs e)
{
    using (OleDbConnection conn = new OleDbConnection())
    {
        conn.ConnectionString = ......
        conn.Open();
        string cmdText = @"SELECT * 
                           FROM Customers 
                           WHERE ([name] LIKE @q1) OR (EGN LIKE @q2)";
        using(OleDbCommand command = new OleDbCommand(cmdText, conn))
        {
            command.Parameters.Add("@q1", OleDbType.VarWChar).Value = search_query;
            command.Parameters.Add("@q2", OleDbType.VarWChar).Value = search_query;
            using (OleDbDataReader reader = command.ExecuteReader())
            {
               ....
            }
        }
    }
}

An importat thing to remember with OleDb is that you have positional parameters. The parameters are not recognized by their name but by their position in the query text. So if you have two parameters placeholder, even if they are for the same value, you need to put two parameters in the collection. One for the first placeholder and one for the second placeholder. This is even more important when you have placeholders for different values. You need to add the values in the exact order in which they are expected in the query text.

Connection String of MS Access database

If you know the path exactly you can use

con = new OleDbConnection
       (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source
             =\dtinaurdsna02\LE-IN\Admin\Quality
                Rating\Quality_Rating_Tool\Quality_Rating_Tool.accdb;
                Jet OLEDB:Database Password=xxxxxxx;
                 Persist Security Info=True;");

If database is within the app folder and you can use below

string path = Environment.CurrentDirectory;
      path = path + "\\Quality_Rating_Tool.accdb;";
   con = new OleDbConnection
           (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source" + path);

How do I open an old Microsoft Access adp file with invalid connection strings?

Well, I am going to close this question. Issue still exists (as such), but we are simply deleting the adp file now and build a replacement from scratch. I have now tried everything I can come to think of, including renaming the file extension to .mdb or .accdb, and unplugging the pc from any network connection while opening.

Every attempt ends in the same: Access does not respond, and I have to force-kill the application. I don't even know if the issue is what I suspect: the connection string being invalid due to a change of server names. But I can't think of any other explanation.

pyodbc and ms access 2010 connection error

Ok, sorry to answer my own question, but by playing around, I learned that you need to specify the absolute path name if you use the second connection string:

ACCESS_DATABASE_FILE = 'C:\\path\\to\\PSA_TEST.accdb'
ODBC_CONN_STR = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' % ACCESS_DATABASE_FILE

Then it even works with the accdb file, as well as with the mdb file as expected.

---

Related Topics