Linked Server

[gbaughma]

I have a linked server to an access database (sorry... no choice on this one).... and some ASP pages that need to make queries to the linked server.

The linked server is working fine; in the SQL Management Studio I can bounce a query (Such as SELECT * FROM AccessDB...Project) and that works fine.

However, I'm missing a permission somewhere, because when I make my connection from an ASP page, I'm getting

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
....
.... It is already opened exclusively by another user, or you need permission to view its data.".

Obviously, it's not a locking issue if I can do the query from within SQL Management Studio, so I need to set a permission somewhere to allow the remote requests.

Help, anyone? TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

Is the Access file on another machine, or on the SQL Server's drive?

Is the web app logging in with a SQL Account, or a Windows account?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gbaughma]

The access DB is on another machine. I think I have it figured out... I just needed to not use a DSN connection for the whole works, and use a SQL connection string. Then things started working.

Meanwhile, are there any tips to "speed up" the queries against the linked access database?

I have found that if I create a query in Access, and call it as a view in ASP through SQL, it's very fast... but if I just do a standard query of the data through SQL to the linked db, it's dog slow....



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

No, sorry. Try checking in the Access forum, they may have more experience with that sort of thing. I haven't actually used access since the 2003 version.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog