"Run-time error '-2147024882 (8007000e)': Out of memory." while building ADO recordset

[Galaxiom]

G, if you look at the extraction code described in post #1 and in particular look carefully at the starting position and size of field N, then the starting position of field N+1, there is no apparent delimiter so STRING_SPLIT() might not work here.

I only use STRING_SPLIT to divide the lines into separate records because the original records I work with have a whole text file in a single record. OP doesn't require this step because the records are already separated in the source recordset.

Clearly it wouldn't be useful for splitting fixed width data with no delimiters.

BTW One must take care with SPLIT_STRING because, despite being numbered, the order of the output is not guaranteed to match the input. In my case I am not concerned about the order of the lines so it is a perfect solution.

 

[vhung]

Is there a way or chances to develop/improve the MSAccess specially to increase the memory for splitting tables for at least 5GB, including field size capacity...

 

[Galaxiom]

Is there a way or chances to develop/improve the MSAccess specially to increase the memory for splitting tables for at least 5GB, including field size capacity...

Not going to happen. The free SQL Server Express is the next step as a back end. It supports 12GB.

The huge advantages of SQL Server are more than worth the learning curve and developers who make the jump don't often revert to Access backends except for very simple applications.

Microsoft also provides the full version of SQL Server to developers for next to nothing but woe betide anyone who installs it on a client's system without a proper licence.

 

[vhung]

Not going to happen. The free SQL Server Express is the next step as a back end. It supports 12GB.

The huge advantages of SQL Server are more than worth the learning curve and developers who make the jump don't often revert to Access backends except for very simple applications.

Microsoft also provides the full version of SQL Server to developers for next to nothing but woe betide anyone who installs it on a client's system without a proper licence.

well;
>sql is there but it was different compare to same langauage as MSAccess...
i already used MSSQL but i was not satified on the long run operation
so i remain on Accdb split database as 2 files use during operation...
>but anyway if my split link source data is full i could add another Accde link source data
but not sure if it works if i arrived there...

 

[The_Doc_Man]

@vhung - It appears that MS won't do anything to expand the address space of Access like they did for Excel and Word because of the incredible number of address pointers that would have to be adjusted. Further, it would break the ability to use a file created on 32-bit on a 64-bit system - and vice versa. EVERYTHING inside Access is based on (32-bit) address pointers. I have to think of it as a daunting task.

 

[vhung]

@vhung - It appears that MS won't do anything to expand the address space of Access like they did for Excel and Word because of the incredible number of address pointers that would have to be adjusted. Further, it would break the ability to use a file created on 32-bit on a 64-bit system - and vice versa. EVERYTHING inside Access is based on (32-bit) address pointers. I have to think of it as a daunting task.

feels;
>sad with it, why this be, i learn to love Accdb but still i can't fully access it
guess right as time goes in a year many things to be done
then problems maybe have solutions...
>but then i still hope that MSAccess should be developed as we users needed it
why have they created this for us and then make us feel hopeless...
>look how far i've gone then something's block my drive way...

 

[Isaac]

feels;
>sad with it, why this be, i learn to love Accdb but still i can't fully access it
guess right as time goes in a year many things to be done
then problems maybe have solutions...
>but then i still hope that MSAccess should be developed as we users needed it
why have they created this for us and then make us feel hopeless...
>look how far i've gone then somethings block my drive way...

WTF ?

 

[Galaxiom]

sql is there but it was different compare to same langauage as MSAccess...
i already used MSSQL but i was not satified on the long run operation
so i remain on Accdb split database as 2 files use during operation...

SQL Server's T-SQL is a vastly superior language to Access SQL in every conceivable way.

However you need to understand that SQL Server is not a complete substitute for Access. It is an enhanced substitute for the back end.
You can still use Access as the front end with tables linked to SQL Server via ODBC. It isn't a huge step to take and comes with many potential advantages.

but anyway if my split link source data is full i could add another Accde link source data
but not sure if it works if i arrived there...

The first you will know when you arrive there is the backend database corrupts. Access cannot deal with it to do a repair so you will have no choice but to restore from a backup. Then you will be faced with the complexity of managing two backends. It is not a good solution.

>sad with it, why this be, i learn to love Accdb but still i can't fully access it
guess right as time goes in a year many things to be done
then problems maybe have solutions...
but then i still hope that MSAccess should be developed as we users needed it
why have they created this for us and then make us feel hopeless...

Microsoft has stated they will not do this so don't waste you time hoping. Users don't need it.
The SQL Server Express product is their solution to larger databases.
That is why there is an Upsize to SQL Server wizard in Access.

look how far i've gone then somethings block my drive way...

Did you think the same of your childhood tricycle when you wanted to ride further?
Do you still lament its inadequacy or did you graduate to a bicycle?

 

[vhung]

SQL Server's T-SQL is a vastly superior language to Access SQL in every conceivable way.

However you need to understand that SQL Server is not a complete substitute for Access. It is an enhanced substitute for the back end.
You can still use Access as the front end with tables linked to SQL Server via ODBC. It isn't a huge step to take and comes with many potential advantages.


The first you will know when you arrive there is the backend database corrupts. Access cannot deal with it to do a repair so you will have no choice but to restore from a backup. Then you will be faced with the complexity of managing two backends. It is not a good solution.



Microsoft has stated they will not do this so don't waste you time hoping. Users don't need it.
The SQL Server Express product is their solution to larger databases.
That is why there is an Upsize to SQL Server wizard in Access.


Did you think the same of your childhood tricycle when you wanted to ride further?
Do you still lament its inadequacy or did you graduate to a bicycle?

>i have to face what i need to
as long as AWF is here it will help me guide on my way...
>this could be my home
and Accdb is been my partner for years on my daily works...
>but we know that we seek for the best result...

 

[Mike_10]

Wow you guys are giving me lots to study! Much appreciated.

@The_Doc_Man here are the results from your test: Access memory started at 13.7 MB and after starting the procedure slowly walked up finally stopping at 964.9 MB when the out of memory error occurred. This process is consistently taking about 45 seconds.

@isladogs thanks for fixing that, I don't even know what code tags are. I played with the "Toggle BB code" and thought I was doing it right, but now that I look at all the options that it makes available I see a "</>" icon that I think you're referring to. I'm guessing I'm supposed to click that before typing any code? Sorry, I'm totally new to this.

@arnelgp I actually did consider this a while back. The reason I chose not to was more out of wanting to force myself to do this whole thing through code in an attempt to learn, treating it as partly an academic project (even though this is a real life problem I'm trying to solve). I wanted to see if I could get it down so far as click one button and walk away. With that said, I have done exactly as you suggested, took me a while to work through this one. I set up an import template for all the field names and string lengths. Then I converted the ADO MasterRS to a DAO and opened the imported table into this recordset (just so I could test right now without having to rewrite the entire rest of my code). After that I had to go through my code and work out all the new syntax errors that didn't jive with the DAO rules. But it worked using my smallest sample file; slight caveat, the output values are only being produced for the first record, no worries, I'll go through my code later and fix this when I have more time. The important part is I have a running code now. So then I ramped up to the full sized file, which took only 55 sec to import. Then I ran the code once more and WAA LAA!!! It runs to completion!!! And only took 2 min 31 sec! I'm fairly confident that this time will still stand after I work out my new little bug with the data output because it looks like it's still running through all the loops and everything else normally. So let me finish tweaking this and I'll report back on the final time. This was a huge concern of mine, and I was just hoping to keep it under 30 min, imagine how excited I am now!

@Isaac done, both are now DAO

@CJ_London I will look into this later when I have more time, I would like to learn more about this idea.

@Galaxiom same comment, I'll have to look at this later when I have more time.

Thank you all once again!

 

[arnelgp]

you can also create an Import/Export Spec since you have "fixed width" of each Column to extract.
save the Import spec, and the next time you need to import just use the Import spec.
the import spec save those column definitions.

Import spec can be created when you use Get External data from ribbon and save the Steps (definition of columns and column types).

 

[The_Doc_Man]

OK, though I didn't expect it to stop at 1 GB, nonetheless you report that the working set went from 13.7 to 964.9 over a period of time less than a minute. Which means you were badly stressing user memory. But then, I should have guessed that virtual address space was ALSO being chewed up with other things. So you didn't have a full 2 GB available because of other issues.

I think my earlier advice (which parallels what others are saying) is your best bet. Build an import spec to manage your fixed-column input and send it to a temporary table . Look at ArnelGP's advice in #31 (above). I won't repeat it 'cause he said what I would have said 30 minutes ago if I had been online then.

 

[Mike_10]

@arnelgp Yes this is exactly what I did, I set up the import spec and saved it, that's what I was referring to when I said "import template." In addition to that, right after I sent that post I remembered seeing, at one time, a way to code the import process, so I'll work on automating these steps later.

@The_Doc_Man Good point, I did have several other things open, many of which were using more memory than Access was, starting with Microsoft Edge at the top with around 1,100 MB, followed by Microsoft Teams at around 700 MB, so on and so forth.

 

[The_Doc_Man]

Actually, not what I meant. There is a performance tab on the Task Manager and a link to launch Resource Monitor. You could use those to see if you ran out of memory, with the understanding that there is such a thing as the virtual memory file (controlled from Control Panel >> System >> Performance or some similar path). If you have a page/swap file at least the size of physical memory, most of those things in memory with Access will be paged out or swapped out. (Task Manager will perhaps show you that at least indirectly as tasks appear to shrink in memory size.) I'll avoid that digression since this isn't a discussion of Windows Paging Dynamics. What was more important was that your working set ballooned until it approached a "round number" - 1 GB - and then went awry.

I think I see a consensus among the responders that using in-memory recordsets in a memory-intensive, heavy-usage situation is probably not the best course of action. If you get some success with the ImportText operation (or if you DON'T) please let us know.

 

[Mike_10]

Actually, not what I meant. There is a performance tab on the Task Manager and a link to launch Resource Monitor. You could use those to see if you ran out of memory, with the understanding that there is such a thing as the virtual memory file (controlled from Control Panel >> System >> Performance or some similar path). If you have a page/swap file at least the size of physical memory, most of those things in memory with Access will be paged out or swapped out. (Task Manager will perhaps show you that at least indirectly as tasks appear to shrink in memory size.) I'll avoid that digression since this isn't a discussion of Windows Paging Dynamics. What was more important was that your working set ballooned until it approached a "round number" - 1 GB - and then went awry.

I think I see a consensus among the responders that using in-memory recordsets in a memory-intensive, heavy-usage situation is probably not the best course of action. If you get some success with the ImportText operation (or if you DON'T) please let us know.

Gotcha, my misunderstanding. I found what you were referring to now.

Will do, as soon as I get caught up.

 

[James Dickinson]

instead of the rs addnew change this to an individual insert. this way you wont have two open recordsets which will bloat your memory usage

 

[Mike_10]

Just to check my understanding here, it is a correct assumption to say that when the table is built via import as per arnelgp's suggestion vs. as an in-memory recordset, that it stores it into ROM leaving my RAM more freed up?

 

[The_Doc_Man]

Not ROM but in the .MDB/.ACCDB file, which is actually a virtual array file that acts as a local page/swap file for Access itself.

The actual data flow is hard to be sure because Microsoft hasn't published a lot about Access internals, but the flow is probably a loop that reads a record, uses the Import Specs to break it apart, and stores a record one at a time. So the table DOES go through memory but only a record at a time. Using the ADODB is what makes the entire import try to fit into memory at once.