[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2007 Split DB - Linked Table Manager

Posted on 2013-11-21
30
Medium Priority
?
459 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

656 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