将数据从Excel导入Access时的运行时错误“3265” [英] Run-time error '3265' when importing data to Access from Excel

问题描述

我有一个VBA程序（利用ADO），可以将Excel电子表格中的一些数据导入到Access数据库的表中。在大多数情况下它工作正常，但源电子表格中有两列导致问题，我得到
消息：

I have an VBA procedure (utilizing ADO) to import some data from an Excel spreadsheet into a table into an Access database. For the most part it works fine, but there are two columns in the source spreadsheet that cause a problem and I get the message:

"运行时错误"3265 '：在与所请求的名称或序号对应的集合中找不到项目。"

"Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal."

这是问题发生的代码部分（具体是我注释掉的两行 - 行2& 4 here）。

Here's the section of code where the problem occurs (specifically the two lines I have commented out - lines 2 & 4 here).

            tbl！Model_Portfolio = rs！[型号组合]

            'tbl！Unrealised_PnL_Book_Sec_PC = rs！[未实现的P / L书籍。 PC]

            tbl！Unrealised_PnL_Book_Value_QC = rs！[未实现的P / L账面价值QC]

            'tbl！Unrealised_PnL_Cost_Sec_PC = rs！[未实现的P / L成本秒。 PC]

            tbl！Unrealised_PnL_Cost_Value_QC = rs！[未实现的P / L成本值QC]

            tbl!Model_Portfolio = rs![Model portfolio]
            'tbl!Unrealised_PnL_Book_Sec_PC = rs![Unrealised P/L book Sec. PC]
            tbl!Unrealised_PnL_Book_Value_QC = rs![Unrealised P/L book value QC]
            'tbl!Unrealised_PnL_Cost_Sec_PC = rs![Unrealised P/L cost Sec. PC]
            tbl!Unrealised_PnL_Cost_Value_QC = rs![Unrealised P/L cost value QC]

当我注释掉这两行时，程序的其余部分运行正常并且数据导入有效，除了那两行数据库中的列结束 空（当然）。

When I comment out those two lines, the rest of the procedure runs fine and the data import works, except those two columns in the database end up empty (of course).

两者之间唯一的相似之处是源文件在字段名称中包含句点（。），这是一个受限制的字符（位于我所包含的代码段的左侧）。我已经将源字段名称括在
方括号中，所以我不确定还有什么可以防止这个问题（假设我对问题的分析是正确的）。

The only similarity I can see between the two is that the source file contains a period (.) in the field name, which is a restricted character (that's on the left side of the code snippet I've included). I've already got the source field name enclosed in square brackets, so I'm not sure what else to do to prevent the problem (assuming my analysis of the problem is correct).

我可以在运行导入之前返回并手动修改源电子表格，但我希望能够以适当修改的形式使用我当前的例程，因为否则它似乎运行得很好。

I could go back and manually modify the source spreadsheet before running the import, but I'd like to be able to use my current routine in a suitably modified form, since otherwise it seems to run quite well.

如果有帮助，这是实际数据传输之前的代码：

If it helps, here's the code that comes before the actual data transfer:

Dim sConnect As String

   ;      sConnect =" Provider = Microsoft.ACE.OLEDB.12.0;" &安培; _
$
         " Data Source =  Y：\ reconconciliation test\imports\data_test.xlsx;" ; &安培; _

         "扩展属性="" Excel 12.0; HDR = YES; IMEX = 1"""&bbsp;           ;         


    Dim sSQL As String

        sSQL =" SELECT *" &安培; _

        " FROM [Port_Calc $]"

               


    Dim rs As ADODB.Recordset

     Set rs = New ADODB.Recordset

    rs.Open sSQL，sConnect，adOpenStatic，adLockReadOnly，adCmdText

       

    Dim tbl As ADODB.Recordset

     Set tbl = New ADODB.Recordset

    tbl.Open" Conversion"，CurrentProject.Connection，adOpenDynamic，adLockOptimistic

Dim sConnect As String
        sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source= Y:\reconciliation test\imports\data_test.xlsx;" & _
         "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
                  
    Dim sSQL As String
        sSQL = "SELECT * " & _
        "FROM [Port_Calc$]"
              
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open sSQL, sConnect, adOpenStatic, adLockReadOnly, adCmdText
       
    Dim tbl As ADODB.Recordset
    Set tbl = New ADODB.Recordset
    tbl.Open "Conversion", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

提前致谢！

Arjen

推荐答案

早安Arjen，

Good Morning Arjen,

从时间的角度来看，它似乎是你将文件放在映射的驱动器中，而不是文件夹位置的UNC路径。如果Y：\在网络上，则考虑使用UNC路径\\Server\Folder | file.ext

From a timing standpoint, it appears you have the file in a mapped drive rather than a UNC path to the folder location. If Y:\ is on the network then consider Using the UNC path \\Server\Folder|file.ext

如果这是在本地计算机上，则只能尝试给出通过添加DoEvents来分离代码并查看是否有帮助，可以多运行一段时间。

If this is on a local machine only you could try giving a little more time to run by adding DoEvents to separate the code and see if that helps.

否则，您可以测试将[。]添加到另一个字段以查看它是否也失败。 （在db ofcourse的副本中）

Otherwise you could test adding the [.] to another field to see if it fails also. (in a copy of the db ofcourse)

这篇关于将数据从Excel导入Access时的运行时错误“3265”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！