Multiple Excel Users Linked To Access Queries

The attached document is a picture of what is currently designed for capturing data in Excel tables for reporting purposes.  Based upon the design are there potential issues with multiple users accessing the same queries in the Access database?  Thanks.  - Tom
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You are going to have trouble with this.  Although Access is multi-user, Excel is not and Access won't open an Excel file that is already opened by someone else.  So if Bob has a query running, Jean won't be able to start one because the Spreadsheets that Access is linked to can't be shared.

To make this work, create an Access template database.  Each day, start with an empty template and import the excel data to an Access database that contains ONLY the data.  Make sure you have appropriate indexes defined.    Then the users can all link to the shared Access database without a problem.
tomfarrarAuthor Commented:
Not sure I follow, Pat.  Not familiar with the term "Access template database".  It sounds like you are saying to separate the front-end Excel tables from the Access Database with the queries, or....?
Jeffrey CoachmanMIS LiasonCommented:
From what I see of your process flow diagram, ...I don't see any potentially "harmful"  issues.
It is not clear how fast you links will reflect any changes to the Excel source though.., you may wish to consider this (if you will be allowing direct access to the Excel source files while users are opening the links in Access.)

And lets be clear on what a "Report" is, (in both the ERP source and the Access "reports" you are showing in your diagram.)
In Excel a Report and a Sheet can be the same thing,...In Access Reports are separate objects from the source (table or query)

Finally, lets simply this into words:
You have multiple Excel sheets linked into Access and you want multiple Access users to view these sheets in Access?, ...and you want to know if any problems will arise? this correct?

If so, I don't see any...

Perhaps I am missing something that Pat is considering?

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jeffrey CoachmanMIS LiasonCommented:
It is also not clear what the "queries" are, ...that you are referring too...
I don't see any queries in your diagram there...?
tomfarrarAuthor Commented:
Jeff -  The plan is to have one person run reports out of the ERP on a periodic basis (say weekly) and save them as Excel spreadsheets that are linked to the Access DB as tables.  

The queries are within the Access database.  The linked Excel spreadsheets on the front-end of the database are the tables within the  Access DB.  There are Access queries written against the  linked tables creating data for the spreadsheets on the output side of the database.  There are no reports within the Access database; just queries and tables.  

The queries are run when the persons with the output spreadsheets on their desktop "refresh" the Excel table, which runs the Access query and returns the results.  The results in the Excel spreadsheet are tables showing the query results.  Did I answer all your questions?  Thanks.  - Tom
tomfarrarAuthor Commented:
I think the main issue may be when, and if, two users on the output side are trying to refresh their Excel report tables that run the same query within Access.  Are they both able to get results, or is one of them locking the other from getting a refreshed table.  I am guessing this may be what Pat is addressing, but I don't quite understand what she has said or what the ultimate solution would be.
tomfarrarAuthor Commented:
Hi Pat - Is this diagram what you were saying?  Thanks.  - Tom
tomfarrarAuthor Commented:
So Pat , I re-read your original comment and it sounds like the linking the ERP saved spreadsheets to the Access database may be the problem.  You are suggesting the ERP spreadsheets be imported to the database and not linked, is that correct?  

I was trying to avoid importing the data as the person working with the ERP data is not that familiar with Access and the process of importing.  That is why I linked the ERP spreadsheets to Access and did not import.  Is there no way to leave the ERP spreadsheets as linked, and still let multiple users use the Access output at the same time?  Thanks.  - Tom
When you create applications for others, you do not expose them to "Access" at all.  You create forms and reports and give them buttons to push.  No user should ever have to run an import wizard.  To automate importing Excel spreadsheets, use the TransferSpreadsheet method.  If the source files always have the same name and are always found in the same folder, those things can be hard-coded or defined in setup tables where they could be changed if necessary.  In fact, if the source is always updated on a regular schedule, you can completely automate the process and no human needs to touch it.  You can use the Windows scheduler to run the Access application that creates the populated database that others will link to.  If the name or location of the import file could vary, then you need to give the user a way to locate the file and you do this by using the File System Object (FSO) to open a standard Windows file dialog.

I suggested a template so that the tables could be predefined along with any necessary indexes.   The user (or the task scheduler) opens an application and pushes a button.  Behind the button, the code copies the empty template to the target folder overlaying the previous copy.  It then runs a series of TransferSpreadsheet commands to load the tables.  It then closes itself.  Using the template method avoids the database bloat issue that would otherwise arise from constantly adding/deleting data and so eliminates completely the need for compacting the database.

I am going to leave this conversation now since I just realized that I should never have responded at all as you do not wish to have input from me.
tomfarrarAuthor Commented:
Thanks, Pat, for your answer.
tomfarrarAuthor Commented:
Thanks all.  Since my question regarding the linked spreadsheets on the front end of the database has not yet been answered, as a heads-up, I will repost another question if I do not get clarification.   I am afraid there has been too many communications for other experts to participate.  Thanks again, both of you.  - Tom
Jeffrey CoachmanMIS LiasonCommented:
I'm confused
Since my question regarding the linked spreadsheets on the front end of the database has not yet been answered, as a heads-up, I will repost another question
First you have to close this question..

2. I thought the question was answered?
Your question was:
are there potential issues with multiple users accessing the same queries in the Access database?
Pat thought that there would be issues
I thought all would be fine.

So there are your answers?
You must evaluate each and report back to us.

I am wiling to yield to Pat's determination, as my opinion was based on an overall view of what you posted.

So what was lacking in Pt's subsequent posts?
She seemed to have explained herself clearly.

tomfarrarAuthor Commented:
First of all I didn't say I wasn't going to award points.  I did get some good information.  Pat explained herself very well, Jeff.  But she answered the question the way she would have done it.   Not the way I currently have things configured.

So, perhaps I didn't ask the question as well as I could of (think that happens often), as now trying "as designed" there appear to be problems, but I still don't understand what is causing the problem.  That is what I tried to clarify with Pat, but she answered my question with "you are doing it all wrong, this is how you need to do it".  That is not where I am at "at the moment".

I am going to repost the question because I still don't understand the problem with the design shown, and I imagine most other experts will run right past this question as there have been too many comments.  Thanks, Jeff, for your thoughts.  - Tom
Jeffrey CoachmanMIS LiasonCommented:
I did not mean to seem as though I was concerned about the points...
So if it sounded that way, I apologize.

I was just curious as to what the ultimate solution was/should have been.
Again, my cursory opinion was that all would be OK.

But Pat seemed to make a good point that there ay be some issues.
I am yielding to her opinion.

I think some of the confusion is over what you currently have and if it is "working" or not, ...or whether you are just asking if the configuration "looks good"

I seemed to have miss the point that it was not currently working.

So again, I will yield to Pat, and step aside avoid confusion.

I answered the question based on experience.  Access holds locks on spreadsheets.  If Access is linked to a spreadsheet that someone has "opened" during the current session, and linking to it via Access from another spreadsheet qualifies,   the second instance of Access won't violate the first instance's lock.  Even though Access can't update Excel via the linked table, it still locks it.  I ran into this in a class I was giving where we were all trying to link to the same spreadsheet.  The problem may exist only for multiple network connections although I attached two of the error messages I was able to generate with all the files on my local computer. -- sorry --- the EE server is having a problem and won't let me upload the images.

I offered an alternate solution since the first one will give you problems.  Of course, if two people never attempt to open the same spreadsheet at the same time, you will never experience a problem.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfarrarAuthor Commented:
Thanks for the information; it is making me a better person.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.