X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Re-linking Frontend To Backend    
 
   
Kamulegeya
post Apr 17 2011, 02:51 PM
Post #1

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



I have database which i use to track project expenditures. For better performance, i have been advised by members on this forum to split the database into FE and BE. I have done that but the challenge i have now is that if i change the BE location say from Desktop to My Documents, The FE doesnt load so as to enable me to open the linked table manager to link the FE to BE. It only loads again if i return the BE to the original location.
What is the solution to this? Because i am sure during the use of the database, the location of BE may need to change say due to un-availability of the LAN.
onald.
Go to the top of the page
 
+
theDBguy
post Apr 17 2011, 03:38 PM
Post #2

Access Wiki and Forums Moderator
Posts: 59,390
From: SoCal, USA



Hi Ronald,
That version of Access are you using?
Odon't have a link for you right now but do a search on "auto FE relinker" in the code archive for some demos on how to automatically relink the FE to the Be. If you want to do it manually, you can use the Linked Table Manager.
Hope thy helps...
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 17 2011, 03:45 PM
Post #3

UtterAccess Editor
Posts: 9,114
From: South coast, England



Hi Ronald
This is the code I use to relink the backend:
CODE
Public Function LinkBackEnd(strFilename As String) As Byte
On Error GoTo err_proc
'Function creates linked tables to all tables (except "msys*" tables) in BE database defined by strFilename
    Dim strSQL As String
    Dim DBS As DAO.Database, tdf As TableDef
    Dim dbs1 As DAO.Database, tdf1 As TableDef
    Dim strTblName As String
  
    Set DBS = OpenDatabase(Application.CurrentProject.FullName)
    Set dbs1 = DBEngine.Workspaces(0).OpenDatabase(strFilename)
    
    On Error Resume Next
    For Each tdf1 In dbs1.TableDefs
        strTblName = tdf1.Name
        Select Case strTblName
        Case ""   'Add any tables in BE you do NOT want linked here, separated by commas
        
        Case Else
            If Left(strTblName, 4) <> "msys" Then
               DBS.TableDefs.Delete strTblName
                Set tdf = db.CreateTableDef(strTblName)
                tdf.Connect = ";DATABASE=" & strFilename
                tdf.SourceTableName = strTblName
                db.TableDefs.Append tdf
            End If
        End Select
    Next tdf1
    On Error GoTo err_proc
    LinkBackEnd = 1
    
exit_proc:
On Error Resume Next
    Set dbs1 = Nothing
    Set DBS = Nothing
    Exit Function
    
err_proc:
    MsgBox err.Description, , strTitle
    Resume exit_proc
    
End Function


.

Call the code passing the new path and file name of the BE you want to link to
CODE
If LinkBackEnd(BEFileNameAndPath) = 0 then
    Msgbox "Link to BE Failed"
    Exit Sub
End if

The code deletes the links to any table that appears in the new BE File and then links the table (in the new BE) to the FE file.
hth
EDIT:
To clarify: the variable 'strTitle' in the above code is a global variable or constant that is only used as an indentifier - it can be safely deleted from the above code.
the variable 'db' as in Set tdf = db.CreateTableDef(strTblName) is a a 'self healing' variable that is set to CurrentDb.
In the code 'db' can be replaced with CurrentDb wherever it occurs.
Go to the top of the page
 
+
CyberCow
post Apr 17 2011, 10:08 PM
Post #4

UdderAccess Admin + UA Ruler
Posts: 19,207
From: Upper MI



You may find this demo useful: Swap/Relink Between LIVE and TEST Data
Hope this helps
Go to the top of the page
 
+
HiTechCoach
post Apr 17 2011, 11:25 PM
Post #5

UtterAccess VIP
Posts: 18,680
From: Oklahoma City, Oklahoma



You can also use the build in link table manager. The trick will be to open the database while holding down the shift key. This will bypass your start up rountine. Now you can use the Link Table Manger to relink your tables to the new location.
Go to the top of the page
 
+
Kamulegeya
post Apr 18 2011, 12:37 AM
Post #6

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



Thanks for the replies.
o pere_de_chipstick
Thank u for the code u have provided. My question is where should i put the code? an autoexec Macro? I have tried to run it in a module and access crushed and re-started. And where do i supply the path to the back end in the code?
To HiTechCoach i tried to hold down the shift key to by pass any routine as you suggest, but the FE doesn't open once the BE is mo
Go to the top of the page
 
+
HiTechCoach
post Apr 18 2011, 12:50 AM
Post #7

UtterAccess VIP
Posts: 18,680
From: Oklahoma City, Oklahoma



Are you using the full version of Access or the Runtime?
If the full version of Access then do you have the bypass key disabled?
If using he Access Runtime then you will need to use VBA code.
Using VBA code to relink the tables:
What I do in my start up routine is to test to see if the back end is present. If not found I display a file browse dialog to allow the user to select the new location. The I use VBA code re relink to the new location.
Also see: How to Manage Your Linked Tables
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 18 2011, 03:54 AM
Post #8

UtterAccess Editor
Posts: 9,114
From: South coast, England



Hi Kamulegeya
I have some code that is run from the AutoExec function that tests the BE is linked and if so that the BE is available:
CODE
strFileName = Nz(DLookup("Database", "MSysObjects", "Name='tblAdmin'"), "") 'tblAdmin is table in BE
                                                                             'normally linked to FE
                                                                            '- rename table
If strFileName = "" Or Dir(strFileName) = "" Then        'If not linked or BE not available,
    strFileName = ""
    'Open save filename dialog box using windows API
    FindSaveFileName "Database files " & Chr(0) & "YourBEdbName.mdb", _
                     "Locate BE Data File on Server", strFileName
    If strFileName = "" Then Quit                 'User cancelled - close db
    if LinkBackEnd(strFileName) = 0 then Quit     'Link function failed - close db
End if
'Your start up code here

hth
Go to the top of the page
 
+
Kamulegeya
post Apr 18 2011, 10:57 AM
Post #9

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



I am using the full version of access 2007.
pere_de_chipstick i need help on calling the API GetOpenFileName in the code you gave me. I have tried to use but get a compile Err. Wrong number of arguments or invalid property assignment.
Here is the wrapper function (Got from Access 2010 Programmer's reference)
Public Function GetFileName() As String
Dim ofn As OPENFILENAME
Dim rc As Long
' set parameters
ofn.lStructSize = LenB(ofn)
ofn.lpstrFile = String(260, 0)
ofn.nMaxFile = LenB(ofn.lpstrFile) - 1
ofn.hwndOwner = Application.hWndAccessApp()
ofn.lpstrTitle = "Select a file to open"
'call the function
rc = GetOpenFileName(ofn)
'if the return code is > 0,
If (rc > 0) Then
GetFileName = Left(ofn.lpstrFile, InStr(ofn.lpstrFile, vbNullChar) - 1)
End If
End Function
THere is the form onload event i want to use to check BE connection. I have used the code you gave me
Private Sub Form_Load()
Dim strFilename As String
On Error GoTo ErrStartUp
strFilename = Nz(DLookup("Database", "MSysObjects", "Name='Users'"), "")

If strFilename = "" Or Dir(strFilename) = "" Then
'Open save filename dialog box using windows API
GetFileName "Database files " & Chr(0) & "UETS_New.accdb", _
"Locate BE Data File on Server", strFilename

If strFilename = "" Then Quit 'User cancelled - close db
If LinkBackEnd(strFilename) = 0 Then Quit 'Link function failed - close db
End If
ErrStartUp:
MsgBox " Err" & Err.Description

End Sub
Go to the top of the page
 
+
theDBguy
post Apr 18 2011, 11:17 AM
Post #10

Access Wiki and Forums Moderator
Posts: 59,390
From: SoCal, USA



Hi,
I was going to give you the link to Bob Larson's Front-End Auto-Update Enabling Tool but I saw that it's already included in the link that HighTechCoach provided to you.
Just wondering if you have tried it because I think it is very straight forward and you wouldn't have to mess too much with trying to make the code work. That is done automatically for you by the tool.
Just my 2 cents...
Go to the top of the page
 
+
ChrisA
post Apr 18 2011, 11:25 AM
Post #11

UtterAccess Addict
Posts: 210
From: Pacific NW - USA



Some more BE relink code examples:
llen Browne: http://allenbrowne.com/ser-13.html
MVPS.org: http://www.mvps.org/access/tables/tbl0009.htm
JStreet Technology: http://www.jstreettech.com/cartgenie/pg_de...erDownloads.asp
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 18 2011, 12:09 PM
Post #12

UtterAccess Editor
Posts: 9,114
From: South coast, England



Hi Ronald
You can see a number of alternative's have been suggested, but just to complete my suggested code I've attached a sample db:
hth
Attached File(s)
Attached File  LinkToDataFile.zip ( 28.57K ) Number of downloads: 64
 
Go to the top of the page
 
+
Kamulegeya
post Apr 18 2011, 01:18 PM
Post #13

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



Thank u Bernie
The code works perfectly
GOD BLESS You
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 18 2011, 02:50 PM
Post #14

UtterAccess Editor
Posts: 9,114
From: South coast, England



You're very welcome - and thank you.
Good luck with your project.
Go to the top of the page
 
+
Kamulegeya
post Apr 19 2011, 09:52 AM
Post #15

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



Bernie i got into trouble and here is my DB .
Thank you for your help.
The Db doesnt have sensitive data.
Attached File  UETS_NewVersion.zip ( 202.81K ) Number of downloads: 21
Go to the top of the page
 
+
HiTechCoach
post Apr 19 2011, 10:48 AM
Post #16

UtterAccess VIP
Posts: 18,680
From: Oklahoma City, Oklahoma



Kamulegeya,
Yegeya,
Your attachment does not have a back end or any linked tables.
Where in your database is the code to relink?
Go to the top of the page
 
+
Kamulegeya
post Apr 19 2011, 10:56 AM
Post #17

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



To HiTechCoach
ernie gave me code which works in the sample he gave me but didnt work in my DB.
Orequested him via message whether he can look at my Db and he accepted.
That is the reason for the upload of the DB.
Thanks
Ronald.
Go to the top of the page
 
+
HiTechCoach
post Apr 19 2011, 12:10 PM
Post #18

UtterAccess VIP
Posts: 18,680
From: Oklahoma City, Oklahoma



Ronald,
understand that part.
I was pointing out that that your database is not split or set up to use the code Bernie posted.
Have you split your database?
It is a lot more helpful if you will post a copy of the database that you have split and have the code for relinking already in the database. This way we can see what you are trying to do. That will allow us to help you learn a lot more.
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 19 2011, 12:12 PM
Post #19

UtterAccess Editor
Posts: 9,114
From: South coast, England



Hi Ronald
hecked over your db, could not see why the changes would not have allowed your db to open. However the db did crash and I had to decompile the db for it to work, you will need to check that all the functionality you need is working.
Onoted that after running the compile function the db asked if I wanted to save a changed form - which seemed odd iconfused.gif
Subsequently
1. split the tables into a new file "UETS BE.accdb"
2. Added a table tblAdmin to the BE file, which is checked by the linking code. (Though I could have as easily changed the table the code looked for to one of the existing tables (I use tblAdmin as you can incorporate a version field for the FE to check)
2. added the linking code "LinkFileCode"
3. Removed the start up form from the Access Options Current Database
4. Added a code line to the DbStartUP function - DoCmd.OpenForm "About", to open the start up form.
5. Modified the code to look only for files with the name UETS BE.accdb to link to.
The attached file has both the FE and BE.
hth
Attached File(s)
Attached File  UETS_BE.zip ( 231.03K ) Number of downloads: 39
 
Go to the top of the page
 
+
Kamulegeya
post Apr 19 2011, 01:12 PM
Post #20

UtterAccess Ruler
Posts: 1,637
From: Kampala,Uganda The Pearl of Africa



Thank you Bernie
It is working now. May be the Db was corrupt that why it failed to work.
Really words cant express my appreciation for your help.
I was totally stuck .
Thank you very much
God Bless
Ronald.
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 18th December 2014 - 11:21 AM