连续循环ADO VBA访问2010

Question

我得到一个连续循环（我相信）。当我运行代码时，我得到MsgBox提示符，单击确定，程序刚刚运行并运行并永不结束？起初，我认为这是一个连接到文件错误，但如果是这种情况，我应该在ADO尝试连接到该文件时收到错误，对吗？ 文件不是那么大，只有70行。我有MsgBox设置的方式，它应该提示在循环的每次迭代中单击OK，但我永远不会收到另一个MsgBox。建议？

' The following section reads from the elec_copy field's hyperlink 
' It scans the Excel file for items it needs to include into the table 
' It enters those cells into the TABLE 'items_needed_table' 
' 
' Selects row by row, and if the item has been marked TRUE, inserts 
' That row into the TABLE 'items_needed_table' 

' Open a connection to Excel 
On Error Resume Next 

Const adOpenStatic = 3 
Const adLockOptimistic = 3 
Const adCmdText = &H0001 

Set objConnection = CreateObject("ADODB.Connection") 

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & elec_copy.Value & ";" & _ 
     "Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";" 

' Decalre a RecordSet Object 
Set objRecordSet = CreateObject("ADODB.Recordset") 

' Grab all Rows in the Plain_VDR Sheet where 'needed' column == TRUE 
objRecordset.Open "SELECT line_no,desc,weeks FROM [Plain_VDR$] WHERE needed = TRUE", _ 
    objConnection, adOpenStatic, adLockOptimistic, adCmdText 

' Write the information pulled, into the TABLE 'items_needed_table' in Access Database 
Do Until objRecordset.EOF 
    MsgBox("" & qd.Parameters("p2")) 
    Set qd = data_base.CreateQueryDef("") 
    qd.sql = "INSERT INTO items_needed_table(pr_no, line_no, desc, weeks) " & _ 
     "Values([p1],[p2],[p3],[p4])" 
    qd.Parameters("p1").Value = pr_num.Value 
    qd.Parameters("p2").Value = objRecorset.Fields.Item("line_no") 
    qd.Parameters("p3").Value = objRecordset.Fields.Item("desc") 
    qd.Parameters("p4").Value = objRecordset.Fields.Item("weeks") 
    qd.Execute 
    objRecordset.MoveNext 
Loop 

' Close Database connection 
data_base.Close 

在此先感谢您的帮助！ Nathan

Answer 1

摆脱错误恢复下一行。它几乎不应该被使用。由于那条线，你不知道导致循环的错误发生在哪里。

另一个好主意是总是在每个模块的顶部使用Option Explicit。这将确保你总是声明你的变量，并比任何人想象的都可以节省更多的悲伤。