User-actuated Import of Excel data into SQL Server: distributed query fails from Access VBA

Posted on 2010-11-15
Medium Priority
Last Modified: 2012-05-10
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!
Question by:tcillcjjr
  • 4
  • 3

Expert Comment

ID: 34137469
You need to make sure that you have access to the excel file.

The user in Access will call the SP.  If dbo, no problems there.

the SP will used the link server, depending on your link server setting, the user used for opening the files will be a specific or impersonate user.

Try to log in Management studio whit the user used in Access and try to see if you have access to the link server.

You can specify a Windows user in the link server setting, select the radio bottom to "Be made using this security context"

if this don't help provide error message.


Author Comment

ID: 34138855
Thanks, Cboudroz.  I misspoke a little - the generic "ODBC call failed" error occurs when the sp is invoked from Access, but if I link to a view that includes the linked server and try to open that linked view, a different error is raised:

 Error when opening ODBC linked view which includes linked server
The Access application has several different users, all of whom have read-write privileges for the XLS file.  I've tried setting "Be made without using a security context" and "Be made using the login's current security context," and there was no difference.

Again, thanks!

Author Comment

ID: 34146547
I spent some time on the phone with the IT guy yesterday afternooon.  He created a Windows user just for this application, with all necessary privileges; no change.  We tried several different configurations.  Same set of error messages.  He even tried it with his own login as domain admin.

Can anyone out there help shed some light on this?  Should I abandon the linked server and try my luck with saved DTS instead?  My web-based SQL colleagues don't have anything good to say about saved DTS, so I'd been trying to avoid it if possible.  The only other approach I can think of would be to use triggers; are those any more predictable/less temperamental than DTS?

Thanks in advance!
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Expert Comment

ID: 34146682
DTS are good to import/export data in SQL server 2000.

In SQL 2005 and 2008 they have been replace by SSIS.

You can also used the OPENDATASOURCE or OPENROWSET

or Used ADO and SQLOLEDB

see this for more info:

but did you try to connect in Query analyzer and try a SELECT on the excel files?


if it work try to connect in Query Analyzer whit the same user has the one access use to connect to SQL.


Author Comment

ID: 34160256
This is spooky:  when I did SELECT * FROM EXCELLINK...[Customers$] yesterday, it worked.  I ran out of time before I could do any more testing.  Now SELECT fails from Query Analyzer and in the (previously functional) view that was based on it.  I can only see the data if I use OPENROWSET.

Here's the message from QA:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].

I've verified that the Excel file is not open.  Why would this fail now, when I've made no changes since the last time I opened it successfully??

Thanks in advance!

Accepted Solution

Cboudroz earned 1000 total points
ID: 34160484
Did  you used the same user to connect to query analyzer?

Did you used the same computer?

Did the files moved?

You clearly have Authentication problems, if the files is accessible by other user or application.  Try to copy it before to link it to a SQL server, make sure your the only one that can access it.

But anyway I never really like using link server and excel (excel is not a server).

Open row set should do a good job, or use DTS to import the excel file in a table.


Author Comment

ID: 34176172
Thanks, Cboudroz.  

As far as I know, I used the same user & same computer to connect to QA; I logged into the terminal server in the same way, anyhow.  The files were not moved between the time the linked server was created & the time the latest error started showing.

It would have been good to link the Excel data dynamically to the SQL db, but your reminder that Excel is not a server tipped the balance away from that strategy.  What I'm doing is opening some SELECT statements for the users to perform manual data cleanup before importing, and selecting straight from the spreadsheet would prevent some bloat.

Instead, I'm using a "temporary" table.  It's not temporary in the sense that the table is created & dropped as needed; its contents are appended & truncated as needed, but the name & structure remain.  Until I can tweak the saved DTS so that it runs reliably every time it's invoked, the spreadsheet is linked to the MS Access front end and is appended to the linked SQL temp table from the Access interface.  After cleanup, the import is done from the temp table, rather than the spreadsheet.

Don't mean to bog you down with details, but if anyone else should have a similar problem it might help to see the workaround.

Thanks again for pointing me in a useful direction.  The Excel import works now, and development of the rest of the system can get back on track.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question