Schalton Schalton - 1 year ago 56
SQL Question

Clearing Dictionary From Ram

MS Access 2013

My goal is to build a crosswalk table, but I cannot join on a Memo/Long Text field and using an update query with a where statement instead of the join is inefficient and hangs, so I decided to build a dictionary populate it, and fill the next column by looking up the ID, problem is I run into "Overflow" issues - my presumption is a RAM limitation. My work around was to fill as much as I could into the dictionary, populate what I could from that subset, clear the dictionary and fill it again with the next batch - rinse wash repeat. However, after the first batch is run and cleared from the dictionary I'm getting the Overflow error on the next Key/Value pair I'm trying to place into the 'empty' dictionary -- and I don't know why.

Ideal JOIN SQL Statement (With Long Text / Memo issue):

UPDATE [ID Crosswalk] INNER JOIN [1 Clean Reasons] ON [ID Crosswalk].Reason = [1 Clean Reasons].Reason SET [ID Crosswalk].CR_ID = [1 Clean Reasons].[Reason];

WHERE SQL Statement (Non-functioning/Hanging):

UPDATE [ID Crosswalk], [1 Clean Reasons] SET [ID Crosswalk].CR_ID = [1 Clean Reasons].[Reason]
WHERE ((([ID Crosswalk].Reason)=[1 Clean Reasons].[Reason]));

VBA Dictionary Solution (Overflow error):

Dim CRtbl as DAO.Recordset
Dim CRDictNT as Dictionary
Set CRDictNT = New Dictionary

'Fill CR Dict
On Error GoTo PrintCR
Do Until CRtbl.EOF
Do Until CRtbl.EOF
CRDictNT.Add CStr(CRtbl("Reason")), CInt(CRtbl("CR_ID")) 'CR_ID by Reason
Do Until CWIDtbl.EOF
If Not IsEmpty(CRDictNT(CWIDtbl("Reason"))) Then
CWIDtbl("CR_ID") = CRDictNT(CWIDtbl("Reason"))
End If
CRDictNT.RemoveAll 'these three lines are
Set CRDictNT = Nothing 'my attempts at clearing
Set CRDictNT = New Dictionary 'the item from RAM.

Answer Source

The issue isn't your Dictionary, it's the cast you're doing. VBA Integers are small (signed 16-bit with max value 32,767). So when you call CInt(CRtbl("CR_ID")) with an ID bigger than that, you're overflowing the integer it tries to return. Use CLng() instead to cast to a Long (signed 32-bit with max value 2,147,483,647). Make sure you also refactor the rest of your code to correctly expect a Long.

See our VBA Type documentation for details on the different types.

I just did a little test leaving a Dictionary just running for a while, adding items (key was a short string, value was an incrementing Long, similar to your situation). It ran to 3 million entries before I cut it off, and it wasn't complaining about any overflows or other errors (and the total memory usage of the host process was just scraping ~0.5GB). So you should probably be fine to fill your Dictionary as big as you like without worrying about memory.