Question Excel file Linked to Access as tables gives Locking issues

[Kris_d]

Hello,

I have an Access Database where several excel workbooks are linked to MS access as Tables basically for the ease of use, when new data is fetched gets updated into access by Refresh.
The problem is when the database is open by another user , the excel sheet can not be updated. Is it possible to work on both of them simultaneously ?
I read through some of the posts which said importing was a better option but I do not want to go with that in this case as the database gets live feed from lot of those excels.
Please help with any relevant suggestions. Appreciate it.

Thanks

 

[Roku]

I have an Access Database where several excel workbooks are linked to MS access as Tables

• What mechanism do you use to link worksheets to Database tables?
• How many worksheets per workbook do you want to (or can) be linked?
• When the other user(s) open the DB, do they also have the same workbook(s) open?

Any other relevant information would help.

 

[Kris_d]

I am so sorry for this late reply , I was out of country for a transition.

To answer your questions :

-Mechanism to connect to DB tables : Link to the Data source by creating a Linked Table
- Due to some problems I faced I restricted it to One worksheet per workbook.
-Yes, and that is precisely the issue, I have the Access DB open and trying to run some query, but at the same time other users have the excel sheet open and they want to make changes on it.

I read a lot on Internet that its a bad idea to Link the tables, But still is there a workaround if Linking is the only option I have ?
Help will be much appreciated. Let me know if you have any questions !

 

[Kris_d]

Thank you for the reply Pat. I know even I want to go with option 2 but requirement is like that.

What exactly do you mean by deleting the link ? In access ? or you mean the excel file ?
Wouldn't that involve a lot of time in linking and deleting again and again.

Thanks

 

[Kris_d]

Deleting the Linked table is also not an option as there are a number of Calculated fields in the table and after I perform different operations on Table I use Refreshh All and it replicates my changes in the linked Excel.

I guess deleting would not work with this case.

I was now thinking to go with import and write a macro may be for exporting it every time.

 

[Kris_d]

I am sorry I think I made my statements unclear, that was not what I meant about importing lets forget that one.

Would you help me explain this : " it merely severs Access' connection to the spreadsheet so the lock is removed "
How can you achieve this ? do you have some steps ?

Thanks