By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Access out of memory on looped code

100+
P: 157
Hello!

I have code wich scans the through 10 computers on a LAN for files and import them into different tabels. This code aproximitly imports 10 000 files (with 1 to 20 rows of info) a day and is running looped (i have 'do' and 'loop' in the top and bottom of the code) and have to be started and stopped manually.

Because i want the files to be imported almost instantly when they are created i let the code just loop and go through as fast as the computer manages. But after about 10 000 - 12 000 loops the application starts to act weird. The code stops with error that it is out of memory.

Do you have any experience with this kind of import? Maybe i should do i another way.



Here is the code, if required
Expand|Select|Wrap|Line Numbers
  1. Dim Maskinplassering As String
  2. Dim MaskinNr As Long
  3. Dim Path As String
  4. Do
  5.  
  6. Dim rst As New ADODB.Recordset
  7.     rst.Open "SELECT * from [Maskinoversikt] where aktiv =-1", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  8.  
  9.     If rst.EOF And rst.BOF Then
  10.     MsgBox "Finner ikke maskin!"
  11.     Else
  12.     Do Until rst.EOF
  13.     Maskinplassering = rst!Maskinplassering
  14.     MaskinNr = rst!MaskinNr
  15.     Path = rst!Path
  16.     Call Hent_fra_atf(MaskinNr, Maskinplassering, Path)
  17.     rst.MoveNext
  18.     Loop
  19.  
  20.     End If
  21.     rst.Close
  22. Set rst = Nothing
  23.  
  24. Loop
And the Module
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function Overfør_fil_r(PathName As String, MaskinNr As Long, Maskinplassering As String)
  4.  
  5. Dim oFSO As New FileSystemObject
  6. Dim oFS
  7.  
  8. Dim Conveyor As String
  9. Dim Varenr As Long
  10. Dim Batch As String
  11. Dim Anttab As Long
  12. Dim Flyttet_tid As Date
  13. Dim Pasientnavn As String
  14. Dim Kundegruppe As String
  15.  
  16. Dim rst As New ADODB.Recordset
  17. Dim RstHist As New ADODB.Recordset
  18.  
  19. Set oFS = oFSO.OpenTextFile(PathName)
  20. RstHist.Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  21.  
  22. Do Until oFS.AtEndOfStream
  23.     stext = oFS.ReadLine
  24.     If Not Len(stext) = 62 Then
  25.     Pasientnavn = Left(stext, 20)
  26.     Kundegruppe = Mid(stext, 35, 6)
  27.     Else
  28.     Conveyor = Left(stext, 1)
  29.     Varenr = Mid(stext, 2, 6)
  30.     Batch = Mid(stext, 17, 15)
  31.     Anttab = Mid(stext, 32, 3) & "," & Mid(stext, 36, 3)
  32.     Flyttet_tid = GetCreateDate(PathName)
  33.  
  34.     With rst
  35.         If Not Conveyor = "C" Then
  36.         .Open "SELECT * from [Lok Loksummer] where varenr=" & Varenr & " AND lokasjon Like '" & Maskinplassering & " ATF " & MaskinNr & " MDK%'", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  37.         If .EOF And .BOF Then
  38.         .AddNew
  39.         !Varenr = Varenr
  40.         !lokasjon = Maskinplassering & " ATF " & MaskinNr & " MDK"
  41.         ![antall tabletter] = -Anttab
  42.         Else
  43.         ![antall tabletter] = ![antall tabletter] - Anttab
  44.         End If
  45.         .Update
  46.         .Close
  47.         End If
  48.         RstHist.AddNew
  49.         RstHist!Conveyor = Conveyor
  50.         RstHist!Pasient = Pasientnavn
  51.         RstHist!Kundegruppe = Kundegruppe
  52.         RstHist!hendelse = "Batchpakking"
  53.         RstHist!Varenr = Varenr
  54.         RstHist![antall tabletter] = Anttab
  55.         RstHist![fra lokasjon] = Maskinplassering & " ATF " & MaskinNr
  56.         RstHist![til lokasjon] = "Til pose"
  57.         RstHist![flyttet av] = "ATF"
  58.         RstHist![flyttet tid] = Flyttet_tid
  59.         RstHist.Update
  60.  
  61.     End With
  62.     End If
  63. Loop
  64.  
  65.     RstHist.Close
  66.     Set rst = Nothing
  67. Set oFSO = Nothing
  68.  
  69.  
  70. End Function
  71.  
  72.  
  73. Public Function Overfør_fil_f(PathName As String, MaskinNr As Long, Maskinplassering As String)
  74.  
  75. Dim rst As New ADODB.Recordset
  76.  
  77. Dim Varenr As String
  78. Dim Varenr2 As String
  79. Dim Varenrbruk As Long
  80. Dim Overskudd As Long
  81. Dim behandlingstype As String
  82. Dim Cellelok As String
  83. Dim BoksBatch As String
  84. Dim Kassett As String
  85. Dim Bruker As String
  86. Dim Antall As String
  87. Dim Flyttet_tid As Date
  88.  
  89. Dim oFSO As New FileSystemObject
  90. Dim oFS
  91. Set oFS = oFSO.OpenTextFile(PathName)
  92.  
  93. Do Until oFS.AtEndOfStream
  94. stext = oFS.ReadLine
  95.  
  96. Kassett = Mid(stext, 2, 3)
  97. Varenr = Mid(stext, 5, 6)
  98. Bruker = Mid(stext, 28, 3)
  99. Batch = Mid(stext, 31, 15)
  100. Antall = Mid(stext, 46, 7)
  101. Varenr2 = Mid(stext, 53, 6)
  102. Flyttet_tid = GetCreateDate(PathName)
  103.  
  104. If Varern1 <> Varenr2 Then
  105.     Varenrbruk = Varenr2
  106.     Else
  107.     Varenrbruk = varenr1
  108. End If
  109.  
  110.  
  111.  
  112.  
  113.     With rst
  114.         If Left(Batch, 3) = "MDB" Then
  115.         .Open "SELECT * from [TBB Tablettboks] where boksid='" & Batch & "'", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  116.         behandlingstype = !behandlingstype
  117.         BoksBatch = !Batch
  118.         Cellelok = ![tilhører lokasjon]
  119.         .Close
  120.         Else
  121.         behandlingstype = Finn_behandlingstype(Varenrbruk, Maskinplassering)
  122.         Cellelok = Finn_lokasjon(Varenrbruk, Maskinplassering, behandlingstype)
  123.         End If
  124.  
  125.         .Open "SELECT * from [LOK Loksummer] where varenr=" & Varenrbruk & " AND lokasjon ='" & Cellelok & "'", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  126.             If .EOF And .BOF Then
  127.             .AddNew
  128.             !Varenr = Varenrbruk
  129.             ![antall tabletter] = -(Left(Antall, 4) & "," & Right(Antall, 2))
  130.             !lokasjon = Cellelok
  131.             Else
  132.             ![antall tabletter] = ![antall tabletter] - (Left(Antall, 4) & "," & Right(Antall, 2))
  133.             .Update
  134.             End If
  135.             .Update
  136.         .Close
  137.  
  138.         .Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  139.         .AddNew
  140.         !hendelse = "Maskinpåfylling"
  141.         !Varenr = Varenrbruk
  142.         ![antall tabletter] = Left(Antall, 4) & "," & Right(Antall, 2)
  143.         If Left(Batch, 3) = "MDB" Then
  144.             !boksid = Batch
  145.             !Batch = BoksBatch
  146.             Else
  147.             !Batch = Batch
  148.         End If
  149.         ![behandlingstype] = behandlingstype
  150.         ![fra lokasjon] = Cellelok
  151.         ![til lokasjon] = Maskinplassering & " " & MaskinNr
  152.         ![flyttet av] = Bruker
  153.         ![flyttet tid] = Flyttet_tid
  154.         .Update
  155.         .Close
  156.  
  157.         If IsNumeric(Kassett) Then
  158.             .Open "SELECT * from [LOK Loksummer] where varenr=" & Varenr & " AND lokasjon ='" & Maskinplassering & " " & MaskinNr & " MDK " & Kassett & "'", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  159.             If .EOF And .BOF Then
  160.             .AddNew
  161.             !Kassett = Kassett
  162.             !Varenr = Varenrbruk
  163.             ![antall tabletter] = ![antall tabletter] + Left(Antall, 4) & "," & Right(Antall, 2)
  164.             !lokasjon = Maskinplassering & " " & MaskinNr & " MDK " & Kassett
  165.             Else
  166.             Overskudd = ![antall tabletter]
  167.             ![antall tabletter] = Left(Antall, 4) & "," & Right(Antall, 2)
  168.             .Update
  169.             End If
  170.             .Update
  171.             .Close
  172.         End If
  173.         If Not Overskudd = 0 Then
  174.             .Open "SELECT * from [LOK Loksummer] where varenr=" & Varenr & " AND lokasjon ='" & Maskinplassering & " " & MaskinNr & " Maskindiff" & "'", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  175.             If .EOF And .BOF Then
  176.             .AddNew
  177.             !Varenr = Varenrbruk
  178.             ![antall tabletter] = Overskudd
  179.             !lokasjon = Maskinplassering & " " & MaskinNr & " Maskindiff"
  180.             Else
  181.             ![antall tabletter] = ![antall tabletter] + Overskudd
  182.             End If
  183.             .Update
  184.             .Close
  185.  
  186.             .Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  187.             .AddNew
  188.             !hendelse = "Maskinpåfylling overskudd"
  189.             !Varenr = Varenrbruk
  190.             ![antall tabletter] = Overskudd
  191.             ![fra lokasjon] = Maskinplassering & " " & MaskinNr
  192.             ![til lokasjon] = Cellelok
  193.             ![flyttet av] = Bruker
  194.             ![flyttet tid] = Flyttet_tid
  195.             .Update
  196.             .Close
  197.         End If
  198.  
  199.  
  200.  
  201.     End With
  202. Loop
  203.  
  204.  
  205. Set rst = Nothing
  206.  
  207.  
  208. End Function
  209.  
  210. Public Function GetCreateDate(Path As String) As Date
  211.     Dim fso As Scripting.FileSystemObject
  212.     Dim fsoFile As Scripting.File
  213.     Set fso = New Scripting.FileSystemObject
  214.     Set fsoFile = fso.GetFile(Path)
  215.     GetCreateDate = fsoFile.DateCreated
  216. End Function
  217.  
  218.  
  219. Function Hent_fra_atf(MaskinNr As Long, Maskinplassering As String, Path As String)
  220.  
  221. Dim fsoFileSearch As FileSearch
  222. Set fsoFileSearch = Application.FileSearch
  223. With fsoFileSearch
  224.  
  225. Dim FoundFiles As Long
  226. Dim Cellelok As String
  227.  
  228. Dim i As Long
  229. Dim Tid As Date
  230. Dim Tidbrukt As Date
  231. Dim Tiddiff As Long
  232. Dim Stnr As Long
  233.  
  234. Call Fremdriftsindikator_v2("ja", , , "Initierer overførsel fra maskin " & MaskinNr)
  235.  
  236. Dim rst As New ADODB.Recordset
  237.     rst.Open "SELECT * from [ATF oppdateringslogg]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  238.     rst.AddNew
  239.     rst!maskin = Maskinplassering & " " & MaskinNr
  240.     rst!Path = Path
  241.     rst![start tid] = Now
  242.  
  243.  
  244. Dim MaskinStatus As New ADODB.Recordset
  245.     MaskinStatus.Open "SELECT * from [main oppetid]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  246.  
  247.  
  248.  
  249. .LookIn = Path
  250. .FileType = msoFileTypeAllFiles
  251. '.FileName = "*.st1"
  252. If .Execute(msoSortByLastModified) > 0 Then
  253.  
  254. FoundFiles = .FoundFiles.Count
  255.  
  256. Call Fremdriftsindikator_v2(, , FoundFiles)
  257.     rst![antall filer] = FoundFiles
  258. For i = 1 To FoundFiles
  259.  
  260. Call Fremdriftsindikator_v2(, , , , "Overfører " & .FoundFiles(i) & " (" & Len(.FoundFiles(i)) & ")", , , i)
  261.  
  262.     If Right(.FoundFiles(i), 2) = "F" & MaskinNr Or Right(.FoundFiles(i), 3) = "F" & MaskinNr Then
  263.     Call Overfør_fil_f(.FoundFiles(i), MaskinNr, Maskinplassering)
  264.     Kill (.FoundFiles(i))
  265.     End If
  266.  
  267.     If Right(.FoundFiles(i), 3) = "ST" & MaskinNr Or Right(.FoundFiles(i), 4) = "ST" & MaskinNr Then
  268.     Stnr = Stnr + 1
  269.     slashfrontpos = InStrRev(.FoundFiles(i), "\")
  270.     leng = Len(.FoundFiles(i)) - slashfrontpos
  271.     navn = Right(.FoundFiles(i), leng)
  272.  
  273.     Tid = GetCreateDate(.FoundFiles(i))
  274.     If Not Tidbrukt = 0 Then
  275.     Tiddiff = DateDiff("s", Tidbrukt, Tid)
  276.     End If
  277.     Tidbrukt = Tid
  278.  
  279.     Pnop = Mid(navn, 31, 3)
  280.     ATF_status = Mid(navn, 35, 1)
  281.     Machine_status = Mid(navn, 36, 1)
  282.     Paper_alarm = Mid(navn, 37, 1)
  283.     Ink_alarm = Mid(navn, 38, 1)
  284.     Shelf_open = Mid(navn, 39, 1)
  285.     Conveyor_alarm = Mid(navn, 40, 1)
  286.     Reserved = Mid(navn, 41, 1)
  287.     If Len(navn) <> 54 Then
  288.     casette_alarm = Mid(navn, 51, 4)
  289.     Else
  290.     casette_alarm = ""
  291.     End If
  292.  
  293.  
  294.     MaskinStatus.AddNew
  295.     MaskinStatus!MaskinNr = MaskinNr
  296.     MaskinStatus!Tid = Tid
  297.     MaskinStatus!Pnop = Pnop
  298.     MaskinStatus![atf-status] = ATF_status
  299.     MaskinStatus![machine status] = Machine_status
  300.     MaskinStatus![paper alarm] = Paper_alarm
  301.     MaskinStatus![ink alarm] = Ink_alarm
  302.     MaskinStatus![shelf open] = Shelf_open
  303.     MaskinStatus![conveyor alarm] = Conveyor_alarm
  304.     MaskinStatus![Reserved] = Reserved
  305.     MaskinStatus![casette alarm] = casette_alarm
  306.     If Not Stnr = 1 Then
  307.     MaskinStatus.Update
  308.     MaskinStatus.MoveLast
  309.     MaskinStatus.MovePrevious
  310.     MaskinStatus![sekunder siden forrige rad] = Tiddiff
  311.     MaskinStatus.Update
  312.     End If
  313.     'Kill .FoundFiles(i)
  314.     End If
  315.  
  316.     If Right(.FoundFiles(i), 2) = "C" & MaskinNr Or Right(.FoundFiles(i), 3) = "C" & MaskinNr Then
  317.     Call Overfør_fil_c(.FoundFiles(i), MaskinNr, Maskinplassering)
  318.     Kill (.FoundFiles(i))
  319.     End If
  320.     If Right(.FoundFiles(i), 2) = "R" & MaskinNr Or Right(.FoundFiles(i), 3) = "R" & MaskinNr Then
  321.     Call Overfør_fil_r(.FoundFiles(i), MaskinNr, Maskinplassering)
  322.     Kill (.FoundFiles(i))
  323.     End If
  324. Next i
  325. End If
  326. End With
  327.  
  328. Call Fremdriftsindikator_v2(, , , , , "ja")
  329.  
  330.     rst![slutt tid] = Now
  331.     rst![tid brukt] = DateDiff("n", rst![start tid], rst![slutt tid])
  332.     rst.Update
  333.     rst.Close
  334. Set rst = Nothing
  335. End Function
  336.  
  337. Public Function Overfør_fil_c(PathName As String, MaskinNr As Long, Maskinplassering As String)
  338. Dim Kasettnr As Integer
  339. Dim Varenr As Long
  340. Dim Antall As Long
  341. Dim Overskudd As Long
  342.  
  343. Dim oFSO As New FileSystemObject
  344. Dim oFS
  345. Set oFS = oFSO.OpenTextFile(PathName)
  346.  
  347. Dim rst As New ADODB.Recordset
  348. With rst
  349.  
  350. Do Until oFS.AtEndOfStream
  351. Overskudd = 0
  352. stext = oFS.ReadLine
  353. Kassett = Left(stext, 3)
  354. Varenr = Mid(stext, 4, 6)
  355. Antall = Mid(stext, 59, 4)
  356.  
  357. .Open "SELECT * from [LOK Loksummer] where varenr=" & Varenr & " AND lokasjon ='" & Maskinplassering & " " & MaskinNr & " MDK " & Kassett & "'", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  358.     If .EOF And .BOF Then
  359.     .AddNew
  360.     !Kassett = Kassett
  361.     !Varenr = Varenr
  362.     ![antall tabletter] = Antall
  363.     !lokasjon = Maskinplassering & " " & MaskinNr & " MDK " & Kassett
  364.     Overskudd = Antall
  365.     .Update
  366.     Else
  367.     If Not ![antall tabletter] = Antall Then
  368.     Overskudd = ![antall tabletter] - Antall
  369.     ![antall tabletter] = Antall
  370.     .Update
  371.     End If
  372.     End If
  373.     .Close
  374.     If Not Overskudd = 0 Then
  375.     .Open "SELECT * from [LOK Loksummer] where varenr=" & Varenr & " AND lokasjon ='" & Maskinplassering & " " & MaskinNr & " Maskindiff" & "'", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  376.         If .EOF And .BOF Then
  377.         .AddNew
  378.         !Varenr = Varenr
  379.         ![antall tabletter] = Overskudd
  380.         !lokasjon = Maskinplassering & " " & MaskinNr & " Maskindiff"
  381.         Else
  382.         ![antall tabletter] = ![antall tabletter] + Overskudd
  383.         End If
  384.         .Update
  385.         .Close
  386.  
  387.         .Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
  388.         .AddNew
  389.         !hendelse = "Maskinlager overskudd"
  390.         !Varenr = Varenr
  391.         ![antall tabletter] = Overskudd
  392.         ![fra lokasjon] = Maskinplassering & " " & MaskinNr & " MDK " & Kassett
  393.         ![til lokasjon] = Maskinplassering & " " & MaskinNr & " Maskindiff"
  394.         ![flyttet av] = "LogiDose"
  395.         ![flyttet tid] = Now
  396.         .Update
  397.         .Close
  398.     End If
  399.  
  400. Loop
  401. End With
  402.  
  403. End Function
  404.  
Oct 24 '08 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Move your "Do" line (#4) after the "Dim" line (#6), and move your "Loop" line (#24) before the "Set" line (#22).

There is no need to create a new recordset variable each time through the loop. This is time and resource intensive (very much so in fact), even though you tidy up the variable within the loop.
Oct 24 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
I should add that proper indenting of code is not only important for usability in a forum, but also for your own benefit. Frankly, wrongly done indenting is worse than none at all when trying to understand code.

All code within blocks (anything with a start and end marker like If ... Else; If ... End If; Do ... Loop; etc) should be indented from the surrounding code. This helps a reader to see easily which code is effected by the grouping.

As an illustration your code should be (including my suggested changes) :
Expand|Select|Wrap|Line Numbers
  1. Dim Maskinplassering As String
  2. Dim MaskinNr As Long
  3. Dim Path As String
  4. Dim rst As New ADODB.Recordset
  5.  
  6.   Do
  7.     rst.Open "SELECT * from [Maskinoversikt] where aktiv =-1", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  8.  
  9.     If rst.EOF And rst.BOF Then
  10.       MsgBox "Finner ikke maskin!"
  11.     Else
  12.       Do Until rst.EOF
  13.         Maskinplassering = rst!Maskinplassering
  14.         MaskinNr = rst!MaskinNr
  15.         Path = rst!Path
  16.         Call Hent_fra_atf(MaskinNr, Maskinplassering, Path)
  17.         rst.MoveNext
  18.       Loop
  19.  
  20.     End If
  21.     rst.Close
  22.  
  23.   Loop
  24.   Set rst = Nothing
I used a simple two-space indentation to fit more easily on the forum. In your own modules I'd use the normal four.
Oct 24 '08 #3

100+
P: 157
Yes, i was meaning to 'clean' up the code before using the application. This is something you can call an Alpha edition of the finished product.

I will move the loop and do and test it out next week. Will post a feedback of the result :)
Oct 24 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
That's cool.

The point I was trying to make though, is that having tidy code is a fundamental part of the development process. Specifically NOT a job to leave till afterwards as it is so closely involved with getting it right in the first place.

We all make our own choices of course, but that would be my advice.
Oct 24 '08 #5

100+
P: 157
now it loops in all eternity :)

Thank you for your help
Oct 28 '08 #6

NeoPa
Expert Mod 15k+
P: 31,494
My pleasure MrD :)

I'm glad to see that fixed the problem.
Oct 28 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.