none
Using a linked server in Visual Studio

    Question

  • I have created a link in SQL-Server to a MS Access database using the following:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a93d810-0927-46b3-9dde-bd122b0db68c/linking-microsoft-access-databases-to-sql-2016?forum=sqlsetupandupgrade

    This was done because I was becoming frustrated with our Application Server's inability to connect to an MS Access database, no matter what I used  as a Connection string in Web.config.

    The SQL-Server to MS Access link works well, but only when I run SQL SMS using the setting 'Run As Administrator'. Once I was there, I can execute my SELECT statements and create a VIEW using the Linked server.

    For example: 

    SELECT *
      FROM [PHONES]...[PhoneCodes]
    GO

    This comes up with a table from the MS Access database.

    This brings us back to our ASP.NET server. I can't get the darn thing to run and I am sure this is a rights issue.

    Web.config:

    <configuration>  
        <connectionStrings>
            <add name="Connection1" connectionString="Data Source=[server name];Initial Catalog=StudentDevice;Persist Security Info=True;User ID=[user id];Password=[password]"
                providerName="System.Data.SqlClient" />
          <add name="Connection2" connectionString="Data Source=[server name];Initial Catalog=StudentDevice;Persist Security Info=True;Integrated Security=SSPI"
              providerName="System.Data.SqlClient" />
        </connectionStrings>
    </configuration>

    Default.aspx.cs:

                try
                {
                    // Let's try using the linked Access database that we just created
                    string strConnString = ConfigurationManager.ConnectionStrings["Connection1"].ConnectionString;
    //                string strConnString = ConfigurationManager.ConnectionStrings["Connection2"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(strConnString))
                    {
                        string subQuery = "SELECT * FROM tblView1";
                        con.Open();
                        using (SqlCommand command = new SqlCommand(subQuery, con))
                        {
                            SqlDataReader reader;
                            reader = command.ExecuteReader();
                            if (reader.Read())
                            {
                                HttpContext.Current.Response.Write(reader["USEC"].ToString());
                            }
                            reader.Close();
    
                        }
                        con.Close();
                    }
                }
                catch (SystemException ex)
                {
                    // Something went wrong with this server.
                    HttpContext.Current.Response.Write("error [Linked DB] : " + ex.Message);
                }

    The result with Connection1 results in this error:

    error [Linked DB] : Login failed for user 'DOMAIN\MACHINE_NAME$'

    The result with Connection2 results in this error:

    error [Linked DB] : Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES".

    Can anyone offer any help?

    Thank you!

    Wednesday, April 26, 2017 8:48 PM

Answers

  • Hi Redant J,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about the ASP.NET Data Access development. Since our forum is to discuss the VS IDE, please redirect to this appropriate forum: SQL Server, SQL Server Express, and SQL Compact Edition and start a new thread for your issue, you will get a more professional support from there, thank you for your understanding.

    Meanwhile, you can have a look at this similar issue: Connection String for MS Access database and have a try with the following connection string:

    conn = new OleDbConnection ("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\Access_db\wing.mdb");

    Best regards,

    Sara


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Redant J Thursday, April 27, 2017 12:00 PM
    Thursday, April 27, 2017 7:47 AM
    Moderator

All replies

  • Hi Redant J,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about the ASP.NET Data Access development. Since our forum is to discuss the VS IDE, please redirect to this appropriate forum: SQL Server, SQL Server Express, and SQL Compact Edition and start a new thread for your issue, you will get a more professional support from there, thank you for your understanding.

    Meanwhile, you can have a look at this similar issue: Connection String for MS Access database and have a try with the following connection string:

    conn = new OleDbConnection ("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\Access_db\wing.mdb");

    Best regards,

    Sara


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Redant J Thursday, April 27, 2017 12:00 PM
    Thursday, April 27, 2017 7:47 AM
    Moderator
  • Done.

    I wish there was an option that I can select that says this discussion has moved.

    I will mark it as answered.

    Thank you Sara.

    Thursday, April 27, 2017 12:00 PM