Solved Subscript Out of Range error when importing back tables

[tcneo]

So I'm trying to reset my database.

So I exported most of the tables. Then I saved the database as a template and created a new database based on the template.

When I try to import the (exported) tables back in, some of the tables imported in with no issue, but some of them failed with the "Subscript Out of Range" error.

Given that I had not made any changes to the exported tables, why can't it be imported back in? Did I miss out something?

 

[arnelgp]

are there any nulls on the PK fields you are importing back?

 

[The_Doc_Man]

In what format did you export the tables?

 

[tcneo]

In what format did you export the tables?

xlsx

 

[tcneo]

are there any nulls on the PK fields you are importing back?

now that you mention it, yes. the fields for one of the columns (remarks/description) are blank

 

[arnelgp]

i don't think remarks/description field is pk?
on the table (design view) is the field remarks "required" to have a value.

 

[arnelgp]

well, I am not Savant as you are.
what I am suggesting are troubleshooting points.
there are also "recommendations" here:
"Subscript out of range" error importing EXCEL worksheet to - Microsoft Community

 

[tcneo]

I still can't figure out what is wrong.

I have attached the files here in a zip for anyone to look.

Description of files:
1) Test Planner - tables loaded. This is the original database
2) Test Planner - Blank created from template. I took file #1, saved as a template and created a new database from this template to create file#2
3) excel files. These are the excel files I exported from the Test Planner (file 1).

I can import the following excel files back in: analyst_details, instrument_type & product_table

analyst_availability and analytical_method will generate key violation errors.

instrument_table will generate "subscript out of range" error.

 

[oleronesoftwares]

Check this url

Top 6 Fixes To Solve Subscript Out Of Range Access Error

Try 6 best fixes to solve subscript out of range Access error. Fix 1: don’t put anything over the limit, fix 2: remove calculated columns, fix 3: check macros programming or VBA coding…

www.accessrepairnrecovery.com

 

[tcneo]

Check this url

Top 6 Fixes To Solve Subscript Out Of Range Access Error

Try 6 best fixes to solve subscript out of range Access error. Fix 1: don’t put anything over the limit, fix 2: remove calculated columns, fix 3: check macros programming or VBA coding…

www.accessrepairnrecovery.com

Thanks for sharing the link but it did not help with my issue.

The link mentioned the following 6 areas, i have added my comments particular to my case.

• In an Excel spreadsheet presence of an excessive number of columns.
  • I checked. no extra columns. in the first place, the excel file were exported from the database itself. i don't understand why i can import some of the tables back in but not the others.
• There could be some Excel files corruption issue occurred.
  • I checked to the best of my ability and do not know of any corruption. I have shared the files in an earlier post.
• Access is unable to translate the calculated or formatted Excel fields.
  • Similar comment to the first, the excel files were exported from the database itself. if it was a formatting issue, all tables should be affected?
• Using the disabled Macros of Excel into Access.
  • Macros not disabled.
• Making use of the tool wizards while designing queries or any other objects.
  • I think this point refers to importing queries from Excel? not applicable in this case.
• The occurrence of some error code in Macros programming or VBA coding.
  • not using macros or vba to import in this case. straight-forward attempting to import using the access import button on the ribbon.

 

[tcneo]

Update:

I managed to import the problematic tables via the csv route instead.

Looking at the csv files vs the excel, it seems like the date fields were exported in "d/m/yyyy" but access requires "d/m/yyyy h:mm:ss" in order to import, text qualifiers (") were somehow missing for text fields too.

Phew, now I can move on to fix other issues and implement more features.

Thanks everyone for helping!

 

[kevin_maher]

Thanks - I struck that error too, and it was due to fields with nothing in them (null) that had either the Required property set to Yes, or Allow Zero Length set to No without realising it, which may be defaults for Access. Those fields in my table were ones that I could afford to have blank for a given record.

To fix that, right click on the table, choose Design View then look for those fields in the Field Properties which should be showing below the list of fields, and change say Allow Zero Length to Yes. I was then able to import the ones that it refused after that with no problems.

 

[The_Doc_Man]

Thanks - I struck that error too and it was due to fields with nothing in them (null) which I had set to Required or Allow Zero Length without realising it, which may be defaults for Access. To fix that, right click on the table, choose Design View then look for those fields in the Field Properties which should be showing below the list of fields. I was then able to import the ones that it refused after that with no problems.

Thank you for sharing a relevant solution! Our other viewers who have this problem now have confirmation of a potential solution from multiple members with similar problems.