Solved Runtime files and split database

[Dumferling]

I have a database split between FE and BE with the BE on a network folder and the FE distributed to users. In distributing to a new user, he needs to link the FE/BE. To date I have VBA code that does the relink and which is initiated by the Autoexec macro. However the new users computer does not trust the application and blocks the autoexec with a 2001 error. We can't get into Access on his computer because he is using runtime files and so can't trust the application to bypass this issue. I have disabled the autoexec to try to get Access to ask for a link to the BE. Access just says it can't find the backend and doesn't give the option to browse for the backend.

I have also tried to bypass the security by putting the FE into appdata/ local but that gives the same 2001 error and won't run the autoexec.

I am really reluctant to mess with registry entries (I have never edited a registry) but it is beginning to look like this is the only way to do it. Any way around this?? It seems like there is no simple solution!

 

[Galaxiom]

Usually done by defining the Trusted Locations,

 

[Dumferling]

Usually done by defining the Trusted Locations,

Unfortunately this is not possible when the user is using runtime files

 

[arnelgp]

The question is why do you always need to relink the fe. You need to link it once and distribute the fe. Unless you are using a dhcp. You should have a static ip for the be.

 

[Isaac]

Can add trusted locations via the registry.

 

[Dumferling]

Yes but I am very nervous about editing registry. Never done it before and don't really understand how it works. I have found information that the runtime automatically creates the ACCWIZ folder as a trusted folder so this is the next thing I am going to try. After that it looks like registry editing is the only option. I have found an exe file which has been referred to a lot as a simple way to edit the registry for trusted locations but I am always nervous about trying that kind of thing out.

 

[theDBguy]

Yes but I am very nervous about editing registry. Never done it before and don't really understand how it works. I have found information that the runtime automatically creates the ACCWIZ folder as a trusted folder so this is the next thing I am going to try. After that it looks like registry editing is the only option. I have found an exe file which has been referred to a lot as a simple way to edit the registry for trusted locations but I am always nervous about trying that kind of thing out.

Hi. You could try to use the EXE program in this link, so you don't have to modify the Registry manually.

Ribbons for Access 2007 / Access 2010 – Trusted Locations

Access 2007 Ribbon Website * Access 2010 Ribbon Website * Home of Ribbon Creator

www.accessribbon.de

 

[Isaac]

I would also feel nervous about using someone else's exe or regedit file unless I thoroughly proved and understood its inner workings, and I'm pretty sure I'd create my own rather than doing that. Probably the last thing I'd do is edit a client's registry using a program given to me by someone else..
You can just manually do it - that's not very risky, I mean you follow Microsoft's instructions and add a value. The riskiest things are deleting, renaming, or editing existing values. In your case you will be adding them.

There is another option I forgot to mention earlier. You might be able to simply design your process such that the user's FE goes into a location already trusted by them, like LocalAppData. I have not personally verified this is true, but have heard it. Maybe worth a try. I usually install my databases in one of their AppData locations anyway - not for this particular reason, but that it seems to work wel.

 

[Pat Hartman]

If you are working in a corporate environment where you have IT support, you can ask the IT folks to distribute the necessary trusted locations to each new user. You just have to give them the correct registry keys and they will make it happen.

I also question why the app needs to relink when it opens.

 

[Dumferling]

I would also feel nervous about using someone else's exe or regedit file unless I thoroughly proved and understood its inner workings, and I'm pretty sure I'd create my own rather than doing that. Probably the last thing I'd do is edit a client's registry using a program given to me by someone else..
You can just manually do it - that's not very risky, I mean you follow Microsoft's instructions and add a value. The riskiest things are deleting, renaming, or editing existing values. In your case you will be adding them.

There is another option I forgot to mention earlier. You might be able to simply design your process such that the user's FE goes into a location already trusted by them, like LocalAppData. I have not personally verified this is true, but have heard it. Maybe worth a try. I usually install my databases in one of their AppData locations anyway - not for this particular reason, but that it seems to work wel.

I have tried localappdata and it did not work - I suspect the runtimes don't add it as a trusted location. The next one is Accwiz which I believe the runtimes do create. I didn't know Microsoft had instructions to edit the Registry for trusted locations?

 

[Dumferling]

If you are working in a corporate environment where you have IT support, you can ask the IT folks to distribute the necessary trusted locations to each new user. You just have to give them the correct registry keys and they will make it happen.

I also question why the app needs to relink when it opens.

I am in a corporate environment but I am not sure my IT will edit registries on my say so. To give this context, I am not an IT person, I am a legal adviser, creating a database that we need to work more efficiently and differently from the usual way legal advisers work - data driven.

I relink using JStreet ReLinker because the backend is on a shared drive and if I don't my users have to manually reconnect each morning by finding the BE. Since they are all pretty much technophobes I don't want them to have to do this and I don't trust them to do it.

 

[Dumferling]

Hi. You could try to use the EXE program in this link, so you don't have to modify the Registry manually.

Ribbons for Access 2007 / Access 2010 – Trusted Locations

Access 2007 Ribbon Website * Access 2010 Ribbon Website * Home of Ribbon Creator

www.accessribbon.de

This is the exe I downloaded but I do not know whether to trust it or not.

 

[Isaac]

I have tried localappdata and it did not work - I suspect the runtimes don't add it as a trusted location. The next one is Accwiz which I believe the runtimes do create. I didn't know Microsoft had instructions to edit the Registry for trusted locations?

Sorry to hear about the localappdata. Might try regular appdata too - not sure on that. I had heard that but never tried it.
I hope ACCWIZ works for you. I just checked mine, and I don't even have permissions to create a file or folder inside that folder. Who knows, though - I am on a highly secured corporate environment.

As for the registry, I felt I had seen it before - but may have misspoke on the "Microsoft" part. Either way, what I was really meaning to emphasize was that, once you have arrived on a plan (from the various instructions that are out there - admittedly maybe not Microsoft) - simply opening regedit and browsing to the location and adding keys is a fairly risk-free process. Manually.
Certainly nothing like taking a program someone else has created and hitting Go! Is all I was saying. I would never do the latter, unless I was able to inspect and approve of every single line of code it contained. Unless - well, you know the saying "you can do anything you want on your last day of work" I wouldn't use any automated way of editing the registry UNLESS there was a specific reason why a manual edit wasn't practical or possible. You'll spend more time writing or reviewing the code for the editing program than you would just open the darn thing and do it.

I did some work on this once and in my scenario there was some manual editing and also some I did via VBScript (only because the new users were becoming significant, and after very thorough testing). Adding the keys manually was very easy. The code was more complex because it had to check for the existence of certain keys, and increase sequential names if they existed, something like that.

I'm as confused as the others with the linking, though - Normally once you link your "development copy" FE, and everything is good, you just get that file over to their machine (however you do that), and everything remains linked. And never gets unlinked.

 

[theDBguy]

This is the exe I downloaded but I do not know whether to trust it or not.

Well, of course, I can only speak for myself. But, that website has been around for a while, and the author is very well-known among the Access Developers' community. So, I trust his work and use them myself. All I can say is, if you trust "our" advice, then you should know we are not going to intentionally lead you astray. Good luck!

 

[arnelgp]

I relink using JStreet ReLinker because the backend is on a shared drive and if I don't my users have to manually reconnect each morning by finding the BE. Since they are all pretty much technophobes I don't want them to have to do this and I don't trust them to do it.​

you need to ask your IT to give your db location a Static IP.
this is true on any databases (MSSQL, MySQL, etc.)

 

[Dumferling]

Well, of course, I can only speak for myself. But, that website has been around for a while, and the author is very well-known among the Access Developers' community. So, I trust his work and use them myself. All I can say is, if you trust "our" advice, then you should know we are not going to intentionally lead you astray. Good luck!

Thanks for this assurance. You can't be too careful these days but I am happy to trust this community

 

[Dumferling]

Sorry to hear about the localappdata. Might try regular appdata too - not sure on that. I had heard that but never tried it.
I hope ACCWIZ works for you. I just checked mine, and I don't even have permissions to create a file or folder inside that folder. Who knows, though - I am on a highly secured corporate environment.

As for the registry, I felt I had seen it before - but may have misspoke on the "Microsoft" part. Either way, what I was really meaning to emphasize was that, once you have arrived on a plan (from the various instructions that are out there - admittedly maybe not Microsoft) - simply opening regedit and browsing to the location and adding keys is a fairly risk-free process. Manually.
Certainly nothing like taking a program someone else has created and hitting Go! Is all I was saying. I would never do the latter, unless I was able to inspect and approve of every single line of code it contained. Unless - well, you know the saying "you can do anything you want on your last day of work" I wouldn't use any automated way of editing the registry UNLESS there was a specific reason why a manual edit wasn't practical or possible. You'll spend more time writing or reviewing the code for the editing program than you would just open the darn thing and do it.

I did some work on this once and in my scenario there was some manual editing and also some I did via VBScript (only because the new users were becoming significant, and after very thorough testing). Adding the keys manually was very easy. The code was more complex because it had to check for the existence of certain keys, and increase sequential names if they existed, something like that.

I'm as confused as the others with the linking, though - Normally once you link your "development copy" FE, and everything is good, you just get that file over to their machine (however you do that), and everything remains linked. And never gets unlinked.

I thought originally that what you are suggesting would work but when it did not I found J Street Relinker and put that in. I think something on the network in the corporate environment is to blame - possible network shares. J Street works fine though (it is a great piece of code - Thanks developer). With Covid 19 and working from home, J Street has really helped since we use VPN's to access the corporate network. Maybe one day I will have the courage to try the original way!

 

[Pat Hartman]

I am in a corporate environment but I am not sure my IT will edit registries on my say so. To give this context, I am not an IT person, I am a legal adviser, creating a database that we need to work more efficiently and differently from the usual way legal advisers work - data driven.

I'm pretty sure that they would cooperate. There's lots of online documentation regarding this process and the need for it. It can't hurt to ask them.

Also, I think you can resolve the issue of needing to relink if instead of using a mapped drive you use the UNC name. Try relinking the master copy and instead of navigating to the mapped drive and picking the file, type in the file name using:

//servername/path/yourdb.accdb

That doesn't require the drive to be linked and so should eliminate the problem. It is also the preferred method of linking anyway because in many environments, there are lots of mapped drives and in some cases, userA and userB will end up with the needed drive mapped to a different letter. The UNC method resolves these issues.

 

[The_Doc_Man]

Absolutely and emphatically agree with Pat regarding UNC mapping. At the Navy Enterprise Data Center, we had over 30 different projects and resources where some kind of drive mapping was required and we had to consider that the number of open USB ports on the hardware platform (laptop OR desktop) would consume as many as half of those letters. My personal desktop here at home consumes through the letter "L" for example. UNC mapping helped me fix the problem for a lot of my users who needed to use my app but already had a dozen other mapped drive letters.