How do you programmatically update a linked table in Access that will refresh data types too?

Question

I'm working with a legacy Microsoft Access database that we've recently updated to use linked tables/SQL Server backend.

I'm making some changes to the structure of the data and would like to programmatically update the linked table reference.

However, with the code I'm using, after doing what I would expect would refresh the linked tables I'm not getting updated data types for a particular table. Right now the data type is Text, but if I use External Data > Linked Table Manager and go through that process they update to Date/Time.

(I'd like to have a function that will flip between dev and production, so I don't consider the above route to be an option.)

Access/VB isn't my strongest skill anymore, but from MSDN (this and this) it seems like tb.Fields.Refresh is required, but it isn't working as I'd expect.

What am I doing wrong?

Function RefreshLinkedTables() As Boolean
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim fld As DAO.Field

    Set db = CurrentDb

    For Each tb In db.TableDefs
        ' Skip system files.
        If (Mid(tb.Name, 1, 4) <> "MSys" And Mid(tb.Name, 1, 4) <> "~TMP") Then
            Debug.Print tb.Name
            Debug.Print tb.Connect
            If (Mid(tb.Connect, 1, 5) = "ODBC;") Then
                tb.RefreshLink
                If (tb.Name = "Jobs") Then
                    Debug.Print "Refreshing fields data"
                    tb.Fields.Refresh
                End If
            End If
            Debug.Print "=== === ==="
        End If
        db.TableDefs.Refresh
    Next

    Set db = Nothing

    RefreshLinkedTables = True
    Exit Function
End Function

Answer 1

There were a couple things that resulted in the odd behavior.

First, the linked table had initially used the SQL Server driver, not the SQL Server Native Client 10.0 one. So, when I refreshed the tables I selected the incorrect one (I knew it wasn't 11.0, but thought it was 10.0).

The second issue is that when the Access tables were converted over to SQL Server the datetime fields were setup as datetime2(0) (Access 2010 migration tool was used). Unfortunately the SQL Server driver doesn't support these.

We want users to authenticate via Windows Authentication (again, legacy app that'll hopefully get moved to the web or a 3rd-party solution one-day), and we know it works this way.

After altering the SQL Server tables to use datetime instead of datetime2, the below code worked just fine:

Option Compare Database
Option Explicit

Function RefreshLinkedTables() As Boolean
    Dim db As DAO.Database
    Dim tb As DAO.TableDef

    Set db = CurrentDb

    For Each tb In db.TableDefs
        ' Skip system files.
        If (Mid(tb.Name, 1, 4) <> "MSys" And Mid(tb.Name, 1, 4) <> "~TMP") Then
            Debug.Print tb.Name
            Debug.Print tb.Connect
            If (Mid(tb.Connect, 1, 5) = "ODBC;") Then
                'We only need to refresh a single table.
                If (tb.Name = "Jobs") Then
                    tb.Connect = tb.Connect & ""
                    'Live connection
                    'tb.Connect = "ODBC;Description=___;DRIVER=SQL Server;SERVER=___;APP=Microsoft Office 2010;DATABASE=___"
                    'Dev connection
                    'tb.Connect = "ODBC;Description=___;DRIVER=SQL Server;SERVER=___;APP=Microsoft Office 2010;DATABASE=___"
                    tb.RefreshLink
                End If
                'tb.RefreshLink
            End If
            Debug.Print "=== === ==="
        End If
    Next
    db.TableDefs.Refresh

    Set db = Nothing

    RefreshLinkedTables = True
    Exit Function
End Function

Logic could be cleaned up a bit more, but it works.

Thanks to Gord Thompson for his helpful comments.