Excel不将数据传输到Access数据库

I have a table in in Access 2010 that stores project information. The main columns - for this purpose - are LocationID, Priority1, and Priority2 (which have proper names but for generalization...)

我在Access 2010中有一个表,用于存储项目信息。主要列 - 为此目的 - 是LocationID,Priority1和Priority2(它们具有适当的名称,但用于概括......)

I also have an Excel 2010 spreadsheet that has this information to be exported into Access. What it does is run through the LocationID columns in Excel and Access, and colours all the matching fields. Then it checks the Priority1 and Priority2 columns to see if any of the rows are already filled in, and colours the ones that aren't. This part works fine.

我还有一个Excel 2010电子表格,可以将此信息导出到Access中。它的作用是运行Excel和Access中的LocationID列,并为所有匹配的字段着色。然后它检查Priority1和Priority2列以查看是否已填写任何行,并将未填充的行着色。这部分工作正常。

What it's supposed to do next is copy the values from two Priority columns that are coloured from Excel into the Access table. This isn't happening, and I'm not sure why.

它接下来应该做的是将两个从Excel着色的Priority列中的值复制到Access表中。这没有发生,我不知道为什么。

The entire code is fairly long, so here's just the section concerning transferring values. I can add any other code that might be useful.

整个代码相当长,所以这里只是关于传递值的部分。我可以添加任何其他可能有用的代码。

If (Not (IsNull(path))) Then
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & path & ";"
    Set rs = New ADODB.Recordset
    rs.Open "BCIImport", cn, adOpenKeyset, adLockOptimistic, adCmdTable
On Error Resume Next
    Do While Len(Range("B" & r).Formula) > 0
        With rs
            If Not rs.BOF Then
                rs.MoveFirst
            End If

            If (Range("B" & r).Interior.Color = RGB(0, 255, 255)) Then
                .AddNew
                .Fields("LocationID") = Range("B" & r).Value
                .Fields("Priority1") = Range("C" & r).Value
                .Fields("Priority2") = Range("D" & r).Value
                .Update
            End If
        End With
        r = r + 1 'advances to next row
    Loop
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End If

I'm not much of a VBA programmer, so any advice would be great.

我不是一个VBA程序员,所以任何建议都会很棒。

1 个解决方案

#1

My guess is your code triggers a primary key violation when attempting to add a new row with a LocationID value which matches the LocationID value of another row. This is where the new row is added:

我的猜测是,当您尝试添加一个LocationID值与另一行的LocationID值匹配的新行时,您的代码会触发主键冲突。这是添加新行的位置:

.AddNew
.Fields("LocationID") = Range("B" & r).Value

Since you have this earlier in the procedure ...

既然你在程序的早期有这个...

On Error Resume Next

... Access does not inform you about the problem which prevented that row from being added.

... Access不会告知您阻止添加该行的问题。

Test my guess by disabling that On Error line, as the other comments recommended:

通过禁用On Error行测试我的猜测,正如其他建议评论:

'On Error Resume Next

I suspect you will need a strategy which first checks whether the current LocationID value is already stored in the table. If it is present, update that existing row. And if not present, add a new row ... something like this ...

我怀疑你需要一个策略,首先检查当前的LocationID值是否已经存储在表中。如果存在,请更新现有行。如果不存在,添加一个新行......就像这样......

If DCount("*", "BCIImport", "LocationID=" & Range("B" & r).Value) = 0 Then
    ' add new row
Else
    ' update existing row
End If

#1