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