User-actuated Import of Excel data into SQL Server: distributed query fails from Access VBA
Posted on 2010-11-15
In a MS Access 2003 application with SQL Server 2000 back-end, users will periodically need
to actuate an import into SQL from an Excel 2003 file. I've been working with the Excel file as a
linked server. The stored procedures execute as expected from Query Analyzer but fail with a
generic ODBC error from Access VBA.
I can execute any other stored proc from Access except for the one that includes the linked
server. Access won't open any linked table, SQL view linked as ODBC table, or stored
procedure that involves the linked server in any way.
The Access user is configured as dbo, so I don't think this is a permissions issue...is it?
Any insight would be much appreciated!