Solved

Can't utlize Excel Driver on Windows Server 2003

Posted on 2006-11-06
9
711 Views
Last Modified: 2010-08-05
I want to access an Excel workbook on a network share and display it's data in an ASP.NET datagrid.  
I set up a DSN using the Microsoft Excel Driver(*.xls) and got this working on my dev machine.  It was my understanding that this driver would allow an app to read the spreadsheet without having Excel actually installed on the server.  However I get the following error on the server.  I've compared the dev DSN to the server and don't see any diffs.  I can drill down to the location of the Excel file on the server so the network share is properly mapped on it.

ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides..

Please help.
0
Comment
Question by:bozworthy
  • 5
  • 2
9 Comments
 
LVL 1

Author Comment

by:bozworthy
ID: 17884857
I also changed the location of the file so that the driver letter is NOT used in the path.  I'm getting this error now:
ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'.  It is already opened exclusively by another user, or you need permission to view its data.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'.  It is already opened exclusively by another user, or you need permission to view its data..

The driver is setup for readonly.  Do file permissions need to be set somewhere?
0
 
LVL 51

Expert Comment

by:Netman66
ID: 17885632
Are you setting this a a User DSN or System DSN?

0
 
LVL 1

Author Comment

by:bozworthy
ID: 17889117
It is a system DSN.
0
 
LVL 51

Expert Comment

by:Netman66
ID: 17891038
You may also want to set itup as a user DSN since ASP uses the ASP.NET account for credentials.

0
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.

 
LVL 1

Author Comment

by:bozworthy
ID: 17891714
hopefully we're getting closer here:

Ok here's what I know:
1) I can sign on to the server as an admin and create DSNs either system or user

Here's what I do NOT know:
1) The user account that ASP.NET uses
2) How to specify any kind of user information in the setup for a DSN using an Excel Driver.  This setup only cares about
the location of the file.

How do setup a user DSN to make this work.

0
 
LVL 1

Author Comment

by:bozworthy
ID: 17891754
I created a user DSN and deleted the System one.  Now the app says it can't find that DSN name.  The spelling is correct.

I see that the ASPNET account is a member of Administrators and Users.
0
 
LVL 1

Author Comment

by:bozworthy
ID: 17907961
Here's what I ended up using.  The only stipulation is that the Excel file had to be on the server.  I could not get this to work through the network share.  Obviously an issue with permission which I'll look into.  But the good news is this works without a DSN and without having Office installed on the server:

         Imports System.Data

                Dim ds As New DataSet()              
                Dim cn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & myfile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

                cn.Open()                
                Dim sql As String = "SELECT * from [Sheet1$];"
                Dim da As New Data.OleDb.OleDbDataAdapter
                da = New Data.OleDb.OleDbDataAdapter(sql, cn)
                da.Fill(ds)            
                Me.dgrid.DataSource = ds
                Me.dgrid.DataBind()
                Me.dgrid.Visible = True
                cn.Close()

0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18119227
PAQed with points refunded (125)

Computer101
EE Admin
0

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

Suggested Solutions

I have never ceased to be amazed how many problems you can encounter on a fresh install of a Windows operating system.  This is certainly case in point& Unable to complete ANY MSI installation.  This means Windows Updates are failing and I can't …
I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 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

11 Experts available now in Live!

Get 1:1 Help Now