Auto import in diffrent time periods (1 Viewer)

Magnus1982 · Aug 15, 2018

Hi,

In my access I have 6 tables . Which are fill by imported data.

Base on this tables - query's and charts are working in my main form.

I am looking wait to automagically import data to my tables
I can do that with form Time Event but it import all data at once . I need import each data separately . For example

1st table every 8minuts
2nd table every 10 minutes
3rd table every 11 minutes

How to achieve this?

pbaldy · Aug 16, 2018

You could run the timer event every minute, and keep track of the last time you've done each import. When the appropriate time has elapsed, run an import and update the time it was done. Presumably you'd have different queries for each.

Mark_ · Aug 16, 2018

As an example in "air-code"

Code:

Dim adImport1 as DateTime
.
.
.
.
.
.

IF adImport1 < NOW( ) then 
   TCO_Import1
   adImport1 = DateAdd('n", 8, now()) ' Do the import in 8 minutes.
End IF

.
.
.
.
.
.

In effect, in your event timer, each of the imports will be done in sequence with their "Next run time" being <MINUTES> from now. You'd simple have one sub for each import. For the example I'm using TCO_Import (Take Care Of) to stand in for your sub calls.

This does mean the first time you fire up the program it will do through and do all of the imports in sequence.

Magnus1982 · Aug 18, 2018

Below I posted my code. I do not fully understand how to insert your suggestion.

Private Sub Form_Timer()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM files"
DoCmd.SetWarnings True
Call ListFilesToTable("E:\UTC\Vizualizacja\A")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM filesfa"
DoCmd.SetWarnings True
Call ListFilesToTableB("E:\UTC\Vizualizacja\B")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM filesdcm"
DoCmd.SetWarnings True
Call ListFilesToTableC("E:\UTC\Vizualizacja\C")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM filesbck"
DoCmd.SetWarnings True
Call ListFilesToTableD("E:\UTC\Vizualizacja\D")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM filesdply"
DoCmd.SetWarnings True
Call ListFilesToTableE("E:\UTC\Vizualizacja\E")
End Sub

MarkK · Aug 18, 2018

Here's one idea...

Code:

Private m_eightCount As Integer
Private m_tenCount As Integer
Private m_elevenCount As Integer

Sub Form_Load()
    Me.TimerInterval = 1000 * 60    ' one minute
End Sub

Private Sub Form_Timer()
    m_eightCount = m_eightCount + 1
    m_tenCount = m_tenCount + 1
    m_elevenCount = m_elevenCount + 1
    
    If m_eightCount = 8 Then
        m_eightCount = 1
        CurrentDb.Execute "DELETE * FROM files"
        Call ListFilesToTable("E:\UTC\Vizualizacja\A")
    End If
    If m_tenCount = 10 Then
        m_tenCount = 1
        CurrentDb.Execute "DELETE * FROM filesfa"
        Call ListFilesToTableB("E:\UTC\Vizualizacja\B")
    End If
    If m_elevenCount = 11 Then
        m_elevenCount = 1
        CurrentDb.Execute "DELETE * FROM filesdcm"
        Call ListFilesToTableC("E:\UTC\Vizualizacja\C")
    End If
End Sub

hth
Mark

arnelgp · Aug 19, 2018

here is another variation. use Static variable:

Code:

' Initially set your TimerInterval to 480000 (8 minutes) in design view
Private Sub Form_Timer()
      Static Counter As Byte
 
     ' firstly kill the Timer, just in case you are running a long process
     Me.TimerInterVal = 0
 
     Counter = Counter + 1
 
     ' if we are done updating the 5 tables, the timer will not fire anymore
     If Counter > 5 Then Exit Sub   'you only have 5 table (I thought it was 6)
     DoCmd.SetWarnings False
     Select Case Counter
     Case Is = 1
          DoCmd.RunSQL "DELETE * FROM files"
          Call ListFilesToTable("E:\UTC\Vizualizacja\A")
     Case Is = 2
          DoCmd.RunSQL "DELETE * FROM filesfa"
          Call ListFilesToTableB("E:\UTC\Vizualizacja\B")
     Case Is = 3
          DoCmd.RunSQL "DELETE * FROM filesdcm"
          Call ListFilesToTableC("E:\UTC\Vizualizacja\C")
     Case Is = 4
          DoCmd.RunSQL "DELETE * FROM filesbck"
          Call ListFilesToTableD("E:\UTC\Vizualizacja\D")
     Case Is = 5
          DoCmd.RunSQL "DELETE * FROM filesdply"
          Call ListFilesToTableE("E:\UTC\Vizualizacja\E")
     End Select
     DoCmd.SetWarnings True

     ' set the new timer to trigger 2 minutes
    Me.TimerInterval = 120000
End Sub

End Sub

Auto import in diffrent time periods (1 Viewer)

Magnus1982

Registered User.

pbaldy

Wino Moderator

Mark_

Longboard on the internet

Magnus1982

Registered User.

MarkK

bit cruncher

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread