Linked Excel creating lock on database

  • Thread starter Thread starter Burnsie
  • Start date Start date
B

Burnsie

I have an application that uses two databases on a server (both a front-end
--yes, it should be on the client but isn't right now--and back-end) with the
back-end having a table linked to an Excel spreadsheet.

Here is my problem: The first person to open a form that uses data from the
Excel linked table will lock out anyone else trying to use data from the
Excel linked table. For instance, one form has a subform that reads data
from the Excel linked table. If another person tries to use the Excel linked
data, even if it is on another form or report, that data is not available.
Also, if the second user tries to reference the same form, the data in the
subform is completely missing. I am not talking blank fields but the subform
itself is completely blank.

It appears that the first person in puts a lock on all records and not just
the record using the Excel linked table. I have Access set to only lock on
the record being updated. And I don't think this is a problem because the
databases live on a server as I can create this problem by creating several
instances on my development box.

I understand that you can't update Excel data because of legal reasons. So,
could this be why I seem to get a lock on all records?

Thanks for any help or suggestions.

Burnsie
 
I believe this is inherit with Excel and not a direct issue with Access. In
other words, I don't think you can work around this. Remember Excel is not a
relational program. It works as if you had open the Excel directly. The next
person to open the file will get a read only message as it is already open.
I would recommend not using Excel and transfer the data to Access so that
you can work with it relationally.
 
I should have known better. Yes, I have already taken the step to import the
data into a normal Access table. I was just hoping to cut out that step.

Thanks,
Burnsie
 
Back
Top