I have a query setup in an Access DB ('10). My users enter parameters into an Excel tool ('10), which uses VBA to pull the results of the query into the Excel tool.
Problem: Sometimes (1/50 times I'd guess) it will pull incomplete data, and I don't understand why.
It's the inconsistency that confuses me. My best guess is that something is causing the final piece of the code to activate and close the connection early. But I'm unsure why it would occassionally do so.
The Code: I updated the code slightly to include some error catching. The issue has persisted.
Sub ParameterQuery_Awesome() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False On Error GoTo Whoa 'Step 1: Declare your variables Dim MyDatabase As DAO.Database Dim MyQueryDef As DAO.QueryDef Dim MyRecordset As DAO.Recordset 'Step 2: Identify the database and query Set MyDatabase = DBEngine.OpenDatabase _ ("\\folders\Awesome.accdb") Set MyQueryDef = MyDatabase.QueryDefs("AwesomeQuery") 'Step 3: Define the Parameters With MyQueryDef .Parameters("[Enter Parameter:]") = [Parameter].Value End With 'Step 4: Open the query Set MyRecordset = MyQueryDef.OpenRecordset 'Step 5: Clear previous contents [DataInputRange].ClearContents 'Step 6: Copy the recordset to Excel [DataInput].CopyFromRecordset MyRecordset 'MsgBox "Your Query has been Run" LetsContinue: Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True On Error Resume Next MyRecordset.Close MyQueryDef.Close MyDatabase.Close Set MyRecordset = Nothing Set MyQueryDef = Nothing Set MyDatabase = Nothing On Error GoTo 0 Exit Sub Whoa: MsgBox "Oh noes!!1" & vbCrLf & _ "Error Description :" & Err.Description & vbCrLf & _ "Error at line :" & Erl & vbCrLf & _ "Error Number :" & Err.Number Resume LetsContinue End Sub
I'm a gigantic idiot.
I had a user send me a copy of the tool when it errored, to try and dig into the issue, and realized that although not all the data was populating, it was happening later in the process. All of the raw data was in fact making it into the tool. My apologies -_-