ADO: Read Excel file that is in use (locked)
 

Is it possible to read an Excel file with ADO if the file is allready opened
by someone(for editing)? I think that it should be possible to open the file
as read only but I keep getting the error:

The Microsoft Jet database engine cannot open the file ''. It is already
opened exclusively by another user, or you need permission to view its data.

I am running the (javascript asp) code below on an intranet server. The
server reads an Excel file on a file server to extract information.
Everything works fine unless some user has the file open.

I know that this error may occur when there are file permissions errors for
in IUSR_computerName user but I am pretty sure I do not have these problems.
The errors occur on the line below with conn.Open(). Is there any way I have
not used that I can instruct JET OLEDB to open the file as read only. (except
for conn.Mode = adModeRead that does not appear to do what I expect it to, to
be able to read an open file)

My code is as follows:

var resRS = Server.CreateObject("ADODB.Recordset");
resRS.CursorType = adOpenKeyset;
resRS.CursorLocation = adUseClient;
resRS.LockType = adLockReadOnly;

var conn = Server.CreateObject("ADODB.Connection");
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
connString += "\\\\OtherMachine\\Temp\\test.xls" + ";Extended
Properties='Excel 8.0;IMEX=1';";
conn.Mode = adModeRead;

// This line generates the error
conn.Open(connString);

var sql = "SELECT MyName, MyValue FROM [Summary$]";
resRS.Open(sql, conn);

Any help most appreciated. Maybe this is something that just cannot be done.

Best regards,

Andreas Stansvik
Software Developer

ADO: Read Excel file that is in use (locked)
 

If a user has the file open and is editing it, how can you know what you are
retrieving?
I think Excel is pretty agressive in its file locking to prevent
inconsistencies, although I know a second user can open a file readonly in
excel if someone has it open.
What about making a (temp) copy of the .xls and pointing your ado at that?
just a straight dos copy would do it. Or can your users access it read only?

cheers
Simon

"Stanza" wrote:

Is it possible to read an Excel file with ADO if the file is allready opened
by someone(for editing)? I think that it should be possible to open the file
as read only but I keep getting the error:

The Microsoft Jet database engine cannot open the file ''. It is already
opened exclusively by another user, or you need permission to view its data.

I am running the (javascript asp) code below on an intranet server. The
server reads an Excel file on a file server to extract information.
Everything works fine unless some user has the file open.

I know that this error may occur when there are file permissions errors for
in IUSR_computerName user but I am pretty sure I do not have these problems.
The errors occur on the line below with conn.Open(). Is there any way I have
not used that I can instruct JET OLEDB to open the file as read only. (except
for conn.Mode = adModeRead that does not appear to do what I expect it to, to
be able to read an open file)

My code is as follows:

var resRS = Server.CreateObject("ADODB.Recordset");
resRS.CursorType = adOpenKeyset;
resRS.CursorLocation = adUseClient;
resRS.LockType = adLockReadOnly;

var conn = Server.CreateObject("ADODB.Connection");
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
connString += "\\\\OtherMachine\\Temp\\test.xls" + ";Extended
Properties='Excel 8.0;IMEX=1';";
conn.Mode = adModeRead;

// This line generates the error
conn.Open(connString);

var sql = "SELECT MyName, MyValue FROM [Summary$]";
resRS.Open(sql, conn);

Any help most appreciated. Maybe this is something that just cannot be done.

Best regards,

Andreas Stansvik
Software Developer

ADO: Read Excel file that is in use (locked)
 

Thanks for quick answer. It is true that I can copy the file and provide a
workaround to the problem. I do however want to avoid this because I do not
think that it is a very elegant solution and I do not want to copy the pretty
large file due to performance of the application. If not anyone has a
solution to this problem that might however be the solution I will have to go
with. I would still be very interested in knowing if this is an impossible
operation to perform (reading the open file).

I think that it should be possible to read the file even when it is opened
for editing, it should retrieve the last saved data.

Thanks for answer Simon,

Andreas

"Simon Murphy" wrote:

If a user has the file open and is editing it, how can you know what you are
retrieving?
I think Excel is pretty agressive in its file locking to prevent
inconsistencies, although I know a second user can open a file readonly in
excel if someone has it open.
What about making a (temp) copy of the .xls and pointing your ado at that?
just a straight dos copy would do it. Or can your users access it read only?

cheers
Simon

"Stanza" wrote:

Is it possible to read an Excel file with ADO if the file is allready opened
by someone(for editing)? I think that it should be possible to open the file
as read only but I keep getting the error:

The Microsoft Jet database engine cannot open the file ''. It is already
opened exclusively by another user, or you need permission to view its data.

I am running the (javascript asp) code below on an intranet server. The
server reads an Excel file on a file server to extract information.
Everything works fine unless some user has the file open.

I know that this error may occur when there are file permissions errors for
in IUSR_computerName user but I am pretty sure I do not have these problems.
The errors occur on the line below with conn.Open(). Is there any way I have
not used that I can instruct JET OLEDB to open the file as read only. (except
for conn.Mode = adModeRead that does not appear to do what I expect it to, to
be able to read an open file)

My code is as follows:

var resRS = Server.CreateObject("ADODB.Recordset");
resRS.CursorType = adOpenKeyset;
resRS.CursorLocation = adUseClient;
resRS.LockType = adLockReadOnly;

var conn = Server.CreateObject("ADODB.Connection");
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
connString += "\\\\OtherMachine\\Temp\\test.xls" + ";Extended
Properties='Excel 8.0;IMEX=1';";
conn.Mode = adModeRead;

// This line generates the error
conn.Open(connString);

var sql = "SELECT MyName, MyValue FROM [Summary$]";
resRS.Open(sql, conn);

Any help most appreciated. Maybe this is something that just cannot be done.

Best regards,

Andreas Stansvik
Software Developer