"Search key was not found in any record" error while importing large .txt

[gurtonj]

I've read in many of the posts of the possible reasons why I'd get this error.
I've created these auditor databases for several years. I download the data in .txt format and import it into Access for reconciling purposes. I've been able to work through most of the issues that I run into, including different networks and one-drive sync problems because the databases are all over 2GB (once I get the lines to import).

There are over 2M (1.4GB) lines of data. I need to bring in the dates as "short text" because there is no time and the import won't bring it in.
I've tried .html, .csv and .txt each getting the same result.

The number of fields and header data has been the same with no changes, so that shouldn't be the issue.

Any suggestions would be appreciated.
Thank you, in advance

 

[theDBguy]

If you're not getting this error from all the different text files you import, then the problem is more than likely with that specific text file.

 

[The_Doc_Man]

This article seems to be related and has some links in it that might be worth exploring.

MS-access reports - The search key was not found in any record - on save

Occasionally my MS Access reports: The search key was not found in any record After this happens the solution is to close Access, compact and repair the backend and then delete the record. What

stackoverflow.com

More than one potential cause is noted. One of them might be the source of your issue.

It should also be noted that if you really think you will have >2 GB of data in a single file, Access is probably not the best tool in the toolbox. Any of several SQL engines (MySQL, SQLLite, SQL Server, ORACLE, ...) can handle larger data amounts. Access is going to top out at a file reaching 2 GB minus some overhead for table definition and field definition structures. If you had split tables, it is possible to have multiple container files that can hold up to 2 GB each but if something is a single-table file, 2 GB is your hard limit.

 

[gemma-the-husky]

That error is always symptomatic of a data corruption to me.

Maybe the data in the import file is badly formed, so that certain rows can't be split into fields correctly.

What are you trying to do with the data? Import to a new table or to an existing table?

 

[ebs17]

I would first ask what significance a search key has in the context of an import. There is nothing to search for in a pure data transfer.
If you carry out specific actions, these should be described in more detail.

 

[arnelgp]

Any suggestions would be appreciated.

can you not just make a Linked table out of the text file?

 

[gurtonj]

I was able to bring in the data, just not all of the columns. Now I just need to convince the auditors that it's acceptable.

Thank you for the suggestions. Looking into a different method for next year.

 

[gemma-the-husky]

As I said, there's something In some text fields that the import wizard doesn't like. If you can identify the rows that fail you might be able to see the issue by inspection. Depends how many records you need to check.

 

[gemma-the-husky]

Just a thought. There was a thread recently about text conversions, that highlighted that VBA strings are different to some other strings. It might be that vba is causing the errors by the way it treats the strings.

I'm not sure how to explain this in technical language, but it might offer an explanation.