Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 22nd, 2006, 11:24 PM
Authorized User
 
Join Date: Feb 2006
Location: , , Singapore.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re-linking the front and back end

Hi,
I have an application deployed using the front-end and back-end method.
How do I, using ADO codes, update the link?

i.e. if the backend data's location has changed from I:\ to J:\,
what ADO codes can I use to change this link?
Currently, the only method I know is to delete the linked tables and
re-link them to the new location. Can someone suggest another way?

THanks Loads!



Scripts82
__________________
Scripts82
Reply With Quote
  #2 (permalink)  
Old February 24th, 2006, 12:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Here are the components of a re-linking routine originally developed by Alison Balter (I believe) that I've used a bunch before. It runs at start-up, verifies the validity of your dbs table links, and if the links aren't valid, uses the FileDialog object to allow the user to select a new backend path:

=======================================
‘ Start code behind a startup form:
=======================================

Private Sub Form_Load()
    DoCmd.Hourglass True
    Me.Visible = False ‘Hide main switchboard form
' DoCmd.OpenForm "frmSplashScreen" ‘ Display splash screen while re-linking code runs

    Call LinkTables

    DoCmd.Hourglass False

End Sub

Sub LinkTables()
    Dim varItem As Variant
    Dim strOut As String
    Dim strMsg As String
    Const conFileDialogFilePicker As Long = 3

    On Error GoTo LinkTables_Err:

    DoCmd.Hourglass True

    If Not VerifyLink Then
        If Not ReLink(CurrentProject.FullName, True) Then
            strMsg = "You must locate the backend data tables to proceed." & vbCrLf & vbCrLf
            strMsg = strMsg & "Click OK to continue."
            MsgBox strMsg, vbExclamation + vbOKOnly, "Re-Fresh Link to Backend"

            With Application.FileDialog(conFileDialogFilePicker)
                .ButtonName = "Select"
                .Title = "Choose your Database Files"
                .AllowMultiSelect = False
                .Filters.Clear
                .Filters.Add "Access databases", "*.mdb; *.mde", 1
                .FilterIndex = 1
                .Show

                If .SelectedItems.Count > 0 Then
                    For Each varItem In .SelectedItems
                        strOut = strOut & varItem & vbCrLf
                    Next varItem
                    If Not ReLink(strOut, False) Then
                        MsgBox "You cannot run this application without locating data tables"
                        DoCmd.Quit
                    End If
                Else
                    MsgBox "You cannot run this application without locating data tables"
                    DoCmd.Quit
                End If
            End With
        End If
    End If

    DoCmd.Hourglass False
    Exit Sub

LinkTables_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Exit Sub

End Sub

=======================================
‘ End code behind a startup form:
=======================================


=======================================
‘ Start code in a class module named BEInfo:
=======================================

Private pstrFullName As String
Private pstrFileName As String
Private pstrFilePath As String
Private pstrFilePathOnly As String
Private pstrDrive As String

Property Let FullName(strFullName As String)

    pstrFullName = strFullName
    pstrFilePath = pstrFullName

    Do While Right$(pstrFilePath, 1) <> "\"
        pstrFilePath = Left$(pstrFilePath, _
            Len(pstrFilePath) - 1)
    Loop

    pstrFileName = Mid$(pstrFullName, _
        Len(pstrFilePath) + 1)

    pstrFilePathOnly = Mid$(pstrFilePath, _
        Len(pstrDrive) + 1)

    pstrDrive = Left$(pstrFullName, _
        InStr(pstrFullName, ":"))

End Property

Property Get FileName() As String
    FileName = pstrFileName
End Property

Property Get FilePath() As String
    FilePath = pstrFilePath
End Property

Property Get FilePathOnly() As String
    FilePathOnly = pstrFilePathOnly
End Property

Property Get Drive() As String
    Drive = pstrDrive
End Property

Property Get Name() As String
    Name = pstrFullName
End Property

=======================================
‘ End code in a class module named BEInfo:
=======================================

=======================================
‘ Start code in a standard module:
=======================================

Option Compare Database
Option Explicit

Function VerifyLink() As Boolean
    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.Table
    Dim strTemp As String

    Set cat = New ADOX.Catalog
    With cat
        Set .ActiveConnection = CurrentProject.Connection

        On Error Resume Next

        For Each tdf In .Tables
            If tdf.Type = "LINK" Then
                strTemp = tdf.Columns(0).Name
                If Err.Number Then
                    Exit For
                End If
            End If
        Next tdf
    End With

    VerifyLink = (Err.Number = 0)

End Function

Function ReLink(strDir As String, DefaultData As Boolean) _
    As Boolean
    Dim cat As ADOX.Catalog
    Dim tdfRelink As ADOX.Table
    Dim oBEInfo As BEInfo
    Dim strPath As String
    Dim strName As String
    Dim intCounter As Integer
    Dim vntStatus As Variant

    vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

    Set cat = New ADOX.Catalog
    Set oBEInfo = New BEInfo

    With cat
        .ActiveConnection = CurrentProject.Connection
        oBEInfo.FullName = strDir
        strPath = oBEInfo.FilePathOnly
        strName = Left(oBEInfo.FileName, InStr(oBEInfo.FileName, ".") - 1)

        On Error Resume Next
        Call SysCmd(acSysCmdInitMeter, "Linking Data Tables", .Tables.Count)

        For Each tdfRelink In .Tables
            intCounter = intCounter + 1
            Call SysCmd(acSysCmdUpdateMeter, intCounter)
            If .Tables(tdfRelink.Name).Type = "LINK" Then
                tdfRelink.Properties("Jet OLEDB:Link Datasource") = strPath & strName & IIf(DefaultData, "Data.Mdb", ".mdb")
            End If
            If Err.Number Then
                Exit For
            End If
        Next tdfRelink
    End With

    Call SysCmd(acSysCmdRemoveMeter)

    vntStatus = SysCmd(acSysCmdClearStatus)

    ReLink = (Err.Number = 0)

End Function

HTH,

Bob

Reply With Quote
  #3 (permalink)  
Old February 24th, 2006, 12:26 AM
Authorized User
 
Join Date: Feb 2006
Location: , , Singapore.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks. I'll go try that out!

Scripts82
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Front end Vs Back end ricmar Access VBA 3 May 27th, 2008 02:36 PM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM
creating Front - End anukagni Access 8 April 22nd, 2006 04:11 AM



All times are GMT -4. The time now is 05:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.