Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179

    Subscript Out of Range Excel to Access

    Hello,

    I was successful importing data from Excel to Access except for tables 'AFRs' and 'AFRsParts'.

    I attached the files and would appreciate it very much if someone can help me to get the data into Access.

    Thanks,
    Bill

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How are you importing?
    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
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I am using the external data from Excel to Access.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Subscript out of range happens when table selected doesn't match the worksheet.

    I get type conversion import errors on TechnicianID, RepairmanID, DispositionID columns, 3 rows for Pressure (728, 1434, 1691), 1 for Temperature (row 1552). Those pressure and temperature values have comma or > instead of period. It's odd those 4 failed because you are saving to text fields. After fixing, 323 rows fail for ID fields.
    Seems the ID cells are blank on those rows. Could be the cells are actually holding empty string and not null.

    Why are you saving temperature, humidity, pressure to text fields? I changed to number type and those import fine.

    AFRsParts fails with error "Field Quantity doesn't exist in destination table".
    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.

  5. #5
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    The AFRsParts had a missmatch on Qty and Quantity so I corrected but it still doesn't import the data. I get error saying quite a bit of records are lost.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I get type conversion error on 26 rows for Quantity. Examples of issue: row 9602 value is 40", row 9691 value is 2 IN.


    Advise not to use spaces nor special characters in field names. If you have control over the Excel file, suggest you change Part Number and PO # names as well.
    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.

  7. #7
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I suppose the 2 in was entered because is was a length instead of an actual quantity. Does that matter?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It certainly does. Quantity is a number field. Can't have alpha characters.
    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.

  9. #9
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I made some corrections and when I import the AFRsParts only 9 records import. Those records do not have AFRsIDs (blanks).

  10. #10
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I noticed on my AFRsParts table there are spaces in the names of :

    Part Number
    PO #

    Should these be corrected without the spaces?

    There is a PartNumber field in another table.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Those field names shoudn't be a problem for the import, but can cause frustration in other situations.

    Is this import a one-time exercise?

    Only 9 records imported or didn't import?
    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.

  12. #12
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Only 9 records imported and it says data is lost in over 11000 records

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I fixed the 26 rows in AFRsParts sheet. All but 50 rows imported. Unfortunately no errors table generated so I have no idea which rows. However, I think the cause is there are no related records in AFRs table. I tried to copy/paste the worksheet and that is the error message I get.

    So, went and tried copy/paste of AFRs. Get an error "data is too long for field" and a paste errors table generated. Only 1 row failed - AFRsID 3516. RepairManual value is too long. I changed "Trim Cable Test Procedure" to "Trim Cable Test Proc".

    If you want the AFRsID in worksheet to be saved into table, field in AFRs cannot be autonumber with copy/paste. An INSERT query can force the values entered to autonumber field, which is basically what the import wizard does. I created a new field AFRsIDtemp (number type Indexed no duplicates), deleted AFRsID field, renamed AFRsIDtemp to AFRsID, copy/pasted from Excel, made copy of table structure only and set AFRsID as autonumber in new table, ran INSERT SELECT sql, deleted original table, renamed new one. Always run C&R afterward to reset the autonumber seed.

    Here is the db with data imported. Had to break table relationships. I will let you reset them. AFR System 20.3.zip
    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.

  14. #14
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thank you June I reset the relationships. My Main Form is different now it is missing the Exit Database button. Also it doesn't maintain tabs going from one form to another and the reports too.

    I have to exit the application anytime I go to another form or report.

    What happened?

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bill,

    Did you do a sample test before you ran the full import??
    Analysis, review and testing are critical parts to developing a database - or any project for that matter.

    I know I have suggested the articles at the Database Design and Planning link in my signature to you before, but I'd
    like to impress upon you once again how important planning and testing are.

    Although you said
    I was successful importing data from Excel to Access except for tables 'AFRs' and 'AFRsParts'.
    after reviewing June's efforts and results, I think you would agree that "successful" may not be the proper term for your import activity.
    Hang in, at some point, concepts and recommended techniques will overtake your current approach.

    Good luck.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-13-2015, 05:21 PM
  2. Subscript out of Range in Access 2010
    By jenngee in forum Import/Export Data
    Replies: 2
    Last Post: 11-26-2013, 10:30 AM
  3. subscript out of range
    By philfer in forum Access
    Replies: 3
    Last Post: 08-17-2013, 01:22 PM
  4. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  5. Subscript out of range (9)
    By msche398 in forum Programming
    Replies: 1
    Last Post: 07-08-2011, 12:52 PM

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