none
VBA Run-time Error RRS feed

  • Question

  • Run-time Error 3021: No Current Record

    I get this error while I try to Import Excel file in one of the MS Access tables using VBA. Attaching screenshot, how to solve this please??
    Just to be clear, I've made sure "tblBoQ" table and "C:\Import\BoQ" Excel files are at place. So there's no question about it.

    Thanks.

    Sunday, November 1, 2015 10:48 AM

Answers

  • >>>...but now when I split the table in a FE/BE scenario, it gives me below error. Why is it so please??

    According to your description, I have reproduced this issue. Regarding this issue, based on my research, two main reasons for the occurrence of the above error message are discussed below:

    1. Appending of the records that were the primary key values

    2. Violating referential integrity rules for a relationship defined between tables

    You could use Get External Data wizard to import Excel file, after you have completed the Import wizard, Access notifies you of any problems that might have occurred during the import process. In some cases, Access might create a new table called ImportErrors, which contains any data that it was unable to import successfully. You can examine the data in this table to try to find out why the data did not import correctly.

    For more information, click here to refer about Introduction to importing and exporting data

    • Marked as answer by KhurramKZ Friday, November 6, 2015 4:05 AM
    Tuesday, November 3, 2015 8:49 AM

All replies

  • Hello Khurram,

    Can you post the whole code? Just want to look at the declarations you have in the beginning of the code.

    Which version of Access are you using? We need to know the version for it might be references on the visual basic libraries that cause the issues.

    Sincerely,

    Ed


    Dr. Mindweb

    Sunday, November 1, 2015 11:55 AM
  • Interesting.. I would expect a different error. Cause C:\Import\BoQ looks like a path and not a file... do you mean C:\Import\BoQ.xlsx ?
    Sunday, November 1, 2015 1:00 PM
  • Thank you. Posting the full portion of VBA below. I'm using Access 2010.

    BoQ is a file name in path C:\Import\BoQ. Yes, it is C:\Import\BoQ.xlsx and i've tried both ways, with and without .xlsx extension and both end up with the same error.

    Does this explain enough?? Am I missing libraries something, or what is wrong please?

    Thanks.


    • Edited by KhurramKZ Monday, November 2, 2015 2:37 AM
    Monday, November 2, 2015 2:36 AM
  • Hello KhuramKZ,

    I may suggest to do the following and let me know the error message that will pop up, if there are any.

    1. Compile your code. Open VBE -> Debug -> Compile

    2. Compact and Repair your database. File -> Info -> Compact and Repair

    Once you completed above, press the btnImportBoq again. If you get the same error, try the following:

    1. Replace the code with:

    DoCmd.TransferSpreadsheet acImport, , "tblBoQ", "C:\Import\BoQ.xlsx", True

    2. Replace the code with:

    DoCmd.TransferSpreadsheet acImport, , "tblBoQ1", "C:\Import\BoQ.xlsx"

    Let me know what happen when you did 1 and 2.

    Sincerely,

    Ed


    Dr. Mindweb

    Monday, November 2, 2015 5:35 AM
  • Thank you Dr. Mindweb. After Compile, Compact and Repair and then changing HasFieldNames to True as in your first replacement code works well with a single Database. Thank you.

    ...but now when I split the table in a FE/BE scenario, it gives me below error. Why is it so please??

    K

    Monday, November 2, 2015 11:38 AM
  • >>>...but now when I split the table in a FE/BE scenario, it gives me below error. Why is it so please??

    According to your description, I have reproduced this issue. Regarding this issue, based on my research, two main reasons for the occurrence of the above error message are discussed below:

    1. Appending of the records that were the primary key values

    2. Violating referential integrity rules for a relationship defined between tables

    You could use Get External Data wizard to import Excel file, after you have completed the Import wizard, Access notifies you of any problems that might have occurred during the import process. In some cases, Access might create a new table called ImportErrors, which contains any data that it was unable to import successfully. You can examine the data in this table to try to find out why the data did not import correctly.

    For more information, click here to refer about Introduction to importing and exporting data

    • Marked as answer by KhurramKZ Friday, November 6, 2015 4:05 AM
    Tuesday, November 3, 2015 8:49 AM
  • Thanks you, i was violating the referential integrity rules. Figured it out.
    Friday, November 6, 2015 4:05 AM