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

so have msaccess database couple linked tables point sql server database via dsn username , password built in.

in .net app need connect access database, , run queries on linked tables. have tried oledb , odbc methods, neither seem work linked tables point sql server, linked tables pointing other access tables fine. error such as: odbc--- connection 'thisdsn' failed.

is there way connect access database can run queries , doesn't matter if linked table sql server or access table?

what describe can done, need check of details in setup.

i have following access database local table [expenses] , linked table [dbo_accountcodes]

i have saved query named [expensedetails] pulls data [expenses] , uses join retrieve related [accountdescription]:

select expenses.*, dbo_accountcodes.accountdescription expenses inner join dbo_accountcodes on expenses.accountid = dbo_accountcodes.accountid; 

i can run saved access query c# application using 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 * expensedetails", conn);     oledbdatareader rdr = cmd.executereader();     int rowcount = 0;     while (rdr.read())     {         rowcount++;         console.writeline("row " + rowcount.tostring() + ":");         (int = 0; < rdr.fieldcount; i++)         {             string colname = rdr.getname(i);             console.writeline("  " + colname + ": " + rdr[colname].tostring());         }     }     rdr.close();     conn.close();      console.writeline("done.");     console.readkey(); } 

when run it, get:

row 1:   id: 1   userid: dr.evil   expensedescription: "laser"   expenseamount: 1000000   accountid: 101   accountdescription: weapons of world domination done. 

so can work. things check are:

1. are using system dsn linked table? depending on how c# code being executed may not able "see" dsn if of other type.

2. if asp.net application make sure process under code runs has required credentials access sql server database. said dsn has "a username , password built in" make sure .connect string linked table explicitly says trusted_connection=no;. try re-creating table link in access , selecting "save password" option in "link tables" dialog. if decide try using windows authentication on sql server may have add sql logins on server , sql users on database nt authority\system and/or nt authority\network service and/or yourdomain\youriisservername$, depending on setup.