I went a differs route :
I was hoping MemoryUsage would be useful. It wasn't, apparently...
I run a vba script that goes through multiple files (since access cannot handle anything too large); and append them to a table, transform it and then spit out a summary.
The script loops through files and runs macros against each of them.
The quick answer is to pull the memory usage from the task manager and then if it exceeds 1 GB; pause the subroutine so no corrupt records get in.
How do we do this?
Insert this memory usage Function with the readfile function.
You will need to create an if statement in your code that says:
dim memory as long
memory = memory_usage
' 1000000 ~ 1 GB
If memory > 1000000 then
End Sub
end if
=================================================
[path to file] = "C:\….\ShellOutputfile.txt"
Function Memory_Usage() as Long
Dim lines As Long
Dim linestring As String
Shell "tasklist /fi " & """IMAGENAME EQ MSACCESS.EXE""" & ">" & """[path to file]"""
'get_list_data
lines = CInt(get_listing_data("[path to file]", 1, 0))
linestring = get_listing_data("[path to file]", 2, 4)
linestring = Right(linestring, 11)
linestring = Replace(linestring, " K", "") ' K
linestring = Replace(linestring, " ", "")
lines = CLng(linestring)
Memory_Usage = lines
End Function
=============================
Public Function get_listing_data(PATH As String, Choice As Integer, typeofreading As Integer) As String
' parse in the variable, of which value you need.
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim tmp_var_str As String
Dim fso, ts, fileObj, filename
Dim textline As String
Dim tmp_result As String
Dim TMP_PATH As String
Dim tmpchoice As Integer
Dim tor As Integer
Dim counter As Integer
' type of reading determines what loop is used
' type of reading = 0; to bypass; > 0, you are choosing a line to read.
counter = 0
TMP_PATH = PATH
tmp_var_str = var_str
tmp_result = ""
tor = typeofreading
' choice = 1 (count the lines)
' choice = 2 (read a specific line)
tmpchoice = Choice
' Create the file, and obtain a file object for the file.
If Right(PATH, 1) = "\" Then TMP_PATH = Left(PATH, Len(PATH) - 1)
filename = TMP_PATH '& "\Profit_Recognition.ini"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fileObj = fso.GetFile(filename)
' Open a text stream for output.
Set ts = fileObj.OpenAsTextStream(ForReading, TristateUseDefault)
Do While ts.AtEndOfStream <> True
If tmpchoice = 1 Then
counter = counter + 1
textline = ts.ReadLine
tmp_result = CStr(counter)
End If
If tmpchoice = 2 Then
counter = counter + 1
tmp_result = ts.ReadLine
If counter = tor Then
Exit Do
End If
End If
Loop
get_listing_data = tmp_result
End Function