[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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!
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

656 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