Continuous Loop ADO VBA Access 2010
I am getting a continuous loop (I believe). When I run the code, I get the MsgBox prompt, click ok, and the program just runs and runs and never ends? At first I assumed it was a connection to the file error, but if that was the case, I should be receiving an error when ADO tries to connect to the file, correct? The file isn't that big, only 70ish rows. The way I have the MsgBox set, it should prompt for an OK to be clicked every iteration through the loop, but I never receive another MsgBox. Suggestions?
' 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
Thanks in advance for any help! Nathan
Get rid of the line On Error Resume Next. It should almost never be used. Because of that line, you have no idea where the error that is causing the loop is occurring.
Another good idea is to always use Option Explicit at the top of each module. This will ensure that you always declare your variables and saves more grief than anyone would think possible.