Switching linked tables from SQL Server to Access

[NYC2RDU]

I have a front end client that needs to be able to connect to both SQL Server and Access. I have no problems switching from Access to SQL Server but when I try and switch from SQL Server to Access every technique has failed.

Despite providing what I believe is the correct connection string (it's the one I use when initially linking the tables to an Access back end) when the TDF.Refreshlink code executes I'm prompted to provide the DSN source.

Here's my code:

For Each Tdf In Dbs.TableDefs
If Len(Tdf.Connect) > 0 Then

Tdf.Connect = "MS Access;" & "PWD=XXXXX" & ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
Tdf.RefreshLink

If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next Tdf

Any and all advice will be greatly appreciated.

 

[Rx_]

Something like this happened to me a long time ago. Don't know why it happened.
What I do is Delete all linked tables first, then re-create them. Have been doing that ever since out of habbit.
Deleting all linked tables is somewhat easy.

To rebuild, create a small table named SQL_Linked - add the name of the linked table, and a couple of yes/no collumns, and a text column for notes.
Instead of a single refresh, loop through the table and get the table name, use that to recreate and append the new table def.

Some code to give you some ideas - my connect property calls other functions that are not incuded so it can link various versions and such.
This should give you an idea or two.

Dim rsSQLLinked As Recordset
      Dim RecordsCount As Integer
      Dim Counter As Integer
      Dim td As TableDef  ' for table SQL_Linked
      Dim tdLinked As TableDef ' for new linked table
10    On Error Resume Next
20    Set rsSQLLinked = CurrentDb.OpenRecordset("SQL_Linked", dbOpenDynaset, dbSeeChanges)
30    rsSQLLinked.MoveLast
40    RecordsCount = rsSQLLinked.RecordCount
50    rsSQLLinked.MoveFirst
60    Debug.Print "Number of Linked Tables " & RecordsCount
      '   Delete the linked tables that have a check in the Relink Column
70    If RecordsCount <> 0 Then
80        For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
90                    If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
100                         For Each td In CurrentDb.TableDefs
110                               If td.Name = rsSQLLinked.Fields(0).Value Then
120                                     CurrentDb.TableDefs.Delete rsSQLLinked.Fields(0).Value
                Debug.Print "Error Deleting old links " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value
130                                     Err.Clear
140                               End If
150                         Next
160                   End If
170       rsSQLLinked.MoveNext
180       Next Counter
190   CurrentDb.TableDefs.Refresh
200   Else
210       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
220       Exit Sub
230   End If
      ' ////////////// Relink to SQL Server ///////////
240   If RecordsCount <> 0 Then
250       rsSQLLinked.MoveFirst
260       For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
270                   If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
                      ' Add new linked table here
'280                           Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value)
280                            Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value, dbAttachSavePWD) ' password persist now
290                           tdLinked.Connect = ModifiedRefreshDNSLess2(rsSQLLinked.Fields(0).Value)
300                                   tdLinked.SourceTableName = "dbo." & rsSQLLinked.Fields(0).Value
310                                   CurrentDb.TableDefs.Append tdLinked
320                                   CurrentDb.TableDefs(rsSQLLinked.Fields(0).Value).RefreshLink
330                                   CurrentDb.Containers("Tables").Documents.Refresh ' doesn't refresh table icon
340                   End If
350       rsSQLLinked.MoveNext
            Debug.Print "Error " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value
360       Err.Clear
370       Next Counter
380   CurrentDb.TableDefs.Refresh
390   RerefreshLinkedTables
400   Else
410       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
420       Exit Sub
430   End If
      ' ////////////////
440   Set rsSQLLinked = Nothing
450   Set tdLinked = Nothing
460   Set td = Nothing
470   Exit Sub
End Sub