Splitting database in 3

[Freshman]

Hi all,

I need some guidance here:

I have a database (already splitted in FE/BE) which Clients use on site.
Part of the service we offer is Outsourcing: We do all the capturing and when they login on their side, it downloads the latest BE file for them to use "Read-Only".

However we added a new module in which half of the data on a particular table needs to be completed by the Client.

So we need for the table to be splitted in such a way that they can capture their info on the form and when we log in on our side, it has to download the data they entered in the same way as when they login and download the data we captured.

Was thinking about adding a 3rd database file to the mix with just one table and in some way link that table to the existing table for the new module. But the intermediate link I created is not updateble.

Any advice on how to do this the right way?

Thanks
Pierre

 

[PaulO]

I would say:-
1) You certainly don't need another database
2) The BE doesn't even need to be read-only
3) You can effectively control what the Client views, edits, or adds by use of Forms restricting which tables (and which fields within the table records) they can add or edit data

Hope this gives you a start.

 

[Freshman]

Hi Paul,

Thanks but it is not as easy as that.
We do outsourcing from a remote office. There is no link between us and our Clients. So each time we update their data and close the app on our side, the lastest BE file to a server.
The moment the Client then logs in, they download the said BE file so they can view all the data we captured last.

This used to work well, but nowadays we need for a spesific table to be populated by them, so what I ended up doing is whoever is logged in, uploads a "lock" file to the server which prevents the other users from loggin in until the data has been captured and then they can login again.
This way I stop them from overwriting the BE data when they work on it at the same time.

The perfect solution for this would have been a fast VPN solution but over here it is very expensive still. So sharing the BE file like I explained above is a cheap work-around.

Hope this makes sense.

Thanks
Pierre

 

[PaulO]

OK ...

I had some help from this Forum creating what I call a "Database Switcher" utility. This varies from your requirement insofar as what I needed was simply to change the NAME of the linked _BE file as I have a FE Database that needed to link to multiple Client BE's (to safely segregate and work with the data).

The MODULE updates the Linked Table Manager automatically, based on the radio-box selection made in a "Database Switcher" form.

You requirement differs insofar as it's not the NAME of the BE that wants updating but the file/network path?

Now, as I said, the MODULE itself was creating by one or two very clever guys on here and like minds may be able to adapt that code successfully for you by using a stored file/network path for each remote site and updating the Linked Table Manager.

I am happy to share this code with you, you yourself may have some ideas on how to adapt it?

Let me know ... and if any code experts are viewing this they may be able to help!

 

[Freshman]

Hi Paul,

Thanks - if you could paste the code here or upload a sample database file I would like to have a look at it.
I understand what you mean on describing how it works. I have a similar script which re-links to each Client's spesific local server path.
Remeber on my side I don't have access to their local server (LAN) so after I send them and update, the script would re-link to their own unique LAN path.

I always like to see how others did things (even if it results in the same) as there are always more ways to skin a cat...

Thanks
Pierre

 

[PaulO]

I have tried to extract the relevant Form and Code to the attached database.

Hopefully you'll understand what's going on, but to make it work you'll need to create a few databases (all copies of the same original, with different names) and park this FE and all the BE's to a folder called C:\SecPro then link one of the BE's to the FE

Open the Form in Design mode to see what's going on ...

 

[Freshman]

Hi Paul, thanks a span - will go through the code and see what they did compared to my method. Sure I would be able to learn lots.

Cheers
Pierre