Need to Have Linked Table Open in Excel for Access to Work...?

[Stang70Fastback]

Need to have linked table open in Excel for Access to work...?

Hey guys and gals. Probably a n00b question here.

I have a .accde file that pulls data from many sources, including a .xlsm file. This is on a shared network, and many people have that .xlsm file open at any given time, with the person in charge of that file making changes to it throughout the day.

I've encountered an issue where I receive an error from Access when trying to run a report that uses data from that .xlsm file. The error states:

The Microsoft Access database engine cannot open or write to the file '....'. It is already opened exclusively by another user, or you need permission to view and write its data.

I assume this is an error that occurs because when you open the file on that computer, you will get the, "This file is read only, would you like to open it in read only mode," warning in Excel since another user is currently editing it, which I assume is something that Access does not know how to handle. Is there a way to avoid this? The current workaround is for the user to open the Excel file first and accept that warning, opening the file in read-only mode. So long as they have the file open, Access doesn't complain.

So is there a way to have Access accept the fact that the file is read-only and proceed anyway?

Thanks!

 

[Pat Hartman]

How is the Access program accessing the spreadsheet? Try importing it rather than linking to it.

I would also consider replacing the spreadsheet with an Access application to collect the data. This will be more stable as a shared application.

File sharing issues are sometimes caused when PCs hosting different operating systems try to share a file on a server. Do all your PCs have the same OS? or do you have a mix?

 

[Stang70Fastback]

All of the PCs using the file are running Windows 7.

I have the spreadsheet linked, rather than imported, because it is constantly changing and needs to be kept up-to-date.

As far as replacing the Excel document with an Access one, that is not possible right now for several reasons that I won't bore you with.

Surely, though, there must be a way to solve this problem. I can't possibly be the only person with a linked Excel table that is shared among other users. All it would appear as though Access needs is a hint as to how to handle that shared file. I've also noticed that I get two different errors depending on what is going on with the other file.

If someone has the Excel document open, then Access gives the error I mentioned above, about not being able to open or write to the file. However, if that person closes the document, and it is now not in use by anyone, then I get a different message stating that the table is not in the expected format. BOTH of these errors are solved by simply having the document open on the computer on which you are running the Access program, so I'm a bit confused as to what is going on.

 

[Pat Hartman]

Try relinking or refreshing the link before using it. Although Access lets you treat linked spreadsheets as tables to work with them, they are really flat files and so are actually quite diffent from links to ACE/SQL Server/Oracle etc.

Also, does Access lock on to the spreadsheet and cause other applications problems? If so, you might consider dropping the link when you are done.

 

[Stang70Fastback]

Try relinking or refreshing the link before using it. Although Access lets you treat linked spreadsheets as tables to work with them, they are really flat files and so are actually quite diffent from links to ACE/SQL Server/Oracle etc.

What do you mean by refreshing the link before using it? I tried re-linking the table in Access but that did not resolve the problem, unless you are talking about doing so programmatically every time, in which case I don't know how to do that.

Also, does Access lock on to the spreadsheet and cause other applications problems? If so, you might consider dropping the link when you are done.

I haven't noticed any issues, with using the Access app and other people using the spreadsheet at the same time, but it's possible nobody has yet tried to open a spreadsheet while the Access app was being run. I had noticed that on the computers at work, before we had this issue, access would actually visibly open a second copy of the Excel file in read only mode in Excel, even if you already had the file open in another Excel window. I thought that was odd because it does not do that on the computer I am developing the program on at home... Why is it opening the Excel file at all?! There are other linked tables in this program (a .csv file, for instance) and those aren't opened in the background... so what makes the Excel file so special?

 

[Pat Hartman]

If you manually deleted the link and then linked again manually and that still caused the problem, then doing it programatically won't solve the problem.

It doesn't surprise me that Access can't link to an actively in use spreadsheet. Excel has its own method of managing concurrency and Access shouldn't have to replicate it. That leaves us with how current do you need the data? Can you copy the spreadsheet and then link to the copy?

 

[Stang70Fastback]

It needs to be as current as possible as that spreadsheet is always being updated. Is it possible to do something programmatically to HAVE a second version of that Excel file available to Access? Perhaps have Access create a duplicate file and then delete it? Is that what you were referring to?

 

[Pat Hartman]

That's what I was suggesting. Copying the workbook, using it, deleting the copy.

 

[Stang70Fastback]

I see what you mean.

By the way, I attempted to create a blank Access Database on one of the workplace computers themselves, and then link the spreadsheet, and I have the same odd issue. If I don't have the spreadsheet actively open in Excel (and at this late hour, there is nobody actively using it, so it isn't open anywhere else) I get an error when I try to create the linked file stating:

"The wizard is unable to access information in the file '...' Please check that the file exists and is in the correct format."

If I then open the file in excel and attempt to link it, everything works fine. I'm starting to think the Excel file itself is what is causing problems. It's a .xlsm file as it has some macros in it. Does this give you any indication of an underlying problem, by any chance?

Again, I don't have any of these issues on my home computer, though that is running Access 2010, whereas here I was using 2007. They do have the 2010 runtime which is what my database uses when they run it, but I obviously couldn't use that to try building a new database.

 

[Stang70Fastback]

I just wanted to follow-up on this thread. I ended up "solving" the problem by adding some VBA code such that Access opens the Excel document in a new hidden Excel window prior to calling anything that references it. It then closes the window when it's done. Problem solved. That way whether they already have it open or not it works.
Thanks for your help guys!

 

[Pat Hartman]

I don't know what the .xlsm extension is for. You can have macros in any spreadsheet.

Check your Access macro security and make sure it is set to its lowest level. Try saving this workbook with a different extension and see if Access still refuses to open it.

 

[stoolpigeon]

Stang - I'm having the same problem. I'm reasonably profficient at excel VBA but wouldn't have the first clue where to start with programming that in Access vba. Can I get a copy of your code?

 

[JoeC_Glenmark]

Hey. I have the same problem. Could you paste the solution here.

Thanks,
Joe

 

[hurtball]

Using the OP's original train of thought, this is what I just wrote. Seems like poor work-around to the problem, but it works.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
    
Set xlBook = xlApp.Workbooks.Open(fileName:="MyFile.xlsx", ReadOnly:=True)
xlBook.Windows(1).Visible = True
CurrentDb.Execute "INSERT INTO [Local Table] SELECT * FROM [Linked Excel]"
'[Linked Excel] would be a linked table/sheet/range in MyFile.xlsx
xlBook.Close (False)

Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

You can play with the visibility as I didn't care if it shows or not.