Edit Linked Excel Data in Access (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 16:05
Joined
Jan 14, 2017
Messages
18,186
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.

METHOD 1
Create a query to the external Excel file and set IMEX = 0 (instead of the default value 2). For example:
Code:
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;

I am very grateful to fellow AWF member @CJ_London 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 2
However, there is an even simpler method which I stumbled across yesterday by chance.
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

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

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

NOTE: 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.

This feature almost completely removes the need for automation or other work-rounds.
You can edit existing records and add new records. However, you still cannot delete existing records.

NOTE: These methods do NOT work for CSV or TXT files

For more details, see my web article:

Or watch the video I uploaded to YouTube:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:05
Joined
Feb 19, 2013
Messages
16,553
There are a couple of things on your website I don't believe to be correct and I've PM'd you to explain. Not included here because not mentioned here.

If you attempt to edit an excel cell from access to convert text to a number with a view to making the column numeric, it won't work - excel will store the value as text - sometimes the value as typed, sometimes some other value if your are trying to correct a #num error. If the column has been defined as text, your numeric value will be stored as text- see this edit from access of a value from 3 to 4.
1646004186816.png


and trying to correct a #num error for a numeric column
1646004722940.png


the value posted has been taken from another row and column in the table and is not what I tried to correct the value to
 

isladogs

MVP / VIP
Local time
Today, 16:05
Joined
Jan 14, 2017
Messages
18,186
Thanks.

I've now amended the webpage comment re: IMEX=1

I also found an interesting and unexpected quirk.
Using HDR=No, the first row should not be treated as a header. However, if HDR=No and IMEX=0, the first row is still treated as a header
This could cause problems!
 

Users who are viewing this thread

Top Bottom