ADO error message

Question

I am getting an error message while trying to update one excel table from another excel table in a different (and closed) workbook. The error only appear when the source workbook is not in a .xls format.

You cannot edit this field because it resides in a linked Excel spreadsheet. The ability to edit data in a linked Excel spreadsheet has been disabled in this Access Release.

This is supposed to be an expected behavior when using Access.

Why am I seeing this error as I'm not working with Access but only two Excel files, and with a more recent version than 2007 ?
How does this feature improve security ?
Is there a workaround in my case ? Other than using a temp table in my target workbook.

UPDATE : Code example

Sub GetFiles()
    'Take !M sheet to create files and their informations
    Dim Base As FichierSource

    '----------------------------
    'Create files object
    '----------------------------

    'Fichier Source
    Base.Path = "C:\Users\Lichar\Documents\SQL TEST\Base.xlsx"
    Base.SourceSheet = "Data"
    Base.TargetSheet = "Base"
    Base.Columns = "*"
    Base.Filter = "WHERE [Base$].id = [Data$].id"
    Base.Name = "Base.xlsx"


    '---------------------------
    'Launch queries
    '---------------------------

    With Base
        Call UPDATEQUERY(.Path, .SourceSheet, .TargetSheet, .Columns, .Filter)
    End With

End Sub

Sub UPDATEQUERY(SourcePath As String, SourceSheet As String, TargetSheet As String, _
Columns As String, Filter As String)

    Dim Cn As ADODB.Connection
    Dim QUERY_SQL As String
    Dim CHAINE_HDR As String
    Dim STRCONNECTION As String

    CHAINE_HDR = "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=5;Extended Properties='HDR=YES;'] "

    Set Cn = New ADODB.Connection

    QUERY_SQL = _
    "UPDATE [" & TargetSheet & "$] INNER JOIN (SELECT * FROM [" & SourceSheet & "$] " & _
    "IN '" & SourcePath & "' " & CHAINE_HDR & ") t2 " & _
    "ON [" & TargetSheet & "$].id = t2.id " & _
    "SET [" & TargetSheet & "$].ColA = t2.ColA "

    STRCONNECTION = _
    "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
    "DriverId=790;" & _
    "Dbq=" & ThisWorkbook.FullName & ";" & _
    "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"

    Cn.Open STRCONNECTION
    Cn.Execute (QUERY_SQL)

    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing

End Sub

Thanks

score 1 · Accepted Answer · answered Oct 29 '17 at 15:16

1

That's three questions in one, but I'll try to answer all of them:

It's not dependent on Access or Excel, but on the Microsoft Access OLEDB provider as far as I know. You can attempt to use an ODBC connection using the Microsoft Excel ODBC driver
One Stack Overflow answerer saying it's because of security reasons is not that good of a source. I doubt it's because of security reasons.
There are lots. If you tell us what you want to do exactly, I can be more specific.

answered Oct 29 '17 at 15:16

Erik A

31,639
12
42
67

I'm a bit lost in all those providers, drivers.. How am I supposed to use the ODBC driver ? Here is a [question](https://stackoverflow.com/questions/46991977/openrowset-in-vba-ado-query) on what I'm trying to achieve. – Lich4r Oct 29 '17 at 15:33
You're not providing any code, so I can't adjust your code to use the ODBC driver. Please provide your own, I don't like to copy-paste from other questions (authorship reasons) – Erik A Oct 29 '17 at 15:34
You need to be a bit more specific on your setup, using this code (at least most of it), I can execute update queries on a simple worksheet. Note that you can't edit linked data (expressions, linked sheets) by using an update query on the linked cell/sheet, you should edit those at the source – Erik A Oct 29 '17 at 16:25
Added the above routine. Source is in an external wb as you can see. The tables are example tables filled with random values (no formula, no link). Note that I can do this update if source file (Base) is a .xls format. I want to achieve the update from .xlsx et .xlsm files – Lich4r Oct 29 '17 at 16:30
Ah. I believe the IN clause makes your query non-updateable. Or it's the JOIN, using a JOIN on a table with no index (such as an Excel sheet) makes the table non updateable. – Erik A Oct 29 '17 at 16:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157756/discussion-between-lich4r-and-erik-von-asmuth). – Lich4r Oct 29 '17 at 16:40

ADO error message

1 Answers1

Linked