473,573 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Automatically Relink Frontend to 2 Backends via form

1 New Member
I use two access databases. 1 Rolodex (Client Info and related jobs, etc) - 2 is Timeclock (Employee timesheets with hours, rates etc and all necessary info for creating the various forms and reports for distribution). From time to time I have to take the above home to make changes to the frontend. Right now I use the built in Linked Table Manager and would like to be able to automate the process.
All frontend and backends are located in the same folder at Office (on the server) or my Desktop when Iím at home. Iím only dealing with a total of 2 locations, 2 frontend and 2 backends. (Right now, Iím just testing my home usage (C) not the server in the office (G) as I donít know what the path is yet.
I created a table that contains 4 records:
C:\users\bobbye \desktop\rolode x_be
C:\users\bobbye \desktop\timecl ock_be
G:\ rolodex_be

The Rolodex uses one backend called Rolodex_be. I created a form to select a file and run a relink function that I found online and it works fine.
The Timeclock uses both the Timeclock_be and the Rolodex_be backends. I copied the same function, table and form to Timeclock. I selected the timeclock_be on the form thinking it would run through the function and connect to the timeclock_be (It didnít) and then run it again to connect the Rolodex_be but it didnít work.
How do I get Timeclock FE to connect to both backends? Iím not a programmer so please keep your response as simple as possible

Here is the program I used.
(I made LnkDataBase Public so that I could change the name of the backend as needed via a form
Expand|Select|Wrap|Line Numbers
  1. Sub Relinktables()
  2. Dim dbs As DAO.Database
  3. Dim tdf As DAO.TableDef
  4. Dim strTable As String
  5. Set dbs = CurrentDb()
  6. For Each tdf In dbs.TableDefs
  7.     If Len(tdf.Connect) > 1 Then 'Only relink linked tables
  8.         If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
  9.             If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
  10.                 strTable = tdf.Name
  11.                 dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
  12.                 dbs.TableDefs(strTable).RefreshLink
  13.             End If
  14.         End If
  15.     End If
  16. Next tdf
  17. End Sub
Mar 3 '23 #1
3 10206
32,564 Recognized Expert Moderator MVP
Hi Bobby.

Welcome to Bytes.com :-)

I have just the thing for you, but I will need to dig it out as I developed it for a previous client and no longer have access to their servers to get it easily as I used to. That is to say - I don't know if I still have access and wouldn't feel comfortable jumping on their systems without specific permission anyway.

It's late in the day here now but I'll try to get on to it tomorrow. I'm sure I have a usable copy somewhere. Essentially, as long as all linked databases are in the same folder the main project (FE) file is in then it finds them and relinks seamlessly.

If you don't here back before the weekend then feel free to bump this thread to grab my attention and kick me into finding what you need.
Mar 3 '23 #2
32,564 Recognized Expert Moderator MVP
Hi Bobby.

This was harder to find than I'd expected :-(

Let's start anyway with the code to relink a table (originally with a link that points to a single old file) to point to the new version, found in the same folder the current database was opened from. NB. I understand you may have multiple tables linked to such files so there will be more to follow which allows you to handle calling this procedure for all the various tables.
Expand|Select|Wrap|Line Numbers
  1. 'RelinkTable() ensures that the specified (Jet-linked) table, currently linked
  2. '   to one file, will be relinked to a file with the same name, but in the same
  3. '   folder where this current database was opened from.
  4. '   Returns True if the relink was successful (or False if not).
  5. Public Function RelinkTable(ByVal strTable As String, _
  6.                             Optional ByRef dbVar As DAO.Database) As Boolean
  7.     Dim strPath As String, strOldPath As String, strConnect As String
  9.     If dbVar Is Nothing Then Set dbVar = CurrentDb()
  10.     With dbVar
  11.         strPath = Left(.Name, InStrRev(.Name, "\"))
  12.         With .TableDefs(strTable)
  13.             If (.Attributes And dbAttachedTable) = 0 Then Exit Function
  14.             strPath = strPath & Mid(.Name, InStrRev(.Name, "\") + 1)
  15.             If Not Exist(strPath) Then Exit Function
  16.             strOldPath = Split(Split(.Connect, "DATABASE=")(1), ";")(0)
  17.             If strPath <> strOldPath Then _
  18.                 .Connect = Replace(.Connect, strOldPath, strPath)
  19.             On Error Resume Next
  20.             Call Err.Clear
  21.             Call .RefreshLink
  22.             RelinkTable = (Err = 0)
  23.         End With
  24.     End With
  25. End Function
NB. I've tried to ensure none of the code here requires access to anything else that I haven't shared so please report at any time if you find the code as written doesn't compile (Before Posting (VBA or SQL) Code provides some basic instructions for compiling etc).
Mar 3 '23 #3
32,564 Recognized Expert Moderator MVP
Now we simply need to call this for all of your linked tables where this is required. This can be as simple as a string containing a list of the table names but let us know if you're thinking of another way of identifying the tables required.
Expand|Select|Wrap|Line Numbers
  1. 'RelinkHomeTables() ensures all tables that need to be relinked to Jet/ACE
  2. '   databases held in the same folder as the current database get so relinked.
  3. Public Sub RelinkHomeTables()
  4.     Dim lngCount As Long
  5.     Dim strArray() As String
  6.     Dim varItem As Variant
  7.     Dim dbVar As DAO.Database
  9.     Set dbVar = CurrentDb()
  10.     strArray = Split("TableA,TableB,TableC,TableD", ",")
  11.     For Each varItem In strArray
  12.         If RelinkTable(strTable:=CStr(varItem), dbVar:=dbVar) Then _
  13.             lngCount = lngCount + 1
  14.     Next varItem
  15.     If lngCount < UBound(strArray) Then
  16.         'You have a problem.
  17.     End If
  18. End Sub
NB. I also updated the earlier post to handle checking first that the new database is not the same as the original one before trying to relink.
Mar 3 '23 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: paul b | last post by:
Hello, I have a small problem in PHP an perhaps someone can help me: I have a simple HTML login page with a username and a password field, as well as a submit button, which I have to use for login(I cannot simply replace it by a php-page :-(). is it possible to call this page via a hyperlink from a php-script and to directly fill the...
by: Shamet | last post by:
Dear Listmembers, I am setting up data entry for a questionnaire. In the Form I have created, I have 2 pages with 50 questions on each page. My question is: How can I go from the last question on page to the first question on page 2 automatically without having to use the mouse? I have been trying but I could not find out. Thank you very...
by: Rose | last post by:
I have a database table with fields Proficiency Due Date and Proficiency Recvd Date. The Proficiency Due Date is already populated. When I enter the Prof Recvd Date, I want to automatically add another entry into the table and automatically update the Proficiency Due Date of the new entry to be current Proficiency Date + 365. Is this...
by: jmpinto | last post by:
I have a field on a form that I would like to automatically generate a number, in sequential order, each time a new record is created, and be stored in a field. The problem is that I do not want this field to be an autonumber field because if a new record is opened by mistake and then not used, the autonumber's field would not be in...
by: jl2886 | last post by:
Private Sub AM_Best_Rating_Click() Me.AM_Best_Rating = DLookup("", "ICNameRating", "=" & Me.Insurance_Company) End Sub Me.AM_Best_Rating the value I want to be automatically input. ICNameRating is the name of the table ICName is the the name of the reference value for the AM_Best_Rating in the table. (I have a table which lists the...
by: printline | last post by:
Hello all I have a form, where the user can choose how many delivery days he wants on his order. Lets say he chooses 2 days. Can i in some way put in a script that automatically sets in the date....? Ex. A customer orders something on the 6th of december, and he wants it on a two days service. The date that then should appear as his...
by: Yousaf Shah | last post by:
Hi everybody my question is very simple (hope so) I have two forms named PatientData and NOK. PatientData form is based on table PatientData with PatientID as primary key. NOK form is based on a Table NOK with its own primary key ID and has a field PatientID that is joined with PatientID in PatientData table. I have a cmdButton_NOK on form...
by: isaace | last post by:
I have an agreement template in a MS access report. The report's fields are populated by the form's records. my goal is to automate the report to filter to the agreement # to what ever the current agreement # on the form is. i do not want to have to enter the agreement # every time i click on the cmd function to preview the report. below is the...
by: tamcq | last post by:
When my database is opened, it starts with the form Patients.Page 2 of the the Patients form contains a subform called New Record. This New Record subform is where you put entries about the medications taken by a particular patient. The medications are categorized into two: current and past, depending on the specific checkbox that is checked. ...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.