Read-Only Linked table?

[wtrimble]

I have a co-worker than has an excel sheet that needs to be inputted in my database so I created a linked table to his excel sheet.

My problem is, he keeps the excel file open 24/7 out of necessity, and when he has it open, Access won't allow me to open up a form with a query based off the table on it because " i don't have permission and the file is locked" I believe.

Is there a way around this??

Thanks for any help!

 

[wtrimble]

Anyone have this problem where a linked -table can't be opened in Access if the user has it open??

 

[The_Doc_Man]

EVERYONE has this problem.

Linked tables in Access can be shared. In EXCEL, I don't think so.

Talk to this person and say, "Don't do that." Or find another way to get there from here.

 

[darwin25]

I'm not sure this will work but you can try using the FileCopy statement in VBA to make a copy of the target excel file and use the copy for your linked table. The only caveat is that you should make sure that the original excel file is saved before copying it so you always have the latest data when you do FileCopy. Ask your co-worker to at least put his excel file on timed auto-save.

 

[gemma-the-husky]

the other solution of course, is to "access-ize" the excel data, so you BOTH use access, and dispense with the excel sheet.

 

[wtrimble]

Yes good ideas. I might just have him use Access to make it easier for everyone. I'll look into the FileCopy statement also.

Thanks guys.