473,290 Members | 1,897 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,290 software developers and data experts.

Automatically Relink Frontend to 2 Backends via form

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\rolodex_be
C:\users\bobbye\desktop\timeclock_be
G:\ rolodex_be
G:\timeclock_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

✓ answered by NeoPa

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
  8.  
  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).

3 10161
NeoPa
32,554 Expert Mod 16PB
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
NeoPa
32,554 Expert Mod 16PB
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
  8.  
  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
NeoPa
32,554 Expert Mod 16PB
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
  8.  
  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

3
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...
1
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...
0
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...
2
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...
1
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....
8
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...
1
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...
1
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...
4
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.