Link a text file to Access using VBA

wscheiman · October 6th, 2004, 09:21 AM

Does anyone know how to link a text file to an Access
database using VBA code? I've tried variations of
the DoCmd.TransferDatabase using a TransferType of
acLink, but it seems that there is no option under
the DatabaseType.

I have been able to link the text file using File |
Get External Data | Link, but have had no luck at
all trying to do this using VBA.

I know that linking a text file or Excel spreadsheet
may be considered more of an exception in many
respects, but any ideas would be GREATLY appreciated!

Thank you. :)

sefarkas · June 30th, 2013, 07:02 PM

Code:

Sub LinkToTextFiles()
On Error GoTo ErrHandler
' include a reference to the "Microsoft Scripting Runtime"
Dim objFSO As FileSystemObject 
Dim objDirRoot As Folder
Dim objDir As Folder
Dim objFile As File
Dim PageName As String
   
    Set objFSO = New FileSystemObject
    Set objDirRoot = objFSO.GetFolder("C:\MonthlyReports")
    For Each objDir In objDirRoot.SubFolders
      Debug.Print objDir.Name
      For Each objFile In objDir.Files
        If objFile.Name Like "*.txt" Then
          PageName = objFile.Name
' create link specification by manually importing one sample
' use the advanced... button to save a particular import style
' the link specification is optional if you want VBA to guess about columns
' in your text file
          DoCmd.TransferText acImportFixed, "MOR_Link_Specification", _            PageName, _
              objFile.Path, _
              False          Debug.Print PageName
        Else
     ' if no txt file is picked then try another one
        End If
      Next
    Next
     GoTo CleanUp
ErrHandler:  MsgBox Err.Description
  Resume CleanUp
  Resume 'debugging only
CleanUp:  MsgBox "done."
End Sub