Multiple Excel Users Linked To Access Queries

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.

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....?

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.., ...so 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?
...is this correct?

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

Perhaps I am missing something that Pat is considering?

JeffCoachman

It is also not clear what the "queries" are, ...that you are referring too...
I don't see any queries in your diagram there...?

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

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.

Hi Pat - Is this diagram what you were saying?  Thanks.  - Tom
EEQuestion-Revised.xlsx

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.

Thanks, Pat, for your answer.

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

I'm confused
1.

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.

JeffCoacman

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

Sorry,
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.

JeffCoachman

Thanks for the information; it is making me a better person.