Skip navigation

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 https://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
     
     
    …………………………….

    ใส่ความเห็น

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

    Twitter picture

    You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

    Facebook photo

    You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

    Google+ photo

    You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

    Connecting to %s

    %d bloggers like this: