Linked (Excel) Table - multiple users at a time unable to query (1 Viewer)

[Minty]

Looks promising and I will have a look as soon as I get finished with the latest "big thing". Really appreciate your time...

You are more than welcome - I may have forgotten to include some Enumerations that I have set up globally - compiling it will soon tell you.

 

[CJ_London]

just curious - why not try the query? Only takes a minute

 

[Minty]

@CJ_London I think @NauticalGent really secretly loves Excel and wants to improve his skilzz.

Honest

 

[NauticalGent]

just curious - why not try the query? Only takes a minute

At first it was because I wanted to make a global solution that allows the users to open up the forms whose recordsource is based on the Linked Excel files. There are about 10 of them that get updated (exported from the Program of Record, SAP I believe) weekly. At no time is the data changed and since linked Excel files are read-only, it cut out a lot of the existing Empty/Delete table, Import Table process. But if some one has a form open that uses the file - either directly or via a query - everyone has to wait until the user closes the form. What kind of STUPID is THAT?!?! This should be a setting that can be toggled off and on easily. When I came across the MaintainConnection Property and that bit of code, I thought I had the fix.

When Minty chimed in, I was hoping he would figure out what I was missing but I am thinking that bit of code has to been run within Excel.

So that I am out of ideas, I will take the time to try your suggestion. My laziness wanted to avoid having to write 10 or so new queries and then change the applicable forms.

I will let you know how it turns out.

 

[NauticalGent]

CJ, I made the Query as suggested and as pleased that it ran so quickly - unfortunately, the same issue persists. If I have the query open and someone else tries to open the same qurery on thier FE, they get the SoL message.

 

[The_Doc_Man]

See my discussion near the bottom of this thread's post #10. I believe the problem with Excel is that its file locks cannot be granular because its internal structure is self-limiting. Doesn't matter who or how you open it. Locked is locked. One of the "make a copy" solutions might help.

 

[CJ_London]

Something to try, not sure if it will work

Having created your query in sql, go to the query gui and change the recordset type from dynaset to snapshot. then save the query.

Or perhaps do this in your form instead - that might even work for a linked table

Note that IMEX has nothing to do with file locking, only which datatype a column of data will be interpreted as based on the first 8 rows. My point was that with IMEX=0, you can edit the excel file which is something you can't do with an excel linked table.

Another thought is to use ADO to create a disconnected recordset and set the form recordset to that.

 

[NauticalGent]

Just to wrap up this bit regarding the "MaintainConnection = False" property, I came back to this and ran this sub in Excel.

It ran with no issues (once a established the connection!) but still would not allow two users to open the linked file.

I and cutting bait and moving on. We have put in a request for an instance on a SQL server and will probably use SharePoint in the meantime. They told us it can take up to 3 months for the request to process.

At least I'm getting paid...

 

[Gasman]

I am probably shooting myself in the foot here but.....
I shared a workbook, and linked it to two different DBs, opened both and was able to open both linked tables.?
I was also able to open a query on both.?

All read only of course?

Wasn't the initial post about read only?

 

[NauticalGent]

Yeah, no idea how you pulled that off. If more than one user tries to open a form that uses a query based on the linked Spreadsheet, the second user gets the error...

 

[Gasman]

I'll try from another computer with a excel file on my NAS from two computers, when I get back from taking this passenger.

 

[Gasman]

Yeah, no idea how you pulled that off. If more than one user tries to open a form that uses a query based on the linked Spreadsheet, the second user gets the error...

Ok, managed to get to two computers.
I selected an Excel file and Shared it.
I then created a new DB on each computer.

I linked the Excel file to each DB. I noticed that no other program other than Excel can be accessing the file when doing this.
I then created a query from the linked table on both computers.
I then created a form based on the above query on both computers.
I then opened form on each computer, without any issues.?

Bear in mind, all read only. Excel file is on a NAS box.

 

[CJ_London]

I think the issue is the users window ID, @Gasman, when you logged into each computer, did you log in as the same user? or different users?

 

[Gasman]

No,
I am Paul on one and SRVPH on the other. All my computers have access to the NAS boxes in the house.
When I tried on my personal laptop, although I tried two different DBs, I was obviously the same person.
That is why I waited, until I had access to two computers, one a laptop and the other a desktop.
The desktop was my old works server, so I left the username as it was.

 

[Isaac]

After pouring over the vast amount of (very good and educational) advice on this topic, combined with experience to-date, I kind of decided linked excel tables was a no-go, which aligns with what I had decided years ago, but revisited when creating this thread.
Now, I do have one db where I found it useful to 'copy' an excel file to the user's local and link from there, they know the data is not 100% up to date.
If I really found myself stuck with a situation where users were constantly updating an excel file with data that needed to go into access, I would probably choose a method where I did some programming in the excel file itself to export its data somewhere (flat file) and then suck it up into access as needed. This is where experience in both excel and access vba are priceless, sometimes you end up in an environment where you have to deal with both, like it or not. Excel has multiple Events which would all be possibilities to "re-export" the latest data. ThisWorkbook.AfterSave would probably be my top choice.

 

[gemma-the-husky]

It doesn't solve the problem, but the real issue is relying on Excel as a mission-critical document.
It's OK as an output format from a database, but not really for a reliable multi user tool.
I don't mind using a csv or an xls/xlsx as output from another system, but I don't like using user-edited Excel files.

I find it amazing that IT departments quibble about Access, but allow untrammelled use of Excel.

And it's everywhere. Even government departments issue Excel worksheets as "data".

 

[NauticalGent]

As a followup to this. I was able the convince the lead developer to stop importing the spreadsheets as a whole and break up the 1 massive table in thee smaller, normalized tables that would be updated from the spreadsheets provided on a weekly basis.

He fell in love with the idea solely on the reduced about of time it takes to update/refresh the data and NOT the benefits of a normalized DB.

The struggle is real...

 

[Isaac]

I agree, but with the caveat that Excel is also excellent at number crunching - that's what it's for, and Access isn't always necessarily a better solution.
I think when the work that needs to be done involves scenarios and number crunching, Excel is often best.
To this day I am flabbergasted at the speed at which Excel calculates 100,000 rows of formulas data - virtually instantly, and frankly faster than my database can. PLUS it can then render that on the screen while paging, something that Access tends to slow when doing.

It just all depends. - right tool for different jobs.

 

[Gasman]

It doesn't solve the problem, but the real issue is relying on Excel as a mission-critical document.
It's OK as an output format from a database, but not really for a reliable multi user tool.
I don't mind using a csv or an xls/xlsx as output from another system, but I don't like using user-edited Excel files.

I find it amazing that IT departments quibble about Access, but allow untrammelled use of Excel.

And it's everywhere. Even government departments issue Excel worksheets as "data".

In Lloyds bank, the Excel workbook would regularly get corrupted due to multi user updates, and they would start anew. However instead of renaming the new as the same as the old, they would add a suffix? So when you had pinned it to the Open list, you had to change it each time. I could never work out why, other than that is management for you?

 

[NauticalGent]

Using Excel as the "master" file is pretty terrifying.

If that is regarding my posts, the Excel file is exported for an "Enterprise" Program of Record the DoD uses. Either Oracle or SQL.

I asked when I was first hired if we could have direct, read only Access so that we could have real time data.

I think the IT folks are still laughing...