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
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.