Version 1.1           Last Updated 26 Feb 2022                 Approx 0.5 MB


Introduction

One of the main advantages of Access is its ability to read and edit linked data files from a wide variety of sources.

Many developers are therefore surprised that linked Excel, CSV and other text files are READ ONLY.
In fact that wasn't always the case. Until 2002, all it was possible to edit both Excel & text files.
However, in 2002 Microsoft lost a copyright infringement case which impacted on the interaction between Access and Excel.
As a result, Microsoft removed the ability to edit those linked files from Access and the functionality has never been restored.

For the past 20 years, the following has been true:
a) Records in linked Excel tables are READ ONLY. Records can NOT be added, edited or deleted.
b) Records in linked CSV, TXT files cannot be edited or deleted. However NEW records can be added.

As a result, most developers either use automation or import the data, edit then re-export (or paste back) to Excel.

However, there are two other solutions that are not widely known.
I am very grateful to fellow Access developer Chris Arnold for reminding me about the first method in this thread at Access World Forums back in June 2021:
Linked (Excel) Table - multiple users at a time unable to query



METHOD 1

Create a query to the external Excel file and set IMEX = 0 (instead of the default value 2).

For example, a simple SELECT query :

SELECT XL.*
FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
    [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

NOTE:
a)   HDR = yes/no. If yes, the first row is treated as field names. Otherwise, the headers will be automatically generated as F1, F2, F3 etc (but see EDIT below)
b)   IMEX = import/export mode with possible values 0,1,2.

IMEX = 0 or 2 results in the first 8 rows being checked and the most common datatype used for each field
IMEX = 1 forces all field datatypes to be text provided there is at least one text record in the first 8 rows of the field (including the header row where HDR = No)
IMEX = 0 allows the query to be editable but there is no clear documentation about this value

Using the above select query, you will be able to edit existing records and add new records.
Likewise, you can also run update and append queries based on the same idea
However, attempting to delete records results in an error message:




METHOD 2

However, there is an even simpler method which is, I believe, even less well known.
In recent versions of Access (2021/365), you can edit the connection string directly from the new linked table manager (LTM).
Once again, set IMEX = 0 instead of the default value 2:

Excel 12.0 Xml;HDR=YES;IMEX=0;ACCDB=YES;

. . . and BINGO => direct editing of linked Excel tables is now possible!

This almost completely removes the need for automation.
You can edit existing records and add new records. However, you still cannot delete existing records.

NOTE:
1.   If you have older versions of Access, you cannot edit connection strings using the LTM. However, you can of course still do so using VBA code.
2.   These methods do NOT work for CSV or TXT files
3.   Excel files can only be edited by one person at a time. If the Excel file is opened by another user, it will be locked and so READ ONLY even when IMEX = 0



To see this in action, please watch the accompanying video on my YouTube channel: Edit Linked Excel files in Access or you can click below:

       

If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.



EDIT 28 Feb 2022
I re-tested with 6 identical Excel files setting all 6 possible combinations of HDR=Yes/No and IMEX=0,1,2

#Num! errors occur where Access is attempting (and inevitably failing!) to treat a text record as a number


TAKE CARE when linking using HDR=No and IMEX=0.
As the top right example shows in the above screenshot, the first row is still treated as a row header although the HDR setting states it should be treated as a record.
This affects the total number of records detected.



Further Reading

a)   Karl Donaubauer (2008-11-06):     Linked Excel tables cannot be updated     (in German)
b)   Daniel Pineault (2022-02-18):       Advanced SQL To Connect With Excel WorkBooks
c)   Dale Fye (2017-04-09):                  Automating Microsoft Excel from Access



Download

Click to download:       Edit Linked Excel Files     Approx 0.5 MB (zipped)



Colin Riddington           Mendip Data Systems                 Last Updated 26 Feb 2022