I'll start by saying that VB is not my first language. I've written a very basic script that queries a list of QuickBooks items for deletion, however, after about 400 deletions QuickBooks pops up an error: "Out of memory. May not complete this operation." I can click the OK button and it will continue to update the records, one by one, however, I am trying to delete about 170,000 of them so this is not an option.
I am connecting to the QuickBooks database using FlexQuarters QODBC. I have followed the steps in this article to increase the InitialCache and MaxCache settings for the QuickBooks DB SERVER, however, it seems that this only applies to the DB SERVER and not to the application cache. The settings appear in the QuickBooks information screen as follows:
File Information
Local Server Information
Those numbers are in MB, so I'm assuming the cache sizes should be more than sufficient to perform the operation without memory error. The max cache represents about 18% of the total available RAM on the machine.
I'm wondering... is there a way to modify this VB script in such a way as to clear the application cache in QuickBooks after say, 250 operations?
Private Sub Command0_Click()
Set cn = CreateObject("ADODB.Connection")
Dim rs As Recordset
Dim iCounter As Integer
Set rs = CurrentDb.OpenRecordset("SELECT ListID FROM ItemsForDeletion")
cn.ConnectionString = "DSN=QuickBooks Data QRemote"
While Not rs.EOF And iCounter < 250
cn.Open
cn.Execute ("DELETE FROM ItemInventory WHERE ListID='" & rs!ListID & "'")
cn.Close
CurrentDb.Execute ("DELETE FROM ItemsForDeletion WHERE ListID='" & rs!ListID & "'")
iCounter = iCounter + 1
rs.MoveNext
Wend
rs.Close
End Sub
Just a note, the original code included only one cn.Open and one cn.Close statement outside of the WHILE loop, but was told by an Intuit developer that this way might be less likely to produce a memory error (which obviously did not pan out).
Hoping there is a way around this. I do not wish to run this process 400 times, rebooting every time, as is currently the only way through the issue.
UPDATE
Posted this question on SuperUser and confirmed that the application cache settings in QB cannot be altered, though my machine has about 59GB of free RAM.
At this point, I'm wondering if there is any way that I a VB script can reset the application cache or limit the size to which it grows as a result of the queries presented above?
UPDATE
For those out there who are experiencing the same issue, I was able to find a "workaround", not one that solves the underlying issue, but that gets around the result of the problem expediently.
A simple macro recorder (in this case I used JitBit) to sniff for the warning message box that pops up with QB is out of memory, and force kill the QuickBooks task, reopen it, restart the QODBC driver, and reinitiate the Access query.
A hacked solution, but at least I don't need to sit a my computer for the next 80 hours doing this. Thank God for macros!
Still hoping that there is an answer to the underlying issue out there.
UPDATE
Attempted installing SQL Server on the machine, with a different version of the QODBC database driver (server driver instead of desktop driver). Initially, seemed to be running, but after further inspection the new configuration simply resulted in a memory leak that caused the "QBW32.EXE -silent" process to repeatedly overload and restart. No records were deleted.
We're essentially out of business until this issue is resolved. Please, if anyone has any thoughts on the matter, really need some help here.
The nightmare is over. After contacting every top QB specialist for assistance, and after being told that performing a database query of this size on a QuickBooks file was impossible without further corrupting our file or messing up the sync with our CRM, there was one who was able to get the job done. We're now finally in sync with the web CRM and all operations have been performed flawlessly, with all corruption eliminated from our file.
Matt Clark of QBorNotQB.com made it happen. Anyone having a similar issue, this is your only viable solution, I assure you. After MONTHS trying to get around the miniscule application cache in QuickBooks, this guy was able to do it in less than 36 hours.