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

Posted on 2010-11-15
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 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!
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Automation VBA 19 30
access 7 21
Sql Query with datetime 3 10
How autonumber field on a form 16 6
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now