Access VBA: Link all Dbase files from a folder

If you would like to link all Dbase files, any linkable file for that matter, in MS Access, use the following code. I read somewhere that refreshing the links is slower than deleting and creating new links.

Sub LinkAllTblsinDir()
Dim sTblNm As String, sPath As String, sFileNm As String
sPath = “C:\DW\”
‘Turn of the Echo to avoid window repaint/refresh
Application.Echo False

sFileNm = Dir(sPath, vbNormal)
Do While sFileNm “”
If Right(sFileNm, 3) = “dbf” Then
sTblNm = Left(sFileNm, Len(sFileNm) – 4) ‘Extract the file name
‘Use the TransferDatabase option to link the tables from the specified directory
‘to your current Access DB
DoCmd.TransferDatabase acLink, “dBase III”, sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir

Application.Echo True
End Sub

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.