Link Tables with progress Bar

[jazzy]

currently i am using this code to ReLink Tables and flash the status bar so the user is aware there is activity BUT most do not see it and get impatient. So I have a form with a progress bar type solution. having some difficulty combining the two. there are 19 linked tables that i can pull the qty from using this code DCount("*", "MSysObjects", "Type = 4")

Public Function RelinkTables() '(environment As Integer)    On Error Resume Next
    Dim tblDef As DAO.TableDef
    Dim LinkedTableCount As Integer
    
    Form_frm_Progress.TimerInterval = 250
    
    LinkedTableCount = DCount("*", "MSysObjects", "Type = 4")
    
    For Each tblDef In CurrentDb.TableDefs
        If tblDef.Connect <> "" Then
            tblDef.Connect = DLookup("ODBCPath", "tbl_SystemInfo", "SystemInfoID= 1")
            'tblDef.Connect = GetConnectionString(environment)
            Status ("Linking Table......")
            tblDef.RefreshLink
            
        End If
            Status ("")
    Next


End Function

Sub Status(pstrStatus As String)    
    Dim lvarStatus As Variant
  
    If pstrStatus = "" Then
        lvarStatus = SysCmd(acSysCmdClearStatus)
    Else
        lvarStatus = SysCmd(acSysCmdSetStatus, pstrStatus)
    End If
    
End Sub

here is the form i am using

Private Sub Command2_Click()Dim LinkedTableCount As Integer
LinkedTableCount = DCount("*", "MSysObjects", "Type = 4")
Me.TimerInterval = 250
End Sub


Private Sub Form_Timer()
Me.txtCount = Me.txtCount + 1
Me.Box3.Width = Me.txtCount * 100
If Me.txtCount = 100 Then
Me.TimerInterval = 0

End If
End Sub

cant figure out how to combine them. after each table gets linked it updates the progress bar.

Thanks

[Welshgasman]

Count the number of tables to link and update as each is done?
So if 10 tables, as each is processed, then increase by 10% ?

[jazzy]

That’s the plan but need the code to do that. I think the code is close but not sure where to put what.

[Micron]

Have done exactly that and can tell you it's doable but not quite as simple. At least, not the way I did it.
First, in between steps you need to repaint the form or much of the time the bar won't change due to screen update lag - the next task will begin before that can happen. To overcome that you need to also create a slight pause (e.g. a function call with a small timer counter). I never knew about Do Events in those days, so maybe I made it more difficult than it needed to be.

Not sure what the issue really is - how to integrate both codes into one procedure? Can you post what you tried if you're not going to use the suggested method?

[isladogs]

This shows a screenshot of a form I use for relinking tables:



As you can see, I have a progress bar on the form itself as that is more obvious than the progress marker on the status bar
If you like the idea, you can download the code from Progress Bar (isladogs.co.uk)

There are 3 functions in modProgress: SetupProgressBar, UpdateProgressBar & HideProgressBar which you would need to import.
You would use these in your RelinkTables procedure:

Public Function RelinkTables() '(environment As Integer)    On Error Resume Next
    Dim tblDef As DAO.TableDef
    Dim iCount As Integer
    
    Form_frm_Progress.TimerInterval = 250
    
    iCount = DCount("*", "MSysObjects", "Type = 4")

    SetupProgressBar 
 
        For Each tblDef In CurrentDb.TableDefs
        If tblDef.Connect <> "" Then
            tblDef.Connect = DLookup("ODBCPath", "tbl_SystemInfo", "SystemInfoID= 1")
            'tblDef.Connect = GetConnectionString(environment)
           '  Status ("Linking Table......")
            tblDef.RefreshLink
            UpdateProgressBar
            
        End If
          '   Status ("")
    Next

    HideProgressBar

End Function

OPTIONAL: You can also add a label showing the table currently being linked as in the screenshot above

BTW If you have any linked Access or Excel tables etc, these have Type = 6

[jazzy]

Great Stuff Colin, Thank You

[isladogs]

You're welcome

EDIT:
The notification email included a question about adding the name of the table currently being linked to a form label.
As that part isn't in your post any longer, I assume you've already worked out how to do that

[jazzy]

got it working, problem is the progress completes and the tables are still linking. i had to move the iCount = DCount("*", "MSysObjects", "Type = 4") to the SetupProgressBar for the bar to display 1 of 19 tables. it also looks like the progress bar is incrementing by 2........ 2 of 19, 4 of 19 etc....

any insight would be appreciated

[isladogs]

If its increasing in steps of two, you must be duplicating the incremental count somehow.
Try debugging step by step.

Compare with my example app. That uses a timer but the basic idea is the same