I have a data connection in several Excel documents that share the same source file on a shared network. If one user opens the source file and another user attempts to refresh the data connection, the query fails and displays an Unrecognized database format. It will then try to open the source file as a Read-Only copy.
There has to be a way to designate that the source file in this data connection is to be Read Only, and that it shouldn't matter if somebody else has the source file open.
I have tried a whole slew of different options, including a "ReadOnly" statement in the Connection String, adding DISTINCT after the SELECTcommand, editing in Microsoft Query, etc. Does anybody know how to force the data connection to retrieve the source file as Read Only and pull in whichever data was last saved even if the source document is open somewhere?
My OBDC Connection String is:
DSN=Excel Files;DBQ="File Path";DefaultDir="Directory Path";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;
Repair your MS-Office to sort out this problem.
Hope this will help you out.