MS Access VBA - Database Engine Cannot Open Programatically Linked Text File

Question

I have an Access Front End that is manually linked to a text file that is updated by an external process. The text file is treated as a linked table. When this file is linked manually, there is no issue.

I'm now trying to write code to programmatically link the table when the front end is opened, since the app will need to be deployed in different locations with different files. Based on suggestions from here, I used the following code which does link the correct file (visible in the MSAccess Toolbar) however when I attempt to open it I get the "Database Engine does not have exclusive access to this file..." error message. The file is on the local machine. Not sure if I'm doing something wrong in the connect string?

If fso.FileExists(FileLocation) Then
        With CurrentDb
            Set td = db.CreateTableDef(tsfilename)
            constring = "Text;Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;ACCDB=YES;DATABASE=C:\Results; TABLE=Results_File#txt"
            td.Connect = constring
            td.SourceTableName = tsfilename
            .TableDefs.Append td
            .TableDefs.Refresh
        End With
End If

Answer 1

You have overcooked your vba code to establish the link. Try this:

DoCmd.TransferText acLinkDelim, "Your Import Spec Name", "Your DB Table Name", "Your Text File Path And Name", , , 437

Note that you probably do not need the codepage. It should default to the local machine codepage.

And to cover all the bases, here is a complete subroutine to seamlessly remove and relink the text table:

Sub sTest()

  Dim db           As DAO.Database
  Dim fso          As Object
  Dim strTableName As String
  Dim strFileName  As String
  Dim strSpecName  As String

  ' You can optionally pass these three values in as parameters to the subroutine
  strSpecName = "Your Specification Name"
  strTableName = "Your Table Name"
  strFileName = "Your File Name"

  Set db = CurrentDb
  Set fso = CreateObject("Scripting.FileSystemObject")

  ' Only process if the text file exists
  If fso.FileExists(strFileName) Then

    ' Conditionally delete the linked table name
    On Error Resume Next
    With db
      If .TableDefs(strTableName).Name = strTableName Then
        If Err.Number = 0 Then
          .TableDefs.Delete strTableName
        End If
      End If
    End With

    ' Err.Number always carries the last value. Force reset to zero.
    Err.Number = 0

    ' Link the text file
    DoCmd.TransferText acLinkDelim, strSpecName, strTableName, strFileName
    If Err.Number = 0 Then
      MsgBox "File " & strFileName & " successfully linked to database."
    Else
      MsgBox "Linking file " & strFileName & " to database failed with error " & Err.Number & "-" & Err.Description
    End If

    On Error GoTo 0

  End If

  Set fso = Nothing
  Set db = Nothing

End Sub