ODBC connection to MS Access database with SQLServer linked tables via DSN

Question

So I have a msaccess database with a couple linked tables that point to an SQL Server database via DSN with a username and password built in.

In my .net app I need to connect to my access database, and run queries on the linked tables. I have tried with OLEDB and ODBC methods, neither seem to work for the linked tables that point to SQL Server, but linked tables pointing to other access tables are fine. I get an error such as: ODBC--- connection to 'THISDSN' failed.

Is there a way to connect to my access database so that I can simply run queries and it doesn't matter if the linked table is SQL server or just an access table?

Answer 1

What you describe can be done, you just need to check some of the details in your setup.

I have the following Access database with a local table [Expenses] and a linked table [dbo_AccountCodes]

I also have a saved query named [ExpenseDetails] that pulls the data from [Expenses] and uses a JOIN to retrieve the related [AccountDescription]:

SELECT Expenses.*, dbo_AccountCodes.AccountDescription
FROM Expenses INNER JOIN dbo_AccountCodes ON Expenses.AccountID = dbo_AccountCodes.AccountID;

I can run that saved Access query from my C# application using the normal OLEDB method:

static void Main(string[] args)
{
    var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\__tmp\accounting.accdb;");
    conn.Open();
    var cmd = new OleDbCommand("SELECT * FROM ExpenseDetails", conn);
    OleDbDataReader rdr = cmd.ExecuteReader();
    int rowCount = 0;
    while (rdr.Read())
    {
        rowCount++;
        Console.WriteLine("Row " + rowCount.ToString() + ":");
        for (int i = 0; i < rdr.FieldCount; i++)
        {
            string colName = rdr.GetName(i);
            Console.WriteLine("  " + colName + ": " + rdr[colName].ToString());
        }
    }
    rdr.Close();
    conn.Close();

    Console.WriteLine("Done.");
    Console.ReadKey();
}

When I run it, I get:

Row 1:
  ID: 1
  UserID: dr.evil
  ExpenseDescription: "Laser"
  ExpenseAmount: 1000000
  AccountID: 101
  AccountDescription: Weapons of World Domination
Done.

So it can work. Some things you could check are:

1. Are you using a System DSN for your linked table? Depending on how your C# code is being executed it may not be able to "see" the DSN if it is of some other type.

2. If this is an ASP.NET application then make sure that the process under which your code runs has the required credentials to access the SQL Server database. You said that your DSN has "a username and password built in" so make sure that the .Connect string for your linked table explicitly says Trusted_Connection=No;. You could also try re-creating the table link in Access and selecting the "Save password" option in the "Link Tables" dialog. If you decide to try using Windows Authentication on the SQL Server you may have to add SQL logins on the server and SQL users on the database for NT AUTHORITY\SYSTEM and/or NT AUTHORITY\NETWORK SERVICE and/or YourDomain\YourIisServerName$, depending on your setup.