Wednesday, July 18, 2007
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
Loop
Application.Echo True
End Sub
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
Loop
Application.Echo True
End Sub
Labels: Access
