X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Fe/be Saving Linked Table Info So That I Don't Have To Keep Redoing It When Deploying New Fe, Access 2007    
post Jan 14 2018, 12:59 AM

Posts: 206
Joined: 29-November 13

Hi Guys,

I will try to explain the issue I have the best I can.

I have a split database with the databases sitting on the Local C:\RPR\Backend drive of a PC...lets called it PC_1.

The FE is also on the same PC_1 in the C:\RPR\.

I have another PC called PC_2 that I have exactly the same folder structure.

I have mapped the PC_1 C:\RPR\Backend folder as Z:\RPR\Backend on PC_2.

I use the JStreetAccessRelinker2 code to handle the linking of the FE on PC_2 to Z:\RPR\Backend.

All of the above is working well and I have PC_1 and PC_2 sharing the same BE.

The issue I have is that if I update the FE on PC_2, I need to manually relink the BE again. I am assuming that this is due to the linked database information is stored in one of the MSys tables in the FE.

Is there a way that I can save the location of the BE databases (I have 3) so that I don't have to manually relink them each time I update the FE?

Thanks for any help and I hope I have explained myself


Go to the top of the page
post Jan 14 2018, 04:21 AM

Posts: 293
Joined: 3-May 17
From: France

Hi Lateral,
Please note this is just theory for me as yet, so I'm just pointing at possibles I've read about..

"UNC-naming makes for a more "robust" set-up."

"Persistent connection" (also performance-related) is strongly recommended by some people who's expertise I value, and is sometimes suggested in response to problems with tables coming unlinked.

I found the links by searching UtterAccess for "mapping linked tables", you could do a better-worded search to get more precise results for your case.

I seem to remember some versions of Access have difficulty working over networks - try putting your version in the search terms. Are all the PC's involved in this issue using access 2007?
Also, there are some tools available in the UA code archive for automating front-end updates.

Good luck.
Go to the top of the page
post Jan 14 2018, 05:27 AM

Posts: 206
Joined: 29-November 13

Thanks mate
Go to the top of the page
post Jan 14 2018, 08:06 AM

UA Admin
Posts: 34,335
Joined: 20-June 02
From: Newcastle, WA

The solution, as previously suggested is to use the full, UNC path, not a mapped path for all linked accdbs. Mapped drives are handy for some things, of course, but this isn't one of them.

This sounds like you have a peer-to-peer network, not a domain, and that one of the two computers is doing double-duty as "the server" for this purpose? If so, that means the computer hosting the BE must remain powered on and accessible at all times, correct?

Go to the top of the page
post Jan 14 2018, 02:34 PM

Posts: 206
Joined: 29-November 13

Hi Grover

Yes, I am running a small peer to peer Work-group on 2 PC's running Windows 10 Home edition and one of the PC's is doubling up as a "the server".

I don't think using UNC will solve the issue.

I suppose the following is what I want to achieve:

1. On PC_2, relink to the Backend on Z:\RPR\Backend
2. At the same time somehow save the Z:\RPR\Backend to a table so that it can be easily accessed the next time the FE is opened. I have an existing setting that sets if this PC is a Master or a Slave. In this case, PC_2 will be a Slave.

I suppose the question therefore is, "How do I save the paths that are being used for linking the BE and then how do I take that data and copy it I think the MSysObjects (I think) table?)

Thanks for your help with this.


Go to the top of the page
post Jan 14 2018, 02:45 PM

Access Wiki and Forums Moderator
Posts: 74,201
Joined: 19-June 07
From: SunnySandyEggo

Hi Greg,

I can't see how using UNC will not work for you; but if you want to use mapped drives, take a look at this demo in the Code Archive. Hopefully, it will give you an idea how to store and use the BE link infomation.

Hope it helps...
Go to the top of the page
post Jan 14 2018, 03:07 PM

Posts: 206
Joined: 29-November 13

Hi Guys

After some more Googling I think I have found exactly what I need.

Here is the code that I have just tested and it seems works perfectly!

Dim Db As DAO.Database
Dim Tdf As DAO.TableDef
Set Db = CurrentDb
For Each Tdf In Db.TableDefs
If Tdf.Connect<>"" Then
Tdf.Connect = Replace(Tdf.Connect, "\Public\", "\")
End If
Next Tdf
Set Tdf = Nothing
Set Db = Nothing

Oh, and I use this query to check to make sure that the the new folder\path has been changed:

SELECT DISTINCTROW msysobjects.Name, msysobjects.Database, msysobjects.Connect
FROM msysobjects WHERE (((msysobjects.Type)=6 Or (msysobjects.Type) Like "dsn*")) ORDER BY msysobjects.Database;

Thanks for your all of your help.

This post has been edited by Lateral: Jan 14 2018, 03:08 PM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th January 2019 - 04:55 PM