Solved

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

Posted on 2010-11-15
7
560 Views
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!
0
Comment
Question by:tcillcjjr
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:Cboudroz
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.



 
link-server.jpg
0
 

Author Comment

by:tcillcjjr
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!
0
 

Author Comment

by:tcillcjjr
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!
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 7

Expert Comment

by:Cboudroz
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:
http://support.microsoft.com/kb/321686



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

SELECT * FROM EXCELLINK...[Customers$]

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



 
0
 

Author Comment

by:tcillcjjr
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!
0
 
LVL 7

Accepted Solution

by:
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.


0
 

Author Comment

by:tcillcjjr
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 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

20 Experts available now in Live!

Get 1:1 Help Now