How do I utilize Access 2007 Linked Table Manager in C#

Question

Scenario: I have a Front End and a Back End Access 2007 Database that are currently linked to each other through the Linked Table Manager Database Tool. The Back End DB is going to be moved to a location on a server. The server name will be different for each facility and there are about 40 or so now which will increase throughout the year.

What I need to try to accomplish is changing the linked tables programatically. I will need to build the linked string to something like:

\\something\facilitynum(gathered from Environment variable)\c$\somefolder\.

I have found that the column Database in MSysObjects contains the link string that would need to be changed. The question becomes, how do get permissions to change a System table or use some .dll that will allow me to change the link to the newly built string?

Everything that I have found so far always leads back to manually changing the link within the Access Database.

See http://stackoverflow.com/questions/7461414/ado-net-way-for-creating-a-linked-table — Fionnuala, Mar 15 '12 at 17:42
@Fionnuala: the questions are actually distinct. That one was about ADDING linked tables with SQL, this one is about retargetting already existing linked tables in the front end Access application/database. The former you can't do, the later you can... — jmoreno, Oct 02 '15 at 06:19

score 0 · Answer 1 · answered Apr 13 '15 at 18:05

You can programmatically change the link from within Access (using VBA) like so (this uses a dsn file to contain the actual server information)

Private Sub UpdateDSN()
  On Error GoTo ErrorHandler

  Dim dbPath As String

  Dim connStr As String
  Dim Tdf As TableDef

  dbPath = Application.CodeDb.Name
  dbPath = Left(dbPath, InStr(dbPath, Dir(dbPath)) - 1)

  For Each Tdf In CurrentDb.TableDefs
    connStr = Tdf.Connect
    If InStr(1, UCase(connStr), "ODBC") Then
      connStr = "odbc; FILEDSN=" & dbPath & "db.dsn;"
      Tdf.Connect = connStr
      Tdf.RefreshLink
    End If
  Next

  Dim fName As String
  Dim fNumber As Integer
  Dim InputStr As String

  fNumber = FreeFile()
  fName = dbPath & "db.dsn"

  Dim serverName As String

  Open fName For Input As fNumber

  Do While Not EOF(fNumber)
    Line Input #fNumber, InputStr
    If InStr(1, UCase(InputStr), "SERVER=") > 0 Then
      serverName = Right(InputStr, Len(InputStr) - _
                   (InStr(1, InputStr, "SERVER=") + 6))
    End If
  Loop

  ErrorHandler:
  On Error GoTo 0

  DoCmd.OpenForm "Main"

  cap = Forms!main.Caption
  If InStr(1, cap, "(") > 1 Then
     cap = Left(cap, InStr(1, cap, "("))
  End If
  Forms!main.Caption = "db" & " (" & serverName & ")"
End Sub

How do I utilize Access 2007 Linked Table Manager in C#

1 Answers1