Using ODC files always locks exclusive

[cerfingnow]

I have created a shared workbook for data entry. Other users nee
custom views of the data. I have opted to create a new workbook the
using Data->Import External Data->Import Data... to grab the main dat
range from the data file, and feed a pivot table view etc. The proble
is, whenever the import is "updated" the data file is locked exclusivel
disabling other users from saving to the main data file (even though i
is a shared doc).

I have created an ODC data source in "My Connections" and have modifie
the connection string to change Mode=Share Deny Write to Mode=Share Den
None which made a difference but still causes an error. I have als
tried Mode=Read for readonly access by my report view file. Still n
luck. Is there another setting in the connection string that I shoul
be modifying? Or perhaps a different OLE DB provider? (using OLE D
Jet 4 now).

Thanks in advance for any help

 

[Jamie Collins]

I have created a shared workbook for data entry. Other users need
custom views of the data. I have opted to create a new workbook then
using Data->Import External Data->Import Data... to grab the main data
range from the data file, and feed a pivot table view etc. The problem
is, whenever the import is "updated" the data file is locked exclusively
disabling other users from saving to the main data file (even though it
is a shared doc).

I have created an ODC data source in "My Connections" and have modified
the connection string to change Mode=Share Deny Write to Mode=Share Deny
None which made a difference but still causes an error. I have also
tried Mode=Read for readonly access by my report view file. Still no
luck. Is there another setting in the connection string that I should
be modifying? Or perhaps a different OLE DB provider? (using OLE DB
Jet 4 now).

Please clarify a couple of points.

What do you mean by 'whenever the import is "updated"'? Are you
refreshing the viewed data by re-running a query or are you amending
the data in the datasource somehow?

What do you mean by 'I have created an ODC data source'? Have you
created a DSN via control panel, administrative tools, data sources
(odbc)? In which case, aren't you using the odbc driver? You later say
you are using the OLE DB Provider for Jet 4.

Jamie.

--

 

[Jamie Collins]

FWIW I did a quick test using the OLE DB Provider for Jet 4.0 and ADO
in VBA code. I can successfully open multiple Connection objects to
Excel data sources if I set the Mode to 1 (adModeRead). If an open
Connection has the default Mode value of 16 (adModeShareDenyNone) the
second Connection fails to open with a provider error, 'Catastrophic
error.'

Jamie.

--