SQLConnection.Open(); throwing exception

Question

Updating an old piece of software but in order to maintain backward compatibility I need to connect to a .mdb (access) database.

I am using the following connection but keep getting an exception, why?

I have validated the path, database existence etc. and that is all correct.

            string Server = "localhost";
            string Database = drive + "\\btc2\\state\\states.mdb";
            string Username = "";
            string Password = "Lhotse";

            string ConnectionString = "Data Source = " + Server + ";" +
                                      "Initial Catalog = " + Database + ";" + 
                                      "User Id = '';" + 
                                      "Password = " + Password + ";";

            SqlConnection SQLConnection = new SqlConnection();

            try
            {
                SQLConnection.ConnectionString = ConnectionString;
                SQLConnection.Open();
            }
            catch (Exception Ex)
            {
                // Try to close the connection
                if (SQLConnection != null)
                    SQLConnection.Dispose();

                //
                //can't connect
                //

                // Stop here
                return false;
            } 

The exception message is:

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

Answer 1

You need to add a data provider to your connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

Answer 2

What exactly makes you think that a SQL Server connection (SqlConnection) will be willing to connect to an MDB Access database???

If you connect to Access, use OleDbConnection. For Access connection strings, see http://www.connectionstrings.com/access

Answer 3

try this more safer version using the using clause...as for the Database variable, it would be better and safer to use the System.IO.Path.Combine method...:

            string Server = "localhost";
            string Database = System.IO.Path.Combine(@"C:\", @"\btc2\state\states.mdb");

            string Username = "";
            string Password = "Lhotse";

            string ConnectionString = "Data Source = " + Server + ";" +
                                      "Initial Catalog = " + Database + ";" + 
                                      "User Id = '';" + 
                                      "Password = " + Password + ";";
            bool bDatabaseOk = false;
            using (SqlConnection SQLConnection = new SqlConnection()){

                try
                {
                    SQLConnection.ConnectionString = ConnectionString;
                    SQLConnection.Open();
                    bDatabaseOk = true;
                }
                catch (SqlException Ex)
                {
                   // Handle the SqlException here....
                   //
                   //can't connect
                    bDatabaseOk = false;
                }
            } 
            return bDatabaseOk;