• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

Access 2007 Split DB - Linked Table Manager

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
CMILLER
Asked:
CMILLER
  • 16
  • 9
  • 5
1 Solution
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
<I am distributing the front end to different users in different sites.>

is the BE the same for all your users?
0
 
CMILLERAuthor Commented:
mbizup: I cant install software.



capricorn1: Yes, BE will be the same.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
CMILLERAuthor Commented:
"you can call the sub in the open or load event of your startup form."

How do I do that?
0
 
Rey Obrero (Capricorn1)Commented:
place this codes in the open event of your startup form

private sub Form_Open(cancel as integer)

RelinkTables

end Sub
0
 
CMILLERAuthor Commented:
I am getting an error here

Private Sub Form_Open(Cancel As Integer)
code-error.JPG
0
 
Rey Obrero (Capricorn1)Commented:
what is the error?
0
 
mbizupCommented:
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
 
CMILLERAuthor Commented:
I attached the error
0
 
CMILLERAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<I attached the error >

that does not tell us anything.. is there any error message? post them here
0
 
CMILLERAuthor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
With that approach, you relink *once* and then distribute a properly linked FE to your users.
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
CMILLERAuthor Commented:
strBEPath = "\\Server-name\Files\#DATABASES\DB-REMOTE\TEST1\REMOTE_be.accdb"
0
 
CMILLERAuthor Commented:
I added the action in my autoexec macro.

RunCode - RelinkTables()
OpenForm - Main-Menu,form,,,,Normal
0
 
CMILLERAuthor Commented:
The issue is that I am distributing the FE to users on different networks, while maintaining the master FE for updates.
0
 
CMILLERAuthor Commented:
Do I need a buttons on my main menu for the different users network, so they run the script for there network.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
CMILLERAuthor Commented:
Changed to Function but still didnt work.
0
 
CMILLERAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
where did you place the "RelinkTables" codes  ?
0
 
CMILLERAuthor Commented:
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
 
CMILLERAuthor Commented:
Then added the first line action "RunCode" with "RelinkTables()" for the arguments in the autoexec macro.
0
 
CMILLERAuthor Commented:
Sorry, something I just realized is that when it wasnt working I wasnt getting the Relink_Error msgbox.
0
 
CMILLERAuthor Commented:
I need to add a msg after a successful relink
0
 
Rey Obrero (Capricorn1)Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 16
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now