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

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

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

I think the IT folks are still laughing...

I posted about this idiocy a while back and explained how a major engine manufacturer managed to get more than 25 part "master" files with conflicting data.

 

[Isaac]

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

Hi CJ,

I used IMEX=1, but it still treats everything as numeric (which is what it sees in the first few rows for sure, and in fact 99% of the file).
Is this one of those...sometimes it works, sometimes it doesn't?

I'm needing to import data as TEXT into a table (whose destination column is text), even though the column has mostly numbers and a few other weird values, like a dash, - , and N/A

 

[Isaac]

Hi CJ,

I used IMEX=1, but it still treats everything as numeric (which is what it sees in the first few rows for sure, and in fact 99% of the file).
Is this one of those...sometimes it works, sometimes it doesn't?

I'm needing to import data as TEXT into a table (whose destination column is text), even though the column has mostly numbers and a few other weird values, like a dash, - , and N/A

Update .... I went ahead and added a few lines of code to manipulate a copy of the Excel file (browse/selected by the end-user) to pre-format an entire range of cells as Text and then paste original as values into it - then import this copy not the original.

This basically solved my whole problem, so I'm happy ... I just like the idea of the IMEX code so much I can't let it go