Results 1 to 4 of 4
  1. #1
    jurbin is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    15

    Identify Errors preventing data from importing from Acces to Excel

    I have been consistently getting an error almost every month when I try to load some data that has been input into access, then I have it linked to an excel sheet so that whenever I refresh, the access data imports into excel and updates my pivots, charts, etc. When my data entry ppl enter data, sometime they forget to enter a code, or something, and when they forget to enter that, it creates some kind of error in the query. The query still runs in access, but shows something similar to " #ERROR#" in the field IF I ever do find it in the access query. The issue is that I cant filter to find that error. I literally have to scan and scroll through thousands of lines of data to try and find this error. When I try to refresh the data in excel, the following error message pops up;

    "Data could not be retrieved from the database. Check the database server or contact your database administrator. Make Sure the external database is available, and then try the operation again."



    In past months I can usually find the #ERROR# by scrolling through access and finding it. Some months I have EXTREME trouble finding the error. It can take hours out of my work day. Is there any way to more easily identify which line these errors are in rather than scrolling through thousands of lines of data? Is there a way to still export the data to excel with the errors still in them? Please help me out. Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I don't know any easy way to find these errors. Suggest you will have to deal with the root issue. Why are data required for the export not being entered? What can be done to make sure the errors don't occur?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I have tried an alternative method to bring in the data...the COPYFROMRECORDSET action.
    in the excel sheet I run a query to put the data into the cells..
    Code:
    Public Sub BuildMap (pRst as recordset)
    With mXL
        .Workbooks.Open vTargFile   
        .Sheets("DATA").Activate
             ' Copy the recordset to the worksheet, starting in cell
        .Range("A1").CopyFromRecordset prst
        
            'format cells
        .Columns("G:I").EntireColumn.AutoFit
        .ActiveWorkbook.Save
    End With
    end sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Issue resolved?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formstack & Importing Data via excel, best way to collect data?
    By Yogibear in forum Import/Export Data
    Replies: 2
    Last Post: 02-10-2014, 07:05 PM
  2. Errors while importing data
    By imran688 in forum Import/Export Data
    Replies: 5
    Last Post: 10-29-2012, 03:24 PM
  3. Importing data from Excel
    By dsaxena15 in forum Access
    Replies: 1
    Last Post: 10-03-2012, 10:56 AM
  4. Acces or excel, for handleing my data?
    By oo0tommyk0oo in forum Access
    Replies: 1
    Last Post: 07-07-2011, 11:27 AM
  5. Importing Data From Excel
    By king_bowzow in forum Import/Export Data
    Replies: 1
    Last Post: 09-11-2009, 02:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums