为多个用户和文件设置从 Excel 到 Access 的 VBA ADO 连接的正确方法是什么？

What is correct way to set up VBA ADO connection from Excel to Access for multiple users and files?

提问by thepanu

I have several excel files that are used for entering data. Files are identical in functionality, one for each service center of ours. In the form there is button that launches a macro which transforms data to table format on another sheet which is then later uploaded to Access db.

我有几个用于输入数据的excel文件。文件在功能上是相同的，我们的每个服务中心一个。在表单中，有一个按钮可以启动一个宏，该宏将数据转换为另一个工作表上的表格格式，然后将其上传到 Access db。

Everything worked fine on my own computer. Adding new rows, updating existing rows and deleting existing roles. I had used early binding which lead to problems when I moved files to our network drive. I managed to convert files to late binding but then other problems arose.

在我自己的电脑上一切正常。添加新行、更新现有行和删除现有角色。我使用了早期绑定，当我将文件移动到我们的网络驱动器时会导致问题。我设法将文件转换为后期绑定，但随后出现了其他问题。

Most of the time, uploading to Access isn't working, especially when multiple users try to do stuff at the same time. Most common error code is that I am not using updateable query or that this method doesn't support backwards scrolling. I sorry for not reporting actual error codes, but I can't replicate them at the moment.

大多数情况下，上传到 Access 是行不通的，尤其是当多个用户同时尝试做某事时。最常见的错误代码是我没有使用可更新查询，或者此方法不支持向后滚动。我很抱歉没有报告实际的错误代码，但我目前无法复制它们。

My connection code is as follows, it is bit of a mix of copy paste code from different examples.

我的连接代码如下，它是来自不同示例的复制粘贴代码的混合。

Opening the connection and other prestuff

打开连接和其他 prestuff

Sub excel2access()

Const adUseClient = 3  
Const adUseServer = 2
Const adLockOptimistic = 3
Const adOpenKeyset = 1
Const adOpenDynamic = 2

Dim oConn As Object
Dim cmd As Object
Dim rs As Object
Dim r As Long
Dim criteria As String
Dim Rng As Range

Set oConn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")

oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source= '" & Range("dbpath").Value & "\" & Range("dbfile").Value & "' ;"

Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open "Select * from need_rows WHERE service_center = '" & Range("scenter_name").Value & "'", oConn

r = 2 ' the start row in the worksheet

Sheets("data").Select

This following bit looks through data in excel sheet and tries to find match from recordset found for that service center. If match is not found new record is created and if match is found the old record is updated.

以下位查看 Excel 表中的数据，并尝试从为该服务中心找到的记录集中查找匹配项。如果未找到匹配，则创建新记录，如果找到匹配，则更新旧记录。

Do While Len(Range("A" & r).Formula) > 0
    With rs
        criteria = Range("D" & r).Value
        .Find "identifier='" & criteria & "'"
        If (.EOF = True) Or (.BOF = True) Then
            .AddNew ' create a new record
            .Fields("service_center") = Range("scenter_name").Value
            .Fields("product_id") = Range("A" & r).Value
            .Fields("quantity") = Range("B" & r).Value
            .Fields("use_date") = Range("C" & r).Value
            .Fields("identifier") = Range("D" & r).Value
            .Fields("file_type") = Range("file_type").Value
            .Fields("use_type") = Range("E" & r).Value
            .Fields("updated_at") = Now
            .Update             
        Else
            If .Fields("quantity") <> Range("B" & r).Value Then
                .Fields("quantity") = Range("B" & r).Value
                .Fields("updated_at") = Now
                .Update ' stores the new record
            End If                      
        End If
        .MoveFirst 
    End With
    r = r + 1 
Loop

rs.Close
Set rs = Nothing
Set oConn = Nothing

MsgBox "Confirmation message"
End Sub

Edit: Based on link by barrowc I changed cursor type to adOpenStatic. I made a test with several users trying to upload data at the same time and everything worked perfectly. Until one user stayed in the file and spent quite a while editing data there and then tried to upload data to db and got following error message:https://dl.dropbox.com/u/3815482/vba_error.jpg

编辑：基于 barrowc 的链接，我将光标类型更改为 adOpenStatic。我与多个用户同时尝试上传数据进行了测试，一切正常。直到一个用户留在文件中并在那里花了很长时间编辑数据，然后尝试将数据上传到数据库并收到以下错误消息：https : //dl.dropbox.com/u/3815482/vba_error.jpg

Again, I am back where I started from.

再一次，我回到了我开始的地方。

Also, I am open to feedback on my code in general as well.

此外，我也愿意接受对我的代码的总体反馈。

I am using Office 2010.

我正在使用 Office 2010。

Am I doing it wrong? All help is appreciated.

我做错了吗？感谢所有帮助。

回答by Aaron Kempf

sounds to me like Jet isn't reliable enough for your environment. I frequently use SQL Server / Access Data Projects to consolidate information from multiple spreadsheets into a single database backend that doesn't barf when you add a half dozen users.

在我看来，Jet 对于您的环境来说不够可靠。我经常使用 SQL Server / Access Data Projects 将来自多个电子表格的信息整合到单个数据库后端中，当您添加六个用户时，该后端不会出现问题。

回答by ClintB

You are going to have a lot of issues with the database being locked by other users. This is for several reasons:

如果数据库被其他用户锁定，您将会遇到很多问题。这有几个原因：

• From what I can see you are not handling errors. Therefore if your script errors half way through the connection will be left open thus causing lock problems.
• By the looks of this, the macros could potentially keep the connection open for a decent amount of time (assuming no errors).

• 据我所知，您没有处理错误。因此，如果您的脚本在连接中途出错，则将保持打开状态，从而导致锁定问题。
• 如此看来，宏可能会在相当长的时间内保持连接打开（假设没有错误）。

Having created a lot of macros connecting to an MS Access database I can tell you straight up. You are going to have a lot of connection issues where the database is being locked by spreadsheets that someone has left open all day/night due to things such as not handling unexpected errors (the connection never closes).

创建了很多连接到 MS Access 数据库的宏后，我可以直接告诉你。您将遇到很多连接问题，其中数据库被电子表格锁定，由于诸如未处理意外错误（连接永远不会关闭）等原因，有人整天/晚上都保持打开状态。

Even once you fix the problems all you need is ONE person to be using the spreadsheet with the old code and they will continue to lock the database.

即使您解决了问题，您所需要的只是一个人使用带有旧代码的电子表格，他们将继续锁定数据库。

One massive problem is that if someone connects to the database when its already open by someone else I believe they inherit the connection type of the already opened database resulting in a daisy chain of write locks. You then need to make sure all connections are severed in order to reset the connection.

一个大问题是，如果有人在数据库已经被其他人打开时连接到数据库，我相信他们继承了已经打开的数据库的连接类型，从而导致写锁菊花链。然后您需要确保所有连接都被切断以重置连接。

You have also not shown us how the data is put into the spreadsheet in the first place. Perhaps you are not correctly closing the connection and that could potentially be the reason why sometimes the database is locked.

您还没有首先向我们展示如何将数据放入电子表格中。也许您没有正确关闭连接，这可能是有时数据库被锁定的原因。

There are many different things you could try to get around this:

您可以尝试许多不同的方法来解决这个问题：

• Easiest would be to use MS Access Front End + MS Access Back End.
• Instead of pressing this button and uploading the data through connection strings you could make it save the file in a folder which would then be processed by an ms access database that is sitting there watching the folder. This would mean that you upload script would be written in MS Access and just be processing the files. Wouldn't be as instantaneous as your current approach but all write connections would be coming from the same machine/user in this circumstance.
• Persist with the current method: eventually you may get it to a stable state but it will be a lot of frustration and effort as determining the reason for a lock may not always be easy. You could at least look at who has the file locked at the time and work from there but as mentioned earlier they may not have been the cause of the lock. They may have just inherited the lock type.

• 最简单的方法是使用 MS Access Front End + MS Access Back End。
• 与其按下此按钮并通过连接字符串上传数据，您还可以将文件保存在一个文件夹中，然后由位于该文件夹中的 ms access 数据库进行处理。这意味着您上传的脚本将在 MS Access 中编写并且只是处理文件。不会像您当前的方法那样即时，但在这种情况下，所有写入连接都将来自同一台机器/用户。
• 坚持当前的方法：最终您可能会使其达到稳定状态，但这将是很多挫折和努力，因为确定锁定的原因可能并不总是那么容易。您至少可以查看当时谁锁定了文件并从那里开始工作，但如前所述，他们可能不是锁定的原因。他们可能刚刚继承了锁类型。

Personally I like to use MS Excel to display MS Access data for users, but avoid like a plague getting it to update MS Access. However if I was to do this I would do it through the use of oConn.Execute commands not opening a recordset, comparing and slowing pushing as that would keep the connection open too long.

我个人喜欢使用 MS Excel 为用户显示 MS Access 数据，但避免像瘟疫一样更新 MS Access。但是，如果我要这样做，我会通过使用 oConn.Execute 命令而不打开记录集、比较和减慢推送来完成，因为这会使连接保持打开时间过长。

Sorry for the wall of text. Hope this information helps.

对不起，文字墙。希望这些信息有帮助。

回答by Mark Farmiloe

You could also try using action queries. First I would try to update using (you may need to format the now value)

您也可以尝试使用操作查询。首先我会尝试使用更新（您可能需要格式化现在的值）

dim count as long
oConn.Execute "UPDATE need_rows SET quantity = " & Range("B" & r).Value & ", updated_at = #" & Now & "# WHERE service_center = '" & Range("scenter_name").Value & "' AND identifier='" & Range("D" & r).Value & "' AND quantity <> " & Range("B" & r).Value", count

If count is zero then no row was updated, so either there was no row to update or the quantity hasn't changed. Either way we can try to INSERT, which will fail in the latter case, but without causing problems.

如果计数为零，则没有行被更新，因此要么没有行要更新，要么数量没有改变。无论哪种方式，我们都可以尝试 INSERT，在后一种情况下会失败，但不会引起问题。

if count = 0 then
    count = oConn.Execute "INSERT ...", count
    if count = 0 then
        ' quantity has not changed, so nothing to do
    else
        ' new record inserted
    end if
else
    ' record updated
end if