File size increases until the macro gives me error 3049(corrupt)

[tomb]

So I have an access macro which runs two vba functions 10 times. Its supposed to import a messy text file, convert it into a table, take certain columns into a query to then export it into an excel sheet. Before I run the code the file is about 300,000 KB, as it is running it gets through 8 of the imports/exports before the file goes over 2,000,000 kb and error 3049 (One more reason that makes this error happens is the file size limit is 2 GB and if you try to add data which get exceeds with 2 GB limit.) Also, I have it clear the two tables (I haven't tried clearing anything with my query tables just actual tables) during every iteration of the macro. HOW DO I FIX THIS!!!!!!!! Thank you in advance because I have no clue from here how to save this memory.

[isladogs]

You do not need to import the text files in order to process them/import to Access tables.
Instead link to them which will prevent the file bloat issue you have now.

Having said that, if the sole purpose is to export to Excel, do you need Access at all?

Assuming you do need Access, make sure you have a system for regularly backing up your database

[Beetle]

What are the details of how your application is structured? Is it split, with the back end (tables) in a separate file?

You have two potential problems here;

1) The amount of data. If you are importing large amounts of data then you may be able to solve your problem by either splitting the application (if it's not already split - hopefully it is), or, breaking the back end up into separate files. You can link to multiple different back end files, and each would have it's own 2gb limit.

2) Front end bloat due to the overhead of all your import/export processing. You solve this by doing a compact & repair.

[ssanfu]

Another option....

You could read in the text file line by line, processing each line and writing the processed line back out to a different text file.

Or use Excel to process the text file. Process the text file using VBA and write directly to a worksheet.


It would be interesting to see the dB and the "messy" text file........ care to post it?

[isladogs]

Tomb
I'm also intrigued by what these messy text files might be.
Are they by any chance JSON or XML files from online sources?
If these are JSON, I may have a better solution for you.