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

If refreshing the table link isn't working to your satisfaction have you tried deleting the linked table and re-creating it? — Gord Thompson, Jun 07 '13 at 22:46
Thanks @GordThompson. I suppose I could delete the linked table, but I'd much rather get the programmatic refresh working like the standard one does. I'm also not 100% how the DSN was pointed to when the linked tables were created, and I want the impact on the users to be as little as possible. — James Skemp, Jun 09 '13 at 11:58
Understood, and if Linked Table Manager can do it then I would have expected that `tb.RefreshLink` would be able to do it too. Did you try something like `tb.Connect = tb.Connect & ""` to make the `.Connect` property "dirty" before invoking `tb.RefreshLink`? — Gord Thompson, Jun 09 '13 at 12:22
That's not a bad idea. I'll give that a try when I'm back in the office tomorrow, and update with how well it worked. Do you know if the tb.Fields.Refresh provides any benefit? I'm not 100% and I'd like to remove it if I can. — James Skemp, Jun 09 '13 at 12:51
RE: `tb.Fields.Refresh` - Can't say, I've never had to use that particular method. — Gord Thompson, Jun 09 '13 at 13:03
Unfortunately, making the `Connect` property dirty didn't work. In addition to trying to add nothing, I also tried changing the `Description` in the connection; neither worked :( — James Skemp, Jun 10 '13 at 14:33
Very strange. I just confirmed that, under "normal" circumstances, a `tb.RefreshLink` all by itself is sufficient. (Access 2010 and SQL Server 2008) — Gord Thompson, Jun 10 '13 at 15:38
Before I started assisting others I was dumping properties; at this point I think I'm going to refresh the one through the Linked Tables Manager and hope that my connection information isn't horribly messed with. — James Skemp, Jun 10 '13 at 16:22
Thanks for your help Gord. I finally figured out what the oddities were and posted an answer detailing it. If you want to add an answer with your comments you've got my +1. — James Skemp, Jun 11 '13 at 16:33

score 2 · Accepted Answer · answered Jun 11 '13 at 16:29

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.

Thanks for the follow-up, and good to know about the `datetime2` issue. — Gord Thompson, Jun 11 '13 at 16:42
Yeah, unfortunate that the driver doesn't support that type, but ... I'm sort of glad it didn't immediately work; I didn't know you could actually use the Immediate window in the Visual Basic for Applications app, and wouldn't have if I hadn't run into so many issues. — James Skemp, Jun 11 '13 at 16:51

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

1 Answers1

Linked