Solved

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

Posted on 2010-11-15
7
570 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
[X]
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
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

691 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