How do you work on a client's local db remotely? (1 Viewer)

George-Bowyer · Jan 16, 2019

My experience with FE/BE databases has been with using the BE on sharepoint.

Changing the FE and sending new copies to users is a doddle, because the sharepoint links remain the same wherever you are

But, if you design a FE/BE db for someone and install it on a server at their location, how do you then do upgrades on the FE if you are not in the same location and unable to link to the BE?

Yes, you can have a copy of the BE at base, but the linked table connections will be different when it comes to sending the new FE to the users.

isladogs · Jan 16, 2019

I use the following approach:
a) use a FIXED location on the C drive for the FE
b) use DSN less connections to the BE tables (Access or SQL Server etc)
c) store the details for the table links in the FE
d) also store these in a separate configuration database distributed for use by admins

When I distribute a new version of the FE (via my website), I remove all links to the BE.
The program administrator receives an automatic alert that a new version exists.
The program admin downloads and runs it.
The FE automatically relinks the tables using the data stored in the config file.
It can also be used to make changes to the SQL or Access BE files if needed.
The admin places the relinked FE in an upgrade folder

When users run the app via a desktop shortcut, they are actually running a small 'starter app'.
This checks if a newer version of the FE is available in the upgrade folder.
If so, it copies this to the C drive and runs the new version.
If not, it runs the existing version of the FE
The whole process is seamless and end users are usually unaware the above is happening.

I can supply more details if you like this approach

HTH

theDBguy · Jan 16, 2019

Hi. One approach i use sometimes is to copy the client’s network structure on my local machine.

bastanu · Jan 16, 2019

Hi George,
Funny enough I've just finished posting a reply on a similar question on a different forum:http://www.accessforums.net/showthread.php?t=75225
My approach is fairly similar to Colin's; I use a small launcher (http://forestbyte.com/ms-access-utilities/fba-db-launcher/) to deploy the updated front-end which is set to relink itself to the client's BE. Any back-end changes can be deployed via the front-end using custom functions (http://forestbyte.com/ms-access-utilities/fba-fe-to-be-table-push/) for Access back-ends or pass-through DDL queries for ODBC back-ends.

Cheers,
Vlad

Solo712 · Jan 16, 2019

isladogs said:
I use the following approach:
a) use a FIXED location on the C drive for the FE
b) use DSN less connections to the BE tables (Access or SQL Server etc)
c) store the details for the table links in the FE
d) also store these in a separate configuration database distributed for use by admins

When I distribute a new version of the FE (via my website), I remove all links to the BE.
The program administrator receives an automatic alert that a new version exists.
The program admin downloads and runs it.
The FE automatically relinks the tables using the data stored in the config file.
It can also be used to make changes to the SQL or Access BE files if needed.
The admin places the relinked FE in an upgrade folder

When users run the app via a desktop shortcut, they are actually running a small 'starter app'.
This checks if a newer version of the FE is available in the upgrade folder.
If so, it copies this to the C drive and runs the new version.
If not, it runs the existing version of the FE
The whole process is seamless and end users are usually unaware the above is happening.

I can supply more details if you like this approach

HTH

I have developed a variation of this which handles this slightly differently. I just leave whatever BE reference there is in the new FE. When the new FE runs, and the reference to BE is incorrect, it will cough up Error 3044 (Invalid Path). Since you need to test a "network down" situation anyway, you may as well test whether the BE path that you have in the FE is valid. (You do that by comparing the BE path to what you have stored locally on a client's workstation - I use a management console for this..demoed here). If the stored path and the FE's path to BE are different, the error handler will engage a relinker and resume the execution of the program to the original first reference to the BE. If the FE's link to BE and the stored reference are the same, it's a network problem and the application should be shut down.

Best,
Jiri

isladogs · Jan 16, 2019

Hi Jiri
That solution works well where the app is used by only one client organisation on a single network.

However, most of my commercial apps are used by a number of different clients (e.g. schools) each of which has its own path to the SQL/Access BE files.
For that reason, removing the table links makes more sense as these are different for each client. Even with the largest app (300+ tables) relinking only takes a few seconds.
Using DSN less connections also simplifies matters as each workstation doesn't need to be individually configured.
I've never needed to visit most of the sites using my apps and in many cases have never needed to provide online support either. For both clients and myself, that's a win-win situation

Solo712 · Jan 16, 2019

isladogs said:
Hi Jiri
That solution works well where the app is used by only one client organisation on a single network.

However, most of my commercial apps are used by a number of different clients (e.g. schools) each of which has its own path to the SQL/Access BE files.
For that reason, removing the table links makes more sense as these are different for each client. Even with the largest app (300+ tables) relinking only takes a few seconds.
Using DSN less connections also simplifies matters as each workstation doesn't need to be individually configured.
I've never needed to visit most of the sites using my apps and in many cases have never needed to provide online support either. For both clients and myself, that's a win-win situation

Good for you Colin. I don't think you are getting it, though. It does not matter one whit how many clients the app has or how many paths to the BE they have. As long as each workstation at each location has a way to find its own path to the BE (independent of the FE current links) what I have proposed will work. As a matter of fact, the only real difference between what you proposing and my model is that in my model the new FE "discovers" a relink is necessary when the path is not working and yours does it through a testing applet.

Best,
Jiri

Pat Hartman · Jan 17, 2019

I also have the FE test a linked table on startup. If the query fails, I bring up the relink dialog and allow the user to navigate to the new location. Otherwise the app just opens. Let me know if you want me to post a form you can use. You would need to change the code a little to test your own links.

@George --
I hope you are really not sharing a BE saved on Sharepoint. That will NOT support concurrent users. The second user to save will clobber the first user's data.

Gasman · Jan 17, 2019

Pat Hartman said:
I also have the FE test a linked table on startup. If the query fails, I bring up the relink dialog and allow the user to navigate to the new location. Otherwise the app just opens. Let me know if you want me to post a form you can use. You would need to change the code a little to test your own links.

@George --
I hope you are really not sharing a BE saved on Sharepoint. That will NOT support concurrent users. The second user to save will clobber the first user's data.

Pat,
I would be very much interested in that form, please.

isladogs · Jan 17, 2019

Solo712 said:
It does not matter one whit how many clients the app has or how many paths to the BE they have. As long as each workstation at each location has a way to find its own path to the BE (independent of the FE current links) what I have proposed will work

I'm not saying otherwise. Each to their own as long as it works well.

However, for my purposes, the relink process forms an integral part of the update process run by the program administrator.
At each site, the program admin will reconfigure the app for all the 200+ users at that site. As each site has a different setup to each other and different to myself as the developer, all would need to relink anyway. As I've already said the relinking is done automatically without any need for user intervention.
Managing relinking as I describe means that happens very slightly quicker as the table check isn't needed though the time saving is small.
Personally, I also prefer to avoid using code that depends on an error including one where the error is that the linked file paths can't be found.

Pat Hartman · Jan 17, 2019

frmCheckLinks is the form you will need to modify to check your own linked table. It is the startup form and opens the login form if the tested table is valid or the relink form if it is not.

Gasman · Jan 17, 2019

Pat Hartman said:
frmCheckLinks is the form you will need to modify to check your own linked table. It is the startup form and opens the login form if the tested table is valid or the relink form if it is not.

Thank you Pat.

Pat Hartman · Jan 17, 2019

You're welcome. Hope you get the idea of how it works. I built the relink form because the built in linked tables manager is extremely difficult to use when you have multiple BE databases which I frequently have. The relinker form basically comes up with one row for each separate BE no matter how many tables are linked. So if you have a hundred linked tables in only one BE, you will see only one row. If you have 100 tables across 4 BE's, you'll see four rows. The idea being, you want to keep all the links in sync so there is no reason to show the detail table names. When you have multiple BE's to link, they are done one at a time. Pick the new target in the top box and press the button on the row you want to relink. Then do the next BE, etc.

George-Bowyer · Jan 17, 2019

Pat Hartman said:
@George -- I hope you are really not sharing a BE saved on Sharepoint. That will NOT support concurrent users. The second user to save will clobber the first user's data.

Hmm. That's a bit alarming.

Especially, since we've been using it for about 6 years and haven't noticed any problems...

Then again, it's only half a dozen users with a fairly small likelihood of any of them being logged on the db at the same time (however, that said, there have been occasional times where I know there have been 3 or 4 of us online at once...)

CJ_London · Jan 17, 2019

I suspect you are talking at cross purposes. Access used to be able to use sharepoint where tables were actually lists - effectively separate files within the sharepoint environment - so sharepoint is the backend - or was since the option is no longer available except if you have sharepoint on your servers and not MS servers - and I think that may be gone now as well.

George-Bowyer · Jan 18, 2019

I'm really confused now.

I have a database that uses lists on sharepoint as the backend via office365 using access for local frontend copies. Seems to be working as it should.

CJ_London · Jan 18, 2019

per post 15, sharepoint is your backend. Lists are individual files on sharepoint and are treated as your tables.

An access backend is a single file containing all the tables - and locating that on sharepoint won't work

George-Bowyer · Jan 18, 2019

I used to have an access "web database", which was located on my Office365 account.

That system was rubbish. Only allowed the use of macros, apart from anything else, no VBA.

That's why I replaced it with the current Sharepoint List BE / Access FE that I have now.

Pat Hartman · Jan 21, 2019

If the FE is linked to sharepoint lists, that is fine. Very few would choose to use SharePoint as the bE for Access, but technically, it will work. The way you said it "using the BE on Sharepoint" has a different connotation since SharePoint can be used to share files. It is NOT OK to have an Access .accdb BE stored on a SharePoint server that everyone shares. That WILL NOT WORK if you ever have a situation where two users might open the BE at the same time. For file sharing, Access relies on Windows and Windows isn't involved when the .accdb BE is stored as a file on a SharePoint Server.

I hope I didn't make you even more confused. You are OK the way you are.

George-Bowyer · Jan 22, 2019

Pat Hartman said:
If the FE is linked to sharepoint lists, that is fine. Very few would choose to use SharePoint as the bE for Access, but technically, it will work.

At the time - around 2014 - it was the only low cost (I was building a db for a club where users are all volunteers spread all over the UK - so no funds to speak of) method that I could find to allow multiple users to access the data over the internet (seeing as the so-called access web database turned out to be a complete bust).

I hope I didn't make you even more confused. You are OK the way you are.

I like being confused - because it makes getting unconfused so darned satisfying...)

How do you work on a client's local db remotely? (1 Viewer)

Registered User.

MVP / VIP

I’m here to help

AWF VIP

Registered User.

MVP / VIP

Registered User.

Super Moderator

Enthusiastic Amateur

MVP / VIP

Super Moderator

Attachments

Enthusiastic Amateur

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread