Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: Allow Runtime Users to change links

    Hi,

    I'm currently designing a front end database, which links to some back end tables. I'm trying to find a way of allowing end users (who will be using runtime) to select which back end file they link to.

    Ideally I need them to select from a combo box, or from a command button which data tables they link to.

    I'm struggling with the VB help files as to how to change the Linked Table path and filename.

    Hope someone can help, thanks in advance.

    Cheers
    David

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Welcome David,

    I use this to relink tables in a runtime DB. Not sure if this will help you, as it doesn't allow users to pick and choose which tables, just the location of the backend DB. Maybe you can modify it to meet your needs. I'm not the author, but have tweeked it a bit myself.

    sub to delete and relink
    Code:
     
    Private Sub cmdLinkTables_Click()
    On Error GoTo Err_cmdLinkTables_Click
    	Dim strFileName, strTableName As String
     
    	strFileName = PromptFileName()
     
    	Dim obj As AccessObject, dbs As Object
     
    	Set dbs = Application.CurrentData
    	' Search for open AccessObject objects in AllTables collection.
    	For Each obj In dbs.AllTables
    		strTableName = obj.Name
    		'Some other objects in the .AllTables Collection are not tables
    		If Not (Left(strTableName, 4) = "MSys") Then
    			'1. Delete the current link
    			DoCmd.DeleteObject acTable, strTableName
    			'This MsgBox was used to debug. Comment out or delete as you like
    			'2. Re-Link the table
    			'MsgBox "Linking " & strTableName & "."
    			DoCmd.TransferDatabase acLink, "Microsoft Access", strFileName, _
    				acTable, strTableName, strTableName
    		End If
    	Next obj
    Exit_cmdLinkTables_Click:
    	Exit Sub
    Err_cmdLinkTables_Click:
    	MsgBox Err.Description
    	Resume Exit_cmdLinkTables_Click
    End Sub
    Then I use this to allow is user to browse for the BE DB. (Can copy this into new module).

    Code:
     
    Option Compare Database
    Option Explicit 
    ' (Copy them to the (declarations) section of a module.)
    Public Type OPENFILENAME
    	lStructSize As Long
    	hwndOwner As Long
    	hInstance As Long
    	lpstrFilter As String
    	lpstrCustomFilter As String
    	nMaxCustomFilter As Long
    	nFilterIndex As Long
    	lpstrFile As String
    	nMaxFile As Long
    	lpstrFileTitle As String
    	nMaxFileTitle As Long
    	lpstrInitialDir As String
    	lpstrTitle As String
    	flags As Long
    	nFileOffset As Integer
    	nFileExtension As Integer
    	lpstrDefExt As String
    	lCustData As Long
    	lpfnHook As Long
    	lpTemplateName As String
    End Type
    Public Const OFN_FILEMUSTEXIST = &H1000
    Public Const OFN_HIDEREADONLY = &H4
    Public Const OFN_PATHMUSTEXIST = &H800
    Public Declare Function GetOpenFileName Lib "comdlg32.dll" _
    	Alias "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long
     
    Public Function PromptFileName() As String
    	Dim filebox As OPENFILENAME ' open file dialog structure
    	Dim FName As String		 ' filename the user selected
    	Dim result As Long		 ' result of opening the dialog
     
    	' Configure how the dialog box will look
    	With filebox
    		' Size of the structure.
    		.lStructSize = Len(filebox)
    		' Handle to window opening the dialog.
    		.hwndOwner = 0 'Me.Hwnd
    		' Handle to calling instance (not needed).
    		.hInstance = 0
    		' File filters to make available: Access Databases and All Files
    		.lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
    			vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
    			vbNullChar & vbNullChar
    		'.lpstrCustomFilter is ignored -- unused string
    		.nMaxCustomFilter = 0
    		' Default filter is the first one (Text Files, in this case).
    		.nFilterIndex = 1
    		' No default filename. Also make room for received
    		' path and filename of the user's selection.
    		.lpstrFile = Space(256) & vbNullChar
    		.nMaxFile = Len(.lpstrFile)
    		' Make room for filename of the user's selection.
    		.lpstrFileTitle = Space(256) & vbNullChar
    		.nMaxFileTitle = Len(.lpstrFileTitle)
    		' Initial directory is C:\.
    		.lpstrInitialDir = "C:\" & vbNullChar
    		' Title of file dialog.
    		.lpstrTitle = "Select a File" & vbNullChar
    		' The path and file must exist; hide the read-only box.
    		.flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
    		' The rest of the options aren't needed.
    		.nFileOffset = 0
    		.nFileExtension = 0
    		'.lpstrDefExt is ignored -- unused string
    		.lCustData = 0
    		.lpfnHook = 0
    		'.lpTemplateName is ignored -- unused string
    	End With
     
    	' Display the dialog box.
    	result = GetOpenFileName(filebox)
    	If result <> 0 Then
    		' Remove null space from the file name.
    		FName = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
    		'Debug.Print "The selected file: "; fname
    	End If
     
    	'return the string of the file name
    	PromptFileName = FName
     
    End Function
    Chris

  3. #3
    Join Date
    Mar 2005
    Posts
    3
    Hi Chris,

    Thanks for your help,

    Unfortunately the tables that need to be refreshed have relationships assigned to them on the FE, so I would need to delete each relationship, run the vb code, and then recreate the relationships.

    What I was originally thinking about, was trying to use the connect, sourcetable name, and refreshlinks methods from DAO3.6.

    Would this be an efficient way of dealing with the issue, and would you have any sample code I could use.

    Thanks again.
    David

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    No problem David,

    What you're asking is a bit out of my league, but there's much more clever people around that might have a solution. Anyone?

    Sorry I couldn't be of more help.

  5. #5
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1

    Question

    Quote Originally Posted by daveuk77
    Hi Chris,

    Thanks for your help,

    Unfortunately the tables that need to be refreshed have relationships assigned to them on the FE, so I would need to delete each relationship, run the vb code, and then recreate the relationships.

    What I was originally thinking about, was trying to use the connect, sourcetable name, and refreshlinks methods from DAO3.6.

    Would this be an efficient way of dealing with the issue, and would you have any sample code I could use.

    Thanks again.
    David

    Hey i am stuck in the exact same situation and require ths soln too

    did you get it if yes pl point me in the rt direction
    thanks so much

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    About the relathionships: you don't need to delete the linked table to change it's source. As long as the table name doesn't change, you shouldn't need to change the relationship.

    Here's a snippet of code from something kicking around on my desktop that allows the user to change the linked table locations.

    Other code in the form shows the user where the tables are currently located and allows then to choose a new location using the Access file open dialog box.

    I have a native table (in the FE - not linked) that contains the names of the tables that need to be linked - that's what the recordset is doing, getting the table names.
    Code:
    Function SetNew() As Boolean
    
        Dim rsDat As DAO.Recordset
        Dim tblX As TableDef
        Dim tblY As TableDef
        Dim sC As String
        Dim sT As String
        Dim blDone As Boolean
        Dim iC As Integer
        Dim iT As Integer
        Dim iI As Integer
        
        On Error GoTo Err_Function
        
        DoCmd.Hourglass True
        blDone = False
        Set rsDat = CurrentDb.OpenRecordset("Select * From LinkTables")
        rsDat.MoveLast
        iT = rsDat.RecordCount
        rsDat.MoveFirst
        iC = 0
        sC = ";DATABASE=" & Me.txtNew
        Do
            lblX.Caption = "Table " & Format(iC + 1) & " of " & Format(iT) & " '" & rsDat(0) & "'" & vbCrLf & "Connect='" & sC & "'"
            Me.Repaint
            For Each tblX In CurrentDb.TableDefs
                If tblX.Name = rsDat(0) Then Set tblY = tblX
            Next
            tblY.Connect = sC
            tblY.RefreshLink
            iC = iC + 1
            rsDat.MoveNext
        Loop Until rsDat.EOF
        blDone = True
        
    Exit_Function:
        DoCmd.Hourglass False
        Set rsDat = Nothing
        Set tblX = Nothing
        Set tblY = Nothing
        SetNew = blDone
        Exit Function
        
    Err_Function:
        MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
               "Table " & Format(iC) & " of " & Format(iT) & " '" & rsDat(0) & "'", vbOKOnly, "Error"
        Err.Clear
        Resume Exit_Function
    End Function
    Often, I'll have the FE check to make sure the BE is visible by grabbing the linked table location and using the DIR command to "touch" it. If the BE is not found, then I take action, depending on the app. In this case, I allow the user to find it and reconnect. In other instances, I tell the user "Verify network connection; log out and back in, etc."
    Last edited by tcace; 03-30-06 at 18:44.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Mar 2005
    Posts
    3
    Hi abhichoudhary,

    This is the code I used:

    Code:
    Sub change_links()
    On Error GoTo Change_Links_Error
    
    
    Dim DB As Database
    Dim tbl As TableDef
    Dim Site As String
    Dim Error As Integer
    Dim np As String
    Dim i As Integer
    Dim tbln As String
    Dim lnk As String
    Dim folderspec As String
    Dim fs, f, f1, fc, s, fa
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("C:\Inventory Software\Central Files")
    Set fc = f.Files
    For Each f1 In fc
        fa = f1.Type Like "*Access*"
        If fa = True Then
            s = s & Mid(f1.Name, 7, Len(f1.Name) - 10) & vbCrLf
        End If
    Next
    MsgBox s, vbInformation, "The Following Sites are setup:    "
    
    Set DB = CurrentDb()
    Site = InputBox("Which Site do you want to connect to?", "Site Required")
    If Site = "" Then End
    np = "C:\Inventory Software\Central Files\MIP - " & Site & ".mdb"
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    For i = 0 To DB.TableDefs.Count - 1
         tbln = DB.TableDefs(i).Name
         Set tbl = DB.TableDefs(tbln)
         lnk = tbl.Connect
         If Left(lnk, 9) = ";DATABASE" Then
             tbl.Connect = ""
             tbl.Connect = ";DATABASE=" & np
             tbl.RefreshLink
         End If
     Next
    
    Exit Sub
    
    Change_Links_Error:
    Error = MsgBox("Invalid Site ID", vbRetryCancel, "Error")
    If Error = 4 Then Run (ChangeLinks()) Else Application.CloseCurrentDatabase
    
    End Sub
    
    Function ChangeLinks()
    
    change_links
    
    End Function
    I then created a little one line macro that runs the ChangeLinks function:

    Action: Runcode
    Function Name: ChangeLinks()

    Basically the function goes to the directory specified in:
    Code:
    Set f = fs.GetFolder("C:\Inventory Software\Central Files")
    and brings up a little message box listing the names of all the Access files

    In the next prompt box, you enter the filename, and this assigns the new path and filename to the following variable:

    Code:
    np = "C:\Inventory Software\Central Files\MIP - " & Site & ".mdb"
    which updates all the linked table settings, and automatically refreshes the links.

    There is very basic error trapping that checks you've entered a valid filename, if not, you can retry or exit.

    Hope this helps,

    Cheers
    Dave

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Exacly. In both bases, the key code is:
    Code:
             tbl.Connect = ";DATABASE=" & np
             tbl.RefreshLink
    where tbl is a Table object and np is a string variable with the path and file name of the desired mdb.

    The Connect property tells the FE where the BE is and the RefreshLink action tells it to reconnect.

    There is info in the help file about using the TableDefs collection to loop through all the tables in the FE.

    Have fun.

  9. #9
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1
    thanks guys this is just why i have fallen in love with this forum
    I agree this is the best solution for a person with my level......
    i have implemented the linking though usinG DEV ASHISH's code which i copied straight and called the function on the on load function of my main form

    BUT i wasnt really satisfied as i didnt understand it all clearly and wont be able to write it myself again....this code now i understand and maybe devs code is also on the same lines - i think only it caters to a whole lot of other situations-given time i will be able to......understand and implement it straight out next when i need it and further customise it.

    THANKS GUYS THANKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •