Solved

Access 2007 Split DB - Linked Table Manager

Posted on 2013-11-21
30
438 Views
Last Modified: 2013-11-22
I have an Access 2007 Split DB (front & backend). I am distributing the front end to different users in different sites. The issue is re-linking the tables. How do I program when the front end is opened by the user that it checks and re-links their backend tables for them?
0
Comment
Question by:CMILLER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 9
  • 5
30 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39667350
There are plenty of free utilities available to do this.  Check out the downloads for Relinker samples here:
http://www.jstreettech.com/downloads.aspx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39667355
<I am distributing the front end to different users in different sites.>

is the BE the same for all your users?
0
 

Author Comment

by:CMILLER
ID: 39667366
mbizup: I cant install software.



capricorn1: Yes, BE will be the same.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 61

Expert Comment

by:mbizup
ID: 39667405
This blog post explains how to relink at startup... similar code to what you'll find in those samples:
http://blogs.office.com/b/microsoft-access/archive/2012/08/03/how-to-automatically-relink-microsoft-access-tables-.aspx
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39667408
place this code in a regular module

Sub RelinkTables()
On Error GoTo Relink_Error
Dim dbs As DAO.Database, strBEPath As String
Dim tdf As DAO.TableDef
Set dbs = CurrentDb()
strBEPath = "<complete path to your backend>"
Dim dbData  As DAO.Database
Set dbData = DBEngine.OpenDatabase(strBEPath)
     For Each tdf In dbs.TableDefs
          If tdf.Connect <> "" Then
               tdf.Connect = ";DATABASE=" & strBEPath
               tdf.RefreshLink
          End If
     Next
dbData.Close
Set dbData = Nothing

Relink_Error:
     If Err.Number = 3024 Then
          MsgBox "Unable to connect to data tables!" & vbCrLf & "Closing Application", vbQuestion, "DATA TABLES MISSING!"
          Err.Clear
           Application.Quit
     End If
End Sub


you can call the sub in the open or load event of your startup form.



.
0
 

Author Comment

by:CMILLER
ID: 39668939
"you can call the sub in the open or load event of your startup form."

How do I do that?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39668984
place this codes in the open event of your startup form

private sub Form_Open(cancel as integer)

RelinkTables

end Sub
0
 

Author Comment

by:CMILLER
ID: 39669103
I am getting an error here

Private Sub Form_Open(Cancel As Integer)
code-error.JPG
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39669131
what is the error?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39669136
Did you read/follow the detailed instructions at the link I posted in my last comment? (The code that cap1 posted is very similar).
0
 

Author Comment

by:CMILLER
ID: 39669185
I attached the error
0
 

Author Comment

by:CMILLER
ID: 39669191
mbizup: I just looked at the link you posted. The issue is that I will have the backend in a network folder and the front end will be on several users desktops.

one of the requirements for your code is that both files have to be in the same folder.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39669208
<I attached the error >

that does not tell us anything.. is there any error message? post them here
0
 

Author Comment

by:CMILLER
ID: 39669270
no error message, but it seemed to work after I had both files in the same network folder but thats not how I will them.

The backend will be on a network share and the front end will be on users desktops.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39669286
The idea there is to relink a single copy of the front end before distributing it to the users, rather than relinking separate copies on the users individual computers.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39669298
With that approach, you relink *once* and then distribute a properly linked FE to your users.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39669301
<no error message, but it seemed to work after I had both files in the same network folder but thats not how I will them.>

it will work where ever you place the FE. as long as you specify in the code the path to the BE


strBEPath = "<complete path to your backend>"


can  you post what did you place as the path to the BE
0
 

Author Comment

by:CMILLER
ID: 39669621
strBEPath = "\\Server-name\Files\#DATABASES\DB-REMOTE\TEST1\REMOTE_be.accdb"
0
 

Author Comment

by:CMILLER
ID: 39669624
I added the action in my autoexec macro.

RunCode - RelinkTables()
OpenForm - Main-Menu,form,,,,Normal
0
 

Author Comment

by:CMILLER
ID: 39669628
The issue is that I am distributing the FE to users on different networks, while maintaining the master FE for updates.
0
 

Author Comment

by:CMILLER
ID: 39669634
Do I need a buttons on my main menu for the different users network, so they run the script for there network.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39669647
so you are calling the codes from a macro, then you need to change the "SUB" to "FUNCTION"


change this

Sub RelinkTables()

with

Function RelinkTables()
0
 

Author Comment

by:CMILLER
ID: 39669816
Changed to Function but still didnt work.
0
 

Author Comment

by:CMILLER
ID: 39669845
Ok, I got it working. There was an issue with module names and what I had in the autoexec.

When I open the FE from my desktop it connects to the path in the RelinkTables.

I think I will change it to buttons on the main screen for the different users. I will create seperate relinktables and buttons.

Thanks, for the help!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39669846
where did you place the "RelinkTables" codes  ?
0
 

Author Comment

by:CMILLER
ID: 39670044
I created the module "Function Name RelinkTables()"

With the code that you first gave me.

Then added the first line action "RunCode" with "RelinkTables()" for the arguments.
0
 

Author Comment

by:CMILLER
ID: 39670076
Then added the first line action "RunCode" with "RelinkTables()" for the arguments in the autoexec macro.
0
 

Author Comment

by:CMILLER
ID: 39670095
Sorry, something I just realized is that when it wasnt working I wasnt getting the Relink_Error msgbox.
0
 

Author Comment

by:CMILLER
ID: 39670097
I need to add a msg after a successful relink
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39670106
Sub RelinkTables()
On Error GoTo Relink_Error
Dim dbs As DAO.Database, strBEPath As String
Dim tdf As DAO.TableDef
Set dbs = CurrentDb()
strBEPath = "<complete path to your backend>"
Dim dbData  As DAO.Database
Set dbData = DBEngine.OpenDatabase(strBEPath)
     For Each tdf In dbs.TableDefs
          If tdf.Connect <> "" Then
               tdf.Connect = ";DATABASE=" & strBEPath
               tdf.RefreshLink
          End If
     Next
dbData.Close
Set dbData = Nothing

'add this line
if err=0 then
msgbox "Linking process successful"
end if


Relink_Error:
     If Err.Number = 3024 Then
          MsgBox "Unable to connect to data tables!" & vbCrLf & "Closing Application", vbQuestion, "DATA TABLES MISSING!"
          Err.Clear
           Application.Quit
     End If
End Sub
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question