VBA ADO更新查询 [英] VBA ADO Update Query

问题描述

我正在尝试创建链接到服务器主excel文件的动态客户端excel文件。

I am trying to create a dynamic client excel file(s) that is linked to a "server" master excel file.

目标是保持每个文件中所有数据的更新。基本上，当打开客户端文件时，我需要从主文件进行更新，然后我想根据客户端文件中的每个更改来更新主文件。

The goal is to keep all data updated in each of the files. Basically, when the client file is opened I have an update from the master file, and I then want to update the master file according to every change made in the client file.

我可以很容易地使用SELECT来获取数据，但更新查询将无法进行。
这是代码的一部分：

I can get data using SELECT very easily but update query won't work. Here is a part of the code :

Option Explicit


Private Type FichierSource
    'Objet Fichier source.
    Path As String
    SourceSheet As String
    TargetSheet As String
    Columns As String
    Filter As String
    Name As String
End Type

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

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

    'Fichier Source
    Base.Path = "U:\Macros\SQL\Base.xlsx"
    Base.SourceSheet = "DATA"
    Base.TargetSheet = "Base2"
    Base.Columns = "*"
    Base.Filter = ""
    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
    Dim i As Long

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


    Set Cn = New ADODB.Connection

    QUERY_SQL = _
    "UPDATE [" & TargetSheet & "$] SET [Col] = (SELECT [Col] FROM [" & SourceSheet & "$] " & _
    "IN '" & SourcePath & "' " & CHAINE_HDR & Filter & ")"



    STRCONNECTION = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source='" & ThisWorkbook.FullName & "';" & _
    "Extended Properties=""Excel 12.0 Macro;"";"

'    QUERY_SQL = _
'    "UPDATE [" & TargetSheet & "$] SET " & _
'    "[Col] = '3'"

    'MsgBox (QUERY_SQL)
    Cn.Open STRCONNECTION

    Cn.Execute (QUERY_SQL)


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

End Sub

当我执行注释的Sql查询以便将列'Col'更新为'3
'时正常工作，但是当我尝试使用主文件中的SELECT进行更新时，出现以下错误

When I execute the commented Sql Query so as to update the column 'Col' to '3 ' it works perfectly however when I'm trying to update using the SELECT from the master file I get the following error

操作必须使用可更新的查询

Operation must use an updatable query

更新：我认为真正的问题在这里：

我已经阅读了有关该主题的问题，但是任何问题对我来说都是有效的。确实如果我在连接字符串中设置了'ReadOnly = False'，则会收到以下错误'Pilote ISAM introuvable'（'ISAM Driver not found'）。

I've read questions raised on the subject but any worked for me. Indeed If I set 'ReadOnly=False' in my connection string I get the following error 'Pilote ISAM introuvable' ('ISAM Driver not found).

更新2：只要连接字符串不正确，就会弹出ISAM驱动程序错误。 （例如：错误的excel版本号）。
需要ReadOnly = False（或Mode ='Share Deny Write'），内部联接也需要。

UPDATE 2 : ISAM Driver error pops up whenever the connection string is not correct. (ex: a bad excel version number). The ReadOnly=False (or Mode='Share Deny Write') is needed, so is the inner join.

我已经实现了所有这些通过在excel连接中将连接添加到主文件中来手动进行，所以我知道这应该可行。

I've already achieved all of this manually by adding a connection to the master file in excel connection so I know this should be possible.

谢谢

推荐答案

我用 update 和 join 做了类似的测试，只是为了好玩，而且效果很好。这是我的代码：

I have made a similar test with an update and a join, just for fun, and it worked perfectly. Here is my code:

Sub SQLUpdateExample()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & ThisWorkbook.FullName & ";" & _
           "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"
    Set rs = New ADODB.Recordset
    Set rs = con.Execute("UPDATE [Sheet1$]  inner join [Sheet2$] on [Sheet1$].test1 = [Sheet2$].test1  SET [Sheet1$].test3 = [Sheet2$].test3 ")

    Set rs = Nothing
    Set con = Nothing
End Sub

也许您所需要的只是连接字符串中的; ReadOnly = False; 吗？ >
请注意，尽管我使用了驱动程序的名称，但它在.XLSM文件中有效。

Perhaps all you need is this ;ReadOnly=False; in your connect string ?
Note that , despite the name I use for the driver, this works in a .XLSM file.

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