How to get values from select statement into a variable in VBA

Solution 1:

result = rst!result

This notation is magical shorthand for this:

result = rst.Fields("result").Value

And your query doesn't have any result field. So either you change the query to alias Key_Old to result:

strSQL = "SELECT Key_Old As result FROM Pivot_Old where Pivot_Old.Count = " & 1 & ""
...
result = rst.Fields("result").Value

Or, you change how you're referring to that field:

strSQL = "SELECT Key_Old FROM Pivot_Old where Pivot_Old.Count = " & 1 & ""
...
result = rst.Fields("Key_Old").Value

Rule of thumb, prefer explicit code over magical shorthand notation =)