CODEWITHSUNDEEP
vbamemoryodbc
Divakar Ragupathy
Divakar Ragupathy

Reputation: 53

Memory error when running VBA

I am running an excel macro which pulls some data from a oracle database and display in a table format.

The macro runs fine for few minutes and then comes up with the below error:

"Microsoft ODBC Oracle can not allocate sufficient memory for proper operation. Please free up some memory and retry operation"

Please note that I am connecting to the database using DSN settings for Mirosoft ODBC for Oracle. Also please note that this macro was running fine XP with Oracle 10g client. I am facing this issue in Windows 7 (32 bit) with Oracle 11g client.

Any help on this will be greatly appreciated.

Code:

Sub Refresh_Tables()

Dim intHour_of_Day As Variant

Sheets("Live Screen").Select
Range("A1").Select

Application.ScreenUpdating = False



Sheets("Data2").Select
Range("Hour_of_Day").Select
intHour_of_Day = ActiveCell.Value

'MsgBox ("1")

Sheets("Data").Select
Range("G4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Sheets("Data").Select
Range("U4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

 'MsgBox ("2")

Sheets("Data").Select
Range("A25").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


 'MsgBox ("3")
 Application.ScreenUpdating = False

If intHour_of_Day < 11 Then

     ' Refresh all Tables

Sheets("Data2").Select
Range("B10").Select

'MsgBox ("4")

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("E10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("H10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("K10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("N10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False



Sheets("Data2").Select

'MsgBox ("5")

Range("Q9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("T9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("W9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("Z9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("AC9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

  ElseIf intHour_of_Day >= 11 And intHour_of_Day < 13 Then

Sheets("Data2").Select
Range("E10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("H10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("K10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("N10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Sheets("Data2").Select
Range("T9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("W9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("Z9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("AC9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

  ElseIf intHour_of_Day >= 13 And intHour_of_Day < 15 Then

Sheets("Data2").Select
Range("H10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("K10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("N10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Sheets("Data2").Select
Range("W9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("Z9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("AC9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


  ElseIf intHour_of_Day >= 15 And intHour_of_Day < 17 Then

  Sheets("Data2").Select
Range("K10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("N10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Sheets("Data2").Select
Range("Z9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("AC9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Else

'Update Running Total Tables

Sheets("Data2").Select
Range("N10").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Range("AC9").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

  End If


Sheets("Live Screen").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 574

Answers (1)

Divakar Ragupathy
Divakar Ragupathy

Reputation: 53

Issue resolved after uninstall windows 7 32 bit and install windows 7 64 bit

Upvotes: 1

Related Questions