Sometimes Query Data is Incomplete - Pulling Results into Excel with VBA

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

-------------Problems Reply------------

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 -_-

Category:excel vba Views:0 Time:2019-02-08

Related post

  • Streaming website data into Excel with VBA 2011-05-02

    A website provides streaming stock quotes via a browser over HTTPS connection. This is done by the server sending a POST HTTP header and updating its body with the new quotes. The DOM is then updated and this then translates into visual updates. I wa

  • Import external data into Excel with VBA 2013-01-12

    Hello, I have been trying to import data into an excel file from another excel file (both csv)f but have not been successful. I used several codes from online and they all seem to have some issues. Can someone please help me? Thanks --------------Sol

  • feeding data labels into excel with VBA 2009-12-18

    i have a scatter plot graph i would like each dot to have a label. how do i feed in labels through VBA for each dot? --------------Solutions------------- You're after the ApplyDataLabels method. Make sure you read the documentation, it has a lot of o

  • Exporting data into Excel with multiple spreadsheets 2012-01-27

    Has anyone successfully transferred data into Excel with multiple spreadsheets? I'm stuck with it. I'm using Visual Basic 2010. --------------Solutions------------- Yes, using EPPlus. It's as simple as: Dim ws = package.Workbook.Worksheets.Add("Name"

  • Convert text file into excel with delimiter and text formatted columns vbscript 2013-05-15

    I'd like to convert a .txt file into Excel with a VBScript. I have a character that I would like to delimit "|". I would also like to format column data as text before the text is opened. This is what I'm currently using minus the formatting of colum

  • Can you export a report into Excel with formatting intact? 2015-01-03

    I have created a report in Access 2007 (and also Access 2000-2003) but I can not get it to export with all formats intact. I have some conditional formatting that highlights certian data in different colors, but when I export the report into Excel, n

  • Query about sql regarding export the results into excel 2012-03-29

    In this query i tried to export the result into csv... But i am getting error as error:----Every derived table must have its own alias SELECT * INTO OUTFILE "c:/mydata.csv" FROM (SELECT e.server,e.token,e.datetime,e.workstation,f.surname,f.forename,f

  • Linked data from Access query into Excel with a date criteria 2012-06-11

    I have an Excel spreadsheet that contains external data from an ODBC connected Access database. I have a query set up in that data base that I want to link the results of the query directly to a separate Excel spreadsheet that I use to format the dat

  • Querying data from Oracle database using java servlet with Netbeans 2008-11-27

    From index.jsp code, statement.executeQuery("select * from fus where tester_num like 'hf60' ") ; Example I want "hf60" to be a variable(userinput), wherein USER must input/write data from input text then submit and get the data so that the result wil

  • Format data from internet an place into excel for mac. 2012-11-19

    I am wanting to extract data from chamber of commerce websites and enter into Excel on Mac. These address are in 3-7 rows on the web, but I want to make it all one row. I have asked the Apple community, and almost got it, but the farthest I got was i

  • Copying SQL Results into Excel 2013-11-12

    We recently upgraded to Windows 7. When I copy SQL QRY results into an Excel spreadsheet; I am getting multiple rows when a field has mutiple lines. Before the upgrade to Windows 7, I was not experiencing this issue. --------------Solutions----------

  • Pulling values into Excel from a connected laboratory balance 2014-03-12

    I would like to connect a laboratory balance to my Mac Powerbook and input the values into Excel when required by a separate program. I don't need to control the balance, just pull the current value. Thanks --------------Solutions------------- You wi

  • Exporting JSP data into excel with image 2011-05-14

    In our project we are having the requirement to export the JSP page into excel. We cannot use Apache POI or any other open source APIs. I came across the below mentioned simple way of doing <% response.setHeader("Content-Disposition", "attachment;

  • Cannot export data from Access into Excel with macros attaxched anymore 2013-05-28

    I have Access 2010. I have fiddled with the security settings in the Trust Centre because I was having trouble exporting some reports. I used to be able to export data from access to excel and the excel file was exported with macros attached. This wa

  • Getting Data from Office E-mails into Excel 2012-02-02

    Good Day All, I'm receiving data via Microsoft Office E-mail that I want to get into excel on a spreadsheet. Each E-Mail has the same fields my problem is that I have many many e-mails so I'm wanting to automise this somehow. Hopefully someone can he

  • Getting Data From Outlook E-Mail into Excel 2012-09-25

    Good Day All, I'm receiving data via Microsoft Outlook E-mail that I want to get into excel on a spreadsheet. Each E-Mail has the same fields my problem is that I have many many e-mails so I'm wanting to automise this somehow. Hopefully someone can h

  • How do I go about importing data from Access tables into Excel using VBA 2013-05-11

    I have an Access 2010 database (legacy mdb file) with multiple tables, and an Excel 2010 workbook (xlsm) with multiple tabs. I need to import data from Access into Excel. I need to import all of Table A and 3 columns of Table B. The index field of bo

  • How to import data from Windows Mobile App into Excel in C# 2011-10-19

    I need to create a Windows Mobile 6.5 app for data entry. Then data needs to end up insside Excel spreadsheet on the PC. What is the best way to do that? Can anyone send some sample codes just to get me started? I am developing in Visual Studio 2008.

  • Microsoft Excel cant detect the figure and currency symbol when data is cut and paste into excel. 2013-09-06

    When only happen when i cut and paste my bank statement from my bank website to excel. There is a long list of credit/debit figures and my currency sign is "RM" . My excel will show RM5000.00 instead of $5000.00, But my excel don't show up the sum on

  • Trying to automatically split data in excel with vba 2011-11-30

    I have absolutely no experience programming in excel vba other than I wrote a function to add a data stamp to a barcode that was scanned in on our production line a few weeks back, mainly through trial and error. Anyways, what I need help with right

  • Optimize SQL SELECT for 4000 searches, into Excel by VBA 2009-12-14

    How could this be optimized for speed by batching or other techniques? Its a 20MB Access2003 Database I am searching from Excel 2003 VBA. I have my Access Table Keyed (autonumber) so I though this would provide intelligent non-linear searching like b

  • Opening a CSV file which has date time field, getting opened in excel with "######" 2011-08-19

    While opening a CSV file in excel which has datetime field, it is getting opened in excel with the field marked as "######". Is there a way to format the excel file while opening the CSV file??? --------------Solutions------------- ##### Error Messag

  • SIMPLE: sort Booklist on Web into Excel using VBA macro? 2011-10-13

    Here is a website with a list of books in a relatively simple format. I copied the list into excel, and each group of % symbols is a listing for a book, with different details like keywords and such. for ins

  • Assistance with Data Entry UserForm Control Coding for Excel 2007 VBA 2015-01-23

    Hello, Greetings and Salutations. I have been making the rounds on the Excel Forums trying to get some help with the coding for a Data Entry User Form in 2007 Excel VBA which I have been designing. Some back story first, then I'll post a link to a sa

  • Putting UTF-8 characters into Excel with Perl? 2011-02-15

    I have XML documents that have many characters that are UTF-8 such as "É" and "é", and I am trying to put these into Excel, but there is something with the encoding that I am just not getting. I am using Win32::OLE to put the data in Excel. I have tr

Copyright (C), All Rights Reserved.

processed in 0.185 (s). 11 q(s)