UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Backend Link, Office 2007    
 
   
Belti
post Mar 22 2012, 06:14 AM
Post #1

UtterAccess Addict
Posts: 204
Hi

I have created an access database for an assignment and I have split the database into a front-end and back-end. This was not particularly required since both the BE and FE are in the same folder (i just split them so that in the documentation of the project i highlight the benefits of splitting the database).

My only concern is the following: when I tested the database on another pc to ensure everything is working fine, I had to re-link the tables in the FE. Is there anything I can do to prevent having to relink the tables every time I try to run the database on another machine?

Thank you
Go to the top of the page
 
+
Doug Steele
post Mar 22 2012, 06:31 AM
Post #2

UtterAccess VIP
Posts: 17,636
From: Don Mills, ON (Canada)
You're always going to have to relink the tables. However, you can add code that runs at startup and checks whether the tables are correctly linked, and relink them if not so that the user doesn't have to do anything.
Go to the top of the page
 
+
Belti
post Mar 22 2012, 09:19 AM
Post #3

UtterAccess Addict
Posts: 204
Hi

Thank you for your reply. Are there any particular keywords that I should search for to start researching how to go about creating such code.

Thanks in advance
Go to the top of the page
 
+
Doug Steele
post Mar 22 2012, 09:45 AM
Post #4

UtterAccess VIP
Posts: 17,636
From: Don Mills, ON (Canada)
Here's a routine I've used in the past:

CODE
Public Function RelinkTables()

On Error GoTo Err_RelinkTables
  
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strCurrLinkage As String
Dim strFrontendPath As String
Dim strExpectedBackend As String
  
    Set dbCurr = CurrentDb()
    strFrontendPath = dbCurr.Name
    strFrontendPath = Left$(strFrontendPath, Len(strFrontendPath) - Len(Dir$(strFrontendPath)))
    strExpectedBackend = ";Database=" & strFrontendPath & "RSSFeedsData.mdb"
  
' Make the simplifying assumption that all linked tables
' will point to the same back-end
    For Each tdfCurr In dbCurr.TableDefs
        With tdfCurr
            strCurrLinkage = .Connect
            If Len(strCurrLinkage) > 0 Then
                If Left(strCurrLinkage, 5) <> "Excel" Then
                    If StrComp(strCurrLinkage, strExpectedBackend, vbTextCompare) <> 0 Then
                        .Connect = strExpectedBackend
                        .RefreshLink
                    End If
                End If
            End If
        End With
    Next tdfCurr
  
End_RelinkTables:
    Exit Function
  
Err_RelinkTables:
    MsgBox Err.Number & ": " & Err.Description
    Resume End_RelinkTables
  
End Function


You'd need to change the line of code

CODE
    strExpectedBackend = ";Database=" & strFrontendPath & "RSSFeedsData.mdb"


to reflect the name of your backend. Note that the reason for the check If Left(strCurrLinkage, 5) <> "Excel" Then is because the application I pulled that code from also has links to Excel spreadsheets, not just the backend database. Theoretically you only need

CODE
Public Function RelinkTables()

On Error GoTo Err_RelinkTables
  
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strCurrLinkage As String
Dim strFrontendPath As String
Dim strExpectedBackend As String
  
    Set dbCurr = CurrentDb()
    strFrontendPath = dbCurr.Name
    strFrontendPath = Left$(strFrontendPath, Len(strFrontendPath) - Len(Dir$(strFrontendPath)))
    strExpectedBackend = ";Database=" & strFrontendPath & "RSSFeedsData.mdb"
  
' Make the simplifying assumption that all linked tables
' will point to the same back-end
    For Each tdfCurr In dbCurr.TableDefs
        With tdfCurr
            strCurrLinkage = .Connect
            If Len(strCurrLinkage) > 0 Then
                If StrComp(strCurrLinkage, strExpectedBackend, vbTextCompare) <> 0 Then
                    .Connect = strExpectedBackend
                    .RefreshLink
                End If
            End If
        End With
    Next tdfCurr
  
End_RelinkTables:
    Exit Function
  
Err_RelinkTables:
    MsgBox Err.Number & ": " & Err.Description
    Resume End_RelinkTables
  
End Function
Go to the top of the page
 
+
Belti
post Mar 22 2012, 10:42 AM
Post #5

UtterAccess Addict
Posts: 204
thank you very much for your help, I very much appreciate it.
Go to the top of the page
 
+
« Next Oldest · Access Runtime, Packaging & Deployment · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 08:24 AM