✔ VBA Dashboards Specialist®: VBA Access - Altera o Path das tabelas Conectadas

22 de novembro de 2013

VBA Access - Altera o Path das tabelas Conectadas - Change Link Path

Consultancy in Excel, Access, VBA & PowerPoint projects

DESENVOLVO APLICAÇÕES

Perguntas, Questões e Esclarecimentos

Com esta função podemos alterar o path das nossas tabelas conectadas a aplicação, por um novo link.

Public Function ChangeLinkPath(strNewPath As String) As String

Dim dbs As DAO.Database
Dim strTblName As String
Dim colTbl As Collection
Dim intTbl As Integer

If strNewPath <> "" And Dir(strNewPath) <> "" Then

      Set colTbl = New Collection
      Set dbs = CurrentDb

      For intTbl = dbs.TableDefs.Count - 1 To 0 Step -1
            If dbs.TableDefs(intTbl).Connect <> "" And _
                  Not dbs.TableDefs(intTbl).Connect Like "*" & strNewPath Then
                  colTbl.Add dbs.TableDefs(intTbl).Name
                  dbs.TableDefs.Delete dbs.TableDefs(intTbl).Name
            End If
      Next intTbl

      For intTbl = colTbl.Count To 1 Step -1
Let strTblName = colTbl(intTbl)
            DoCmd.TransferDatabase acLink, "Microsoft Access", _ strNewPath, acTable, strTblName, strTblName
            Debug.Print "connection made to '" & strTblName & "'"
      Next intTbl

      Set dbs = Nothing
      Set colTbl = Nothing

      Debug.Print "Feito!"
      ChangeLinkPath = "Feito!"
Else
'Debug.Print "New path not provided. No changes made!"
Let ChangeLinkPath = "New path not provided. No changes made!"

End If

Exit Function
End Function