ADO错误消息 [英] ADO error message

问题描述

我在尝试从另一个（并关闭）工作簿中的另一个excel表更新一个excel表时收到一条错误消息。
仅当源工作簿不是.xls格式时才会出现错误。

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.

您无法编辑此字段，因为它位于链接的Excel电子表格中。在此Access Release中，已禁用在链接的Excel电子表格中编辑数据的功能。

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.

这应该是使用Access时的预期行为。

1. 为什么我看到此错误？ m无法使用Access，而只能使用两个Excel文件，并且其版本比2007年更高？


2. 此功能如何提高安全性？


3. 我的情况是否有解决方法？除了在目标工作簿中使用临时表之外。

UPDATE：代码示例

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

谢谢

推荐答案

这是三个问题，但我将尝试回答所有问题：

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

1. 据我所知，它不依赖于Access或Excel，而是依赖于Microsoft Access OLEDB提供程序。您可以尝试使用Microsoft Excel ODBC驱动程序来使用ODBC连接


2. 一个堆栈溢出应答器表示这是出于安全原因而不是源。我怀疑是出于安全原因。


3. 有很多东西。如果您告诉我们您想确切做些什么，我可以更具体一些。

这篇关于ADO错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！