Results 1 to 12 of 12
  1. #1
    twgonder is online now Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    651

    How to design for remote locations?

    I saw this question asked before, but it was quite complicated and as I recall it wasn’t solved. It was asked years ago, with a version of Access that was possibly less capable.

    Several replies to my post have advised to split the database. And I understand that concept.

    Now the question is, if we split (like we should) and ALSO have branch locations that use the same front end and backend, what are we to do if the network goes down? Some operations can halt of course, but others, like selling products must continue.

    If we don’t have some of the backend files available locally, then we obviously can’t sell. And maybe we don’t want all that traffic all the time to a backend table over a WAN anyways.

    Can we have local copies of the backend tables on one local branch workstation, that the other local workstations can use? And if so, how do we synch them up to the remote backend when the network comes back up, or maybe once a day if we are avoiding all that WAN traffic?

    If I recall, some answered the old thread with the suggestion, “Look at SQL Server”, but without any indication that it could work in this case either. In any case, since we are using Access, it would be nice to have a solution that works in Access, because some companies may have selling staff at several remote locations that have only one or two cashiers, while “corporate” may only have ten users. It doesn’t make sense to install big SQL Servers in such a small business environment.

    We had the problem in the old mini/mainframe days, and the only solution to network/comms (as in modems) down was to pull out the paper invoices, fill them out by hand and then enter them later. In 2022, it would be nice to have a solution a little more elegant than that.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In my limited poking around this subject, it seems that when a WAN is involved, Access is a non-contender - still.
    My only experience in working remotely with Access was using Citrix or Terminal Services (renamed Win32 apps, I believe) and it performed quite well. In the case of Citrix, you're using an application to remotely run the same db that you would be running if you were local to the server (LAN) it was stored in. However, Citrix means licensing $. Maybe SQL Azure would be a viable option for you.
    HTH
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, you can use 1 standard SQL server as central server where you set up the distributor, and choose SQL express https://docs.microsoft.com/en-us/sql...l-server-ver16 for your local subscribers, it's free. As front end you can still use access. SQL express doesn't have a SQL agent, but most tasks, like taking back-ups and so, can be performed using Powershell.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access 2003 had a feature called replication. That was deprecated in 2007 (it was pretty horrible) but the component parts are still there to enable data synchronisation. Can work well under the right conditions - typically the satellite db's update the master db and typically with discreet datasets (i.e. satellite 1 updates master with satellite 1 transactions). Gets more complicated if they also need to receive timely updates from the master or other satellites and/or are updating the same record (e.g. satellite 1 and satellite 2 both want to change the same customers details).

    Personally I would look at citrix or terminal server or perhaps sql azure. Won't help if the network goes down, but usually that is a pretty rare occurence. More likely to get a power cut and then you can't sell anyway

    how do we synch them up to the remote backend
    if you want to go this route, your tables need some additional fields and you will need a number of queries.

    Additional fields required - date created, date last updated from sync, date of last update and a GUID field. Your autonumbers also need to be set to random and not incremental. The GUID field provides a 'global' unique ID not limited to the table - but it is big (16 bytes) and is not suitable as a PK - try it and you will see what happens. It's purpose is to match records between master and satellite (a one to one 'relationship')

    Queries should be self evident, for each table and in no particular order
    update to master where matched to satellite and satellite has a date of last update > the master date of last update
    insert to master new records in satellite not in master
    delete from master records not in satellite

    and reverse the process

    insert and update can often be combined into one 'upsert' query

    You will also need to create some means of managing clashes.

  5. #5
    Info@MsAccess.nl is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2022
    Posts
    7
    I have made a database that’s totally functional stand alone and will sync all changes with other databases with onedrive. The biggest part of your data is kept on your own pc, no big dataset transfers over the internet. No dependability of cloud services or sql server with the extra costs. Only if a change is made, this change is written to a textfile. Whenever the pc is connecting with the internet/onedrive, all the changes are uploaded and downloaded, which makes your local database synced with the other databases. This is also great solution for countries with bad internet. The text files could be replaced by a sharepoint list or rest api. I found a simple trick to have two seperate databases make a unique id, which will not create duplicates.

    If a new customer is created in Database A, The ID will be DBA1 (DBA + autonumber)
    if a new customer is created in Database C the ID will be DBC1

    This way you will always have a unique identifier.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I have used a hosted SQL database from a vendor(don't remember who), they just added a database for our project on their SQL box and gave us access to just our database with IP to connect to, ID and Password. Then I created the front end in Access and linked the tables to that SQL database. I could use Management Studio to create new tables on that SQL box also. I think you could do that and have multiple Access front ends hitting the same SQL database. As for network issues and how can they keep selling, not sure on that. Maybe write a separate process for them to keep selling on local tables and be able to upload it back to the SQL database once network was up.

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    We use Azure SQL cloud based back end for 90% of our clients.
    It's reliable, relatively cheap, and provided you allow for designing things to accommodate a remote WAN based backend data store things work very well.

    Generally, if someone has lost internet access, these days they have also lost the ability to take someone's money as well, so it's a moot point if you need to get around that.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    twgonder is online now Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    651
    Quote Originally Posted by Minty View Post
    Generally, if someone has lost internet access, these days they have also lost the ability to take someone's money as well, so it's a moot point if you need to get around that.
    Most people, including store owners, are loathe to pass up taking someone else's money. One of the best systems I ever saw, developed by the wheelchair-bound man that created Contempo Casuals, was: open the drawer, toss the cash in, close the drawer. Being away from retail for so many years, I don't know if the credit card companies still use the manual imprinter method in a pinch. Or if they allow for delayed processing of credit card purchases. But you're right, I've been to many stores in Colombia and heard that they can only receive cash when the "systems are down" and many times not even that.

  9. #9
    twgonder is online now Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    651
    @ Info@MsAccess.nl
    Thanks for you comments. I'm writing a big explanation for what I used to do to synch. Your solution sounds very similar. Feasible and manageable.
    I would have a be at each Remote location and the option is to have the bes log the changes or the fe has to do it if the be can't monitor changes.
    This wasn't an issue for me as the old mini-computers did it all, there weren't any fe workstations, only dumb terminals.
    Only a localized subset of all records at the Home are held at the Remote be. There's no reason to have all customers from Home on each remote. In addition to using naming like DBA1 I used a three-digit code for all clients/remotes. This was used in synching to create what is in Access a composite key. This code was used on the Home be.

    I suppose this three-digit code could be added right after the auto number as a field (byte number) to create a unique auto id in every table, or somehow added to the auto number if one can't create a primary key using the auto number and remote code as a composite. Heck, I've got to go test that now.

    I would like to know why you chose a text file instead of using a Access table to track changes. Did you track records changed or fields changed?
    Do you have a set of demo dbs that you would consider sharing for testing and collaboration?

  10. #10
    Info@MsAccess.nl is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2022
    Posts
    7

    Using text files to sync databse

    Quote Originally Posted by twgonder View Post
    @ Info@MsAccess.nl
    Thanks for you comments. I'm writing a big explanation for what I used to do to synch. Your solution sounds very similar. Feasible and manageable.
    I would have a be at each Remote location and the option is to have the bes log the changes or the fe has to do it if the be can't monitor changes.
    This wasn't an issue for me as the old mini-computers did it all, there weren't any fe workstations, only dumb terminals.
    Only a localized subset of all records at the Home are held at the Remote be. There's no reason to have all customers from Home on each remote. In addition to using naming like DBA1 I used a three-digit code for all clients/remotes. This was used in synching to create what is in Access a composite key. This code was used on the Home be.

    I suppose this three-digit code could be added right after the auto number as a field (byte number) to create a unique auto id in every table, or somehow added to the auto number if one can't create a primary key using the auto number and remote code as a composite. Heck, I've got to go test that now.

    I would like to know why you chose a text file instead of using a Access table to track changes. Did you track records changed or fields changed?
    Do you have a set of demo dbs that you would consider sharing for testing and collaboration?

    I used text files because they are small and fast over the internetand can be replaced by a rest api. Furthermore in this example i use onedrive and you can’t use access on one drive. I am now working on a model with sharepoint, just using two tables to communicate the changes made on each individual database. I think your id with adding 3 unique numbers will work to. Be aware the a relationship to another table has to be with this id. I don’t mind to share knowledge, I personally think that the concept of only communicating the changes in a database can have many advantages instead of transporting big recordsets over the internet.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @ Info@MsAccess.nl
    interesting concept - I can see that working between two databases, but say you have one 'master' and 30 'satellite' databases. How does it work then?

    Each satellite creates a text file (presumably one for each table or perhaps a single xml file) and by whatever method sends it to the master who then imports and deletes or archives the file? But what about the other way? The master has read the data from satellite A and then needs to tell all the other satellites about the updates - does it create 30 text files, one for each satellite? or a single text file which all the other satellites can read - but at what point can that file be deleted or archived?

    Or does the satellite produce some sort of return to the master that says 'processed that file' (plus no doubt an internal flag to prevent the satellite trying to upload the file again) and when all satellites have done the return the master can delete/archive the file?

    You also have the potential for data clashes - satellite A and satellite B both modify some customer detail for the same customer - both send up up a text file and both are imported to the master - but which one is right? How do you manage that?

    Similarly, two satellites create a new record for the same customer at the same time - both get uploaded. They have discreet ID's so not problem there, but you now have the same customer in the system twice. Made doubly difficult if there is a small typo difference between say customer name - in a project I was involved in a few years ago, we discovered McDonalds had been entered into the system 21 times, each with a slightly different spelling. The correct one is McDonald's but we also had McDonalds, Macdonalds, Mc Donalds, to name just a few.

    Appreciate it very much depends on what the app is actually doing but would be interested to know if you are having to cope with the above scenarios and if so, how.

  12. #12
    Info@MsAccess.nl is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2022
    Posts
    7
    Quote Originally Posted by CJ_London View Post
    @ Info@MsAccess.nl
    interesting concept - I can see that working between two databases, but say you have one 'master' and 30 'satellite' databases. How does it work then?

    Each satellite creates a text file (presumably one for each table or perhaps a single xml file) and by whatever method sends it to the master who then imports and deletes or archives the file? But what about the other way? The master has read the data from satellite A and then needs to tell all the other satellites about the updates - does it create 30 text files, one for each satellite? or a single text file which all the other satellites can read - but at what point can that file be deleted or archived?

    Or does the satellite produce some sort of return to the master that says 'processed that file' (plus no doubt an internal flag to prevent the satellite trying to upload the file again) and when all satellites have done the return the master can delete/archive the file?

    You also have the potential for data clashes - satellite A and satellite B both modify some customer detail for the same customer - both send up up a text file and both are imported to the master - but which one is right? How do you manage that?

    Similarly, two satellites create a new record for the same customer at the same time - both get uploaded. They have discreet ID's so not problem there, but you now have the same customer in the system twice. Made doubly difficult if there is a small typo difference between say customer name - in a project I was involved in a few years ago, we discovered McDonalds had been entered into the system 21 times, each with a slightly different spelling. The correct one is McDonald's but we also had McDonalds, Macdonalds, Mc Donalds, to name just a few.

    Appreciate it very much depends on what the app is actually doing but would be interested to know if you are having to cope with the above scenarios and if so, how.

    A seperate text file for each change on each satelite. When you are in a field on a form, and the record is updated, you see the change immediately (import triggers refresh of the form), no error like normal. The files text files are archived after import. I wouldn’t use this system with more then 5 (Wan-) users. But you could use a sharepoint list or a free cloud database to sync the changes (with 1 table) instead of the text files. This could handle more users. The Computers connected to the lan could read the Db directly, the outside (Wan) users could use this system. This could also extend the amount of users.The other questions are solved with vba. The validation problem is also present with a normal database, you have to write a validation rule, for preventing double entries.
    When you have 5 workers doing inspections are sales, then it’s hardly possible that 2 workers add the same information. It also depends on the situation. Changing the same data on the same time also indicates organizational problems..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to add up locations by date
    By johnnybaccessless in forum Queries
    Replies: 2
    Last Post: 12-19-2020, 03:35 PM
  2. Sales Locations and Sales to Locations
    By Dinger045 in forum Access
    Replies: 1
    Last Post: 04-19-2016, 12:41 PM
  3. Trusted locations
    By kcmiuser in forum Security
    Replies: 1
    Last Post: 08-14-2013, 09:48 AM
  4. Replies: 0
    Last Post: 01-26-2011, 05:51 PM
  5. Deploying Access db to different locations
    By lupis in forum Security
    Replies: 7
    Last Post: 06-01-2010, 05:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums