Results 1 to 4 of 4
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Match Error Table To Import Table


    I am using vba to import two excel spreadsheets into Access like this
    Code:
            DoCmd.TransferSpreadsheet acImport, , "vbaPLU", Me.txtPLU.Value, True
            DoCmd.TransferSpreadsheet acImport, , "vbaPTI", Me.txtPTI.Value, True
    Now, sometimes there will be an error on the import and a error spreadsheet will be created called XXXX_ImportErrors - I want a way to link that error table back to the imported table so I can show relevant data to the user to let them know what the issue was and hopefully remedy it.

    Question being, since I am importing two spreadsheets into two different tables - how do I link the ImportError table to the table that was created by the vba import?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Your excel file will need to have line numbers in it before import.
    THEN you can match line numbers in the err tbl to the excel file.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    My Excel file has line numbers, and the error table tells me the field and the row that is giving the error

    Since I am importing two tables, how do I know which table the import error table is for?

    I'm importing 2 excel files and getting 1 error table....how do I know which file this error table belongs to?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Look for it after each import:
    Code:
    dim tdf as DAO.tablebef, strErrorTable as string
    
    DoCmd.TransferSpreadsheet acImport, , "vbaPLU", Me.txtPLU.Value, True
    
    For each tdf in CurrentDb.Tabledefs
      if instr(tdf.Name,"_ImportErrors")>0 then 
          strErrorTable ="vbaPLU"  
          exit next
      End If
    next tdf
    
    DoCmd.TransferSpreadsheet acImport, , "vbaPTI", Me.txtPTI.Value, True
    
    For each tdf in CurrentDb.Tabledefs
      if instr(tdf.Name,"_ImportErrors")>0 then 
          strErrorTable ="vbaPTI"  
          exit next
      End If
    next tdf
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 7
    Last Post: 07-04-2017, 09:26 AM
  2. Error when trying to import table
    By Gary Childress in forum Import/Export Data
    Replies: 5
    Last Post: 05-29-2014, 12:45 PM
  3. Replies: 16
    Last Post: 03-13-2012, 03:47 PM
  4. Replies: 5
    Last Post: 03-01-2012, 12:59 AM
  5. Replies: 0
    Last Post: 12-08-2011, 09:12 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