Member Avatar for Chandrakumar

I copied book1.xls into IISServer c:/inetpup/wwwroot

objConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" & Server.mappath("/book1.xls") &";ReadOnly= true ; UID=admin;"

Dim strSQL
excelfilename = "Book1.xls"

strSQL = "select * from " & excelfilename

Dim objRS
Set objRS = Server.CreateObject ("ADODB.RecordSet")
objRS.Open strSQL, objConn,3,3

When I execute the page

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.

Please help me

Regards
Chandrakumar

  • 3 Contributors
  • 4 Replies
  • 324 Views
  • 7 Years Discussion Span
  • Latest Post Latest Post by rajeev_cv_2012

Dani AI

Generated

A short, practical overlay for this thread (addresses the two errors shown by and the later bulk-import suggestion by ). The first error (ODBC driver does not support the requested properties) usually comes from asking ADO for a cursor/lock combination the Excel ODBC driver does not implement. The second error (Jet cannot open the file / opened exclusively) points to file-locking or NTFS permission problems (IIS process cannot open the workbook, or Excel has it open). was correct that this is Classic ASP/VBScript, not C/C++.

Key, proven fixes and checks:

  • Use an OLEDB provider instead of the old ODBC driver for more predictable behavior (for .xls use Jet; for .xlsx use the ACE provider).
  • Query a sheet or named range, not the workbook name. Example SQL: SELECT * FROM [Sheet1$].
  • Avoid unsupported ADO cursor/lock settings. Either use conn.Execute(sql) (returns a forward-only read-only recordset) or open the RecordSet with adOpenForwardOnly/adLockReadOnly (0,1).
  • Ensure the file is not open in Excel and grant the IIS application pool identity or IIS_IUSRS read (or read/write when needed) permission on the folder. If full trust is not possible, copy the uploaded file to a temp folder that the appcan access and open that copy.
  • On 64-bit servers Jet.OLEDB.4.0 is not available; either enable 32-bit mode for the app pool or install/use the ACE OLEDB provider.

Example Classic ASP snippet (use instead of the ODBC Driver approach shown earlier):

Dim conn, rs, sql
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/book1.xls") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
sql = "SELECT * FROM [Sheet1$]"
Set rs = conn.Execute(sql)
' iterate rs.Fields("ColumnName")...
rs.Close
conn.Close

For repeated or large imports prefer a server-side bulk strategy (SSIS, SqlBulkCopy or scheduled service) rather than opening Excel from ASP — that scales better and avoids many permission/locking pitfalls (the bulk-import pointer from is in the thread and worth reviewing).

Recommended Answers

All 4 Replies

Member Avatar for Narue

That's Visual Basic, not C or C++. Please direct your question to a more appropriate forum.

Member Avatar for Chandrakumar

I Upload Book1.xls in to IISServer machine c:\Inetpub\wwwroot

When I am Using this code in ASP

Dim objConn
Set objConn = Server.CreateObject ("ADODB.Connection")
'response.write Server.mappath("/")
'response.end
objConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" & Server.mappath("/book1.xls") &";ReadOnly= true ; UID=admin;"

Dim strSQL
excelfilename = "Book1.xls"

strSQL = "select * from " & excelfilename

Dim objRS
Set objRS = Server.CreateObject ("ADODB.RecordSet")
objRS.Open strSQL, objConn,2,3


Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file 'Book1'. It is already opened exclusively by another user, or you need permission to view its data.

Please help me.

Member Avatar for Narue

What part of "You're in the wrong forum" is difficult?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.