Link to a worksheet as read-only

[barbola]

I have an access database with a table link to an excel worksheet that is almost always in use. Access won't let me run any queries on this table because it's in use.

If I cannnot eliminate the possibility that the user has the excel sheet open as exclusive, how can I get Access to use the data in the excel sheet as read-only?



Thanks!
Barb E.

 

[Blorf]

Hi.

Consider copying the Xls file to a temp xls file, and link to that. That way you can be sure it's not in use. Barring that, I have not found a solution. You can copy the file with VBA code.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[barbola]

Can I copy the file using VBA if the file is in use? Will updates to the file update the copy - prob not, eh?

I would have to update the copy every time I want to query it - is that what the code would do?


Thanks!
Barb E.

 

[Blorf]

You are right. If the file is in use, the filecopy command will fail.

Sorry,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[barbola]

I noticed that my report works if I open the Excel sheet as read-only. I will use VBA to open the file as read-only and then have it close.

Any code suggestions would be appreciated (I'm not lazy, just busy, and I know you are too).


Thanks!
Barb E.

 

[a98dsu98sad8sua]

fileSource = "X:\..\..\..\...Calculator.xls"
fileDestination = "X:\..\..\..\Data\Headcount.xls"
FileCopy fileSource, fileDestination

This works fine for me, even when it is in use

Look up FileSystemObject if you need another way

 

[barbola]

I will give that a try. Thanks very much!

Thanks!
Barb E.

 

[barbola]

That did not work for me, it said Error 70: Permission denied.

I put the code in Access, in the form load event of the main menu that opens on startup, if that helps.

Yet, it lets me copy it in Windows Explorer even when I have the Access file open.

I will look at FileSystemObject, unless you have any other ideas?



Thanks!
Barb E.