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

[Isaac]

Is it normal that, if you have a linked table object linked to an Excel workbook, that - while one user has the query 'open' in any way (such as, displaying a Form whose Recordsource is a query involving that linked table object), then during that time, another user is unable to run/open that query? (such as them trying to View the same Form in their FE) ?

I generally avoid linking to Excel but am doing so in this one oddball case, and just wanting to make sure that the behavior I am seeing is normal.

Is there any ideas on working around this or taking a separate approach?

Background:
The reason I am doing this is that the Excel workbook has a Data Connection to a Sharepoint list. It's nice to have this link, because then I get the names of people in Sharepoint People column like:

John Doe;#271;#Jane Doe;#3010;#Sally Doe;#1220

...etc
(I am using the term "Nice" loosely - obviously that's a horrible way to get data, but it's preferable to the way Access renders People columns from Sharepoint, which is very difficult to write any type of textual comparisons against, query expressions, etc - at least with the Excel link I can examine and process 'raw text' and come out with people's actual names).

 

[CJ_London]

rationally, if two people open the same excel file, the first to open has exclusive use to save, the second does not. In either event, the file is flagged as 'in use' but the contents being manipulated are in the users memory.

Doc can probably answer this more succinctly but pretty sure the flag prevents the file being viewed in access.

You could try using a query instead of a linked table, see if it makes a difference - query would be something like

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\mypath\myfile.XLSX'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes])  AS XL;

note the IMEX value - linked excel files are read only, setting IMEX=0 enables editing of the excel file. It might just make a difference.

Other factors are if excel is linked to sharepoint, whichever user is using access needs those same access rights

 

[Isaac]

I just want to clarify - the Excel file is linked as a linked table object to access. Nobody is opening the excel file 'directly' from where it resides in the network folder. Just that the FE has a query that involves this linked excel table, and when the query is open for one FE user, the second user cannot then open or run the same query. I re-read my first post and maybe I didn't make that very clear.
Is your thoughts still the same with that?

 

[CJ_London]

yes - my thoughts are the same. I do know that a query as I outlined enables access to edit excel data, something you can't do in a linked table - so there is a difference. Whether that difference enables you to overcome your issue, I don't know. I'm not able to test it as I don't have the right environment to do so.

create the query as I've suggested and use that in your query rather than the linked table, see what happens

 

[Isaac]

Ok, I'm going to try it and see. But - it seems like I would want to use IMEX=something other than 0, So that I don't open it Read-Write, but rather just Read, to reduce conflicts?

 

[CJ_London]

you can try 1 and 2, but both are read only - same as a linked table

 

[Isaac]

Okay, I went ahead and tried your suggestion. (I did try IMEX=0, 1 & 2).
I still had the same problem, although it did vary just slightly with the IMEX's.

When using IMEX=1 or 2, then if database1 was open with the bound form on display, database2 would give "external table is not in the expected format"

When using IMEX=0, then if database1 was open with the bound form on display, database2 would give the familiar error (same one I get when using my old method, plain linked excel table), where the error message references the full path "path\filename is in use" or something along those lines.

Just before giving up, I decided to go into the query properties (the newly minted 'CJ's method' query that replaced my Linked Table Object)....and mess with the record lock property. I changed it from Record Lock to No Locks. No improvement.

I guess I'm going to switch gears and, when the database opens, I'm going to actually Copy the excel workbook from its network home to the user's AppData folder, then link to it from there. So everyone's link will be to a unique local file. Hopefully that's the end of my linked excel table woes.

I learned something very valuable from this thread, and I have you to thank @CJ_London -- Thank you! I am pretty sure I will use this again, I could use to get more familiar with connection strings anyway.

 

[CJ_London]

glad you found it useful, shame it didn't solve the problem

nice thing about IMEX=0 is you can edit an excel file - at least update rows and add new ones. You can't change the columns or create a new table.

 

[Isaac]

glad you found it useful, shame it didn't solve the problem

nice thing about IMEX=0 is you can edit an excel file - at least update rows and add new ones. You can't change the columns or create a new table.

Yes, that's one thing that stood out to me about learning this. It contradicts something I have thought always, which is that access can't directly edit excel, outside of using Excel automation. So that's very fascinating to me that really, it can.. with that connection string.

I did find one other reason to go with the link instead of the connection string, and that is that for example I had a type mismatch on a join which previously worked okay, but with the connection string method apparently the data types came in a little bit differently. I decided I liked the traditional Excel link in this scenario because I can right click on it and go to design view and at least see how access is interpreting the column data type, which was helpful to me to realize that it was wrong and hence it must be my join that was producing that mismatch error.

But now that I've typed all this out, I realize I might have been able to specify the IMEX differently and actually had the column types come in more correctly, might have to take a second look at that!

Thanks again for bearing with all my questions, I'm glad I got something working. Cheers

 

[The_Doc_Man]

Somehow I missed this. Thanks, CJ, for the vote of confidence.

Part of the problem is that Excel and Access use different locking styles.

In theory you could write Excel VBA doing formal recordset operations, but that is not its native operational mode. I believe that if you were using Excel directly, only one person can edit an Excel file at a time even if it is set up for sharing. That is because Excel uses whole-file locks whereas Access uses disk-block locks. The Windows file locks CAN make a file shared-write but whatever Access is using to get into the Excel file doesn't bypass the Excel write lock. As CJ suggested, Excel just locks the whole file and is then done with it.

I'm going to go out on a philosophical limb and suggest that two factors relate to this difference in locking style. First, Excel is older than Access by a pot-load. (You can figure out what's in the pot on your own.) The style in which it was created is reminiscent of MS-DOS file management when file locking was just a skosh more primitive. Second. Access is organized into discrete tables and other component parts. Further, each table's records have data independence (to some degree). You can muck about in one table and leave the other entities alone. You can open a form or report and leave lots of other elements quiet.

Excel, on the other hand, is organized into discrete CELLS. At least in theory, you can build an Excel spreadsheet in which no two cells have anything to do with each other. Think about it. If a cell is supposed to contain a sum then it ACTUALLY contains a function and the function calls out a range. The cells being summed don't "know" that they are being summed and, in fact, a different cell could grab and sum a different range of cells that overlap with that first sum in some obscure way.

With Access, you have more predictability in cross-field or cross-record or cross-table references. In Excel, you can literally go all over the place (or go nowhere). For that reason, it would be far harder to do block-level locking with Excel. No predictability.

Net result: Excel doesn't share as well as Access does. And the observed behavior is consistent with that stinginess of sharing.

 

[CJ_London]

Thanks doc, not sure of any real practical use that I can think of but if you open excel, then in access an Imex=0 query to the same file, changes made in access are reflected immediately in excel. A change in excel is not seen in access until the data is refreshed by going a new row or similar causal action

 

[isladogs]

@CJ_London
Many thanks for reminding me of this approach which I had forgotten about
I have just mentioned this method in a thread at UA: https://www.utteraccess.com/topics/2061483/posts/2782885

 

[Isaac]

CJ I do have one question. Now I am not any type of knowledgeable person about this, so this question just stems from me googling it and reading various articles that I find. But what I find they reference the IMEX option as having to do with how column data types are recognized and matched. Whereas you are saying it has more to do with read write or read only or something along those lines, am I right? Or is it both?

 

[CJ_London]

@isladogs - thanks for the mention - probably worth pointing out that you also can't delete a record (row) in excel from access.

@Isaac - I agree, you can use imex=1 (import mode) to treat everything as text rather a datatype determined in the first few rows - in principle anyway. If your first 8 rows have a mixture of numbers and text, then the column is treated as text but if the first text value is after 8 rows, then the column is treated as numeric and you will get #num for the text values. If imex=0 or 2 and your first 8 rows have a mixture of numbers and text, then the column will be treated as numeric with the text displaying #num. Note the mixture is majority rules 5 numeric+3 text column is designated as number (or text for imex=1), 5 text and 3 numeric is designated text whilst 4+4 will default to numeric

you can change the default 8 rows to another number but involves editing the registry, can't quite remember which keys but can find out if required

The imex =0 is an export mode which in a sense is what your are doing when editing but as an 'edit function' would appear to be undocumented

imex=2 is supposed to be linked mode with full update capabilities, but that functionality was disabled many years ago. No idea how that would differ from imex=0 in terms of functionality. Lost in the mists of time.

 

[Isaac]

That's great information, thanks! I would think IMEX=1 might be a key piece of information, as a very attractive option, for folks agonizing over DoCmd.TransferSpreadsheet, versus data type struggles. Next time I need to ETL raw stuff from an external source I'm going to remember this ... Importing everything as text is precisely what I often want to do.

 

[isladogs]

@isladogs - thanks for the mention - probably worth pointing out that you also can't delete a record (row) in excel from access.

You're right. I've amended that post accordingly.

 

[CJ_London]

that's why I prefer to use csv or tab delimited files - you know exactly what you are dealing with and can plan accordingly.

with regards imex=1, still won't solve the problem if the first 8 rows are numeric and text is further down. I get round the problem by importing without headers and imex set to 2 which forces everything to text (unless you have a numeric header!). Append query just need to ignore the first row. Headers are then F1, F2 etc but assuming it is straight forward. Query then becomes

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\mypath\myfile.XLSX'[Excel 12.0;HDR=No;IMEX=2;ACCDB=Yes]) AS XL
WHERE F1<>"Customer"

Or whatever the column heading is for the first column.

 

[Isaac]

that's why I prefer to use csv or tab delimited files - you know exactly what you are dealing with and can plan accordingly.

with regards imex=1, still won't solve the problem if the first 8 rows are numeric and text is further down. I get round the problem by importing without headers and imex set to 2 which forces everything to text (unless you have a numeric header!). Append query just need to ignore the first row. Headers are then F1, F2 etc but assuming it is straight forward. Query then becomes

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\mypath\myfile.XLSX'[Excel 12.0;HDR=No;IMEX=2;ACCDB=Yes]) AS XL
WHERE F1<>"Customer"

Or whatever the column heading is for the first column.

Yes, a well laid out agreed-upon file format is the best!

Thanks for additional explanation on importing as text. Bookmarking.

 

[Gasman]

Somehow I missed this. Thanks, CJ, for the vote of confidence.

Part of the problem is that Excel and Access use different locking styles.

In theory you could write Excel VBA doing formal recordset operations, but that is not its native operational mode. I believe that if you were using Excel directly, only one person can edit an Excel file at a time even if it is set up for sharing. That is because Excel uses whole-file locks whereas Access uses disk-block locks. The Windows file locks CAN make a file shared-write but whatever Access is using to get into the Excel file doesn't bypass the Excel write lock. As CJ suggested, Excel just locks the whole file and is then done with it.

I'm going to go out on a philosophical limb and suggest that two factors relate to this difference in locking style. First, Excel is older than Access by a pot-load. (You can figure out what's in the pot on your own.) The style in which it was created is reminiscent of MS-DOS file management when file locking was just a skosh more primitive. Second. Access is organized into discrete tables and other component parts. Further, each table's records have data independence (to some degree). You can muck about in one table and leave the other entities alone. You can open a form or report and leave lots of other elements quiet.

Excel, on the other hand, is organized into discrete CELLS. At least in theory, you can build an Excel spreadsheet in which no two cells have anything to do with each other. Think about it. If a cell is supposed to contain a sum then it ACTUALLY contains a function and the function calls out a range. The cells being summed don't "know" that they are being summed and, in fact, a different cell could grab and sum a different range of cells that overlap with that first sum in some obscure way.

With Access, you have more predictability in cross-field or cross-record or cross-table references. In Excel, you can literally go all over the place (or go nowhere). For that reason, it would be far harder to do block-level locking with Excel. No predictability.

Net result: Excel doesn't share as well as Access does. And the observed behavior is consistent with that stinginess of sharing.

I'm on my phone, so not editing
We used excel sharing in the last bank I worked at, so we could a edit the same sheet, but different rows? It would however corrupt a lot

 

[Isaac]

I'm on my phone, so not editing
We used excel sharing in the last bank I worked at, so we could a edit the same sheet, but different rows? It would however corrupt a lot

Same here, I used and managed a lot of shared Excel workbooks back between like 2007 to 2012. That was before Microsoft basically took away or almost took away the shared workbook feature, causing it now to be something that must be elaborately unhidden and I've actually tried to follow those instructions and couldn't even find the options in the ribbon that they were talking about. I found a couple but not all three of them. Anyway they removed that more or less in favor of their stupid OneDrive and Excel online sharing.

I really wish they hadn't, as I found Excel shared workbooks on a network drive to be extremely useful. We mostly had people working on different worksheets anyway and everything was assigned and segregated out by those assignments, but it was a great way to have live dashboards of production...