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

[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