Auto link table in MDB using VBA (With Password)

For every link table on your MDB, when you need to change path to link
this is simple VBA function for working, place this code into your module (some old or new)
(on front-end mdb or adp)

 Read my advice “How to use this function”

”*****Module:****

Function Linkdatabase(ByVal LinkFile As String, Optional pw = “”, Optional Alltb = True)
Dim cnn As New ADODB.Connection
Dim Rss As New ADODB.Recordset
Dim sq As String
Set cnn = Application.CurrentProject.Connection

If pw <> “” And InStr(1, LCase(pw), “access”) = 0 Then pw = “MS Acess;PWD=” & pw & “;”

If Alltb = False Then
sq = “SELECT * FROM LinkTable”
Else
sq = “SELECT NAME FROM MsysObjects WHERE ([TYPE]=6)”
End If

Dim dbs As dao.Database
Dim tb As dao.TableDef
Dim j As Long
    If Rss.State = 1 Then Rss.Close
    Rss.Open sq, cnn, 1
    j = Rss.RecordCount
    Do While j <> 0
    sq = Rss(0)
    j = j – 1
    If tbExist(Rss(0)) Then DoCmd.DeleteObject acTable, Rss(0)
    If pw <> “” Then
        Set dbs = DBEngine.Workspaces(0).OpenDatabase(LinkFile, False, False, pw)
            For Each tb In dbs.TableDefs
            If tb.Name = sq Then DoCmd.TransferDatabase acLink, “Microsoft Access”, _
            LinkFile, acTable, sq, sq, False, True
            Next
        dbs.Close
        Set dbs = Nothing
    Else
    DoCmd.TransferDatabase acLink, “Microsoft Access”, LinkFile, acTable, sq, sq, False
    End If
    Rss.MoveNext
   
    Loop
   
    Rss.Close
    Set Rss = Nothing
    Set cnn = Nothing

End Function

‘*****End Module:****

 

the function “tbExist” that included is a function I post on first post if you not yet read it please follow this http://yeadram.wordpress.com/check exists before alter table field/

 

Multi-style to use this function

By Macro Autoexec  if your want to MDB ask every time yor start the program

by Command button  Let this function can called by any command button

Link some table(s)    if you want to re-link for some tables (Not all)

&npsp;

use auto Macro

- take create macro name = “autoexec”
- use the command          = “runcode”
- argument name of function = ChangeDatabase
- save and close the macro (besure name of macro is “autoexec”)
- copy below function into your module (some old or new)

 

Function ChangeDatabase()
Dim sq As String
Dim pw As String
Dim msg As String
Dim LinkFile As String
Dim aTb As Boolean

LinkFile = DLookup(“Database”, “MsysObjects”, “[TYPE] = 6″)
pw = Nz(DLookup(“Connect”, “MsysObjects”, “[TYPE] = 6″), “”)
If InStr(1, pw, “PWD”) > 0 Then
msg = LinkFile & “-With Password!”
Else
msg = LinkFile
End If

sq = InputBox(“Your current path is” & vbCrLf & msg & _
vbCrLf & “If you need to change path please retype then press OK button” & _
vbCrLf & “if The ‘destination MDB’ have a password ….” & _
vbCrLf & “input ‘-[your password]‘ after path”, “Confirm path of your data”, msg)
If IsNull(sq) Or IsEmpty(sq) Or sq = ” ” Then Exit Function

If tbExist(“LinkTable”) Then
Select Case MsgBox(“Do you want re-link only table’s name in ‘LinkTable’?” & vbCrLf & _
“… if you said ‘No’ it will re-link All tables …”, vbQuestion + vbYesNoCancel, “How many tables”)
Case vbYes
aTb = True
Case vbNo
aTb = False
Case Else
Exit Function
End Select
End IF

If InStr(1, sq, “-”) > 0 Then
Dim ar() As String
ar = Split(sq, “-”, , vbTextCompare)
    If ar(0) <> LinkFile Then
    LinkFile = ar(0)
        If ar(1) = “With Password!” Then
        Linkdatabase LinkFile, pw, aTb
        Else
        Linkdatabase LinkFile, “MS Acess;PWD=” & ar(1) & “;”, aTb
        End If
    End If
Else
    If sq <> LinkFile Then
    LinkFile = sq
    Linkdatabase LinkFile, “”, aTb
    End If
End If
End Function

 

Use by Command button

you can call this function from any forms by this syntax
- Call LinkDatabase [path and name destination MDB],[Option Password],[Option AllTable]

 

Link some table(s)

 create table one field
table name =”LinkTable”
field DataTye =”text”
field DataWidth=50
field Name = (any word)

- Input name of tables that you want to re-link
- one name per one record
- and then
if you use by command button Besure the third argument is ‘FALSE’
but if use by macro don’t warry

 

 

…………………………….