Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Synch tables between .accdbs

    I have staff doing work in different .accdbs.


    Sometimes I need to synch the records from different tables in their .accdb.
    All the tables in question have a last update field with a date and time.

    What I would like to do is write a routine that goes to all the .accdbs and pulls up the records for a given table to an "aggregator" table, each time the iteration runs, it should only pull up (for merge, append or replace) records that have a date newer than the one in the aggregator table. When it's all done, the aggregator should have only the most recent records.

    Since it's multiple tables in different .accdbs, I'm sure I will need some VBA code, surely avoiding macros.
    The question is it best to do it with SQL statements somehow, or write down and dirty VBA DAO code to do it?

    Then when it's done, I need to do the reverse, sending the most recent records back down to the individual .accdbs.
    In the future, I expect to have millions of records being moved up and down to the various .accdbs, so it makes sense to somehow do this efficiently over a network.

    I could import each table in some manner, and then let the code on the aggregator do the work. I'm not sure if the import, with big tables would be efficient, common sense tells me yes, since VBA code would have to run SQL to bring up the records anyways. Alternatively, the aggregator could read just the update field in the remote .accdb, record by record to see what to then import, but there might be a big efficiency penalty for doing it this way.

    Anybody got any ideas as to dos and don'ts in such a scenario?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This 2-part article may help: Synchronise Data
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    @isladogs, post #2. Thanks for the article. I read it once and I will probably need to reread it 5 more times to understand it all. As I read the article, I wasn't sure if it was a 2-way synch, and if not, which was the main table.

    Some considerations. I'm thinking the aggregator can serve as a backup of sorts. Since it will grow to accommodate all the records, one might as well just let it stay populated. I'm not using Access autonumbers for records, so that helps to simplify that problem.
    The idea is to run code on the aggregator, a back end. I haven't done much with backends yet, but I'm guessing there must be a way to run forms, queries and code in the backend for this synching. Then this aggregator backend pulls up the data from a remote backend.

    I've done a bit with splitting an .accdb, and noticed, even with a modern local wifi connection, there are big delays in how Access responds (often with "not responding"), far worse than any other Windows programs that use the wifi network. If Access is so finicky with a high-speed, local wifi, I have concerns as to how this synching is going to work over the Internet. (I know, I've heard dozens of times to use SQL server for this, but I'm not yet ready to go there with my development and testing, and it's ultimately going to be VBA code that manages all this, with its own tables for synching, so it will probably have to be on an Access backend.)

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. Backend databases should only contain tables
    2. Access is not robust enough to work reliably with wifi connections to the local network. No matter how good the Wifi, there will be intermittent breaks in connection. If someone is in the process of editing data when that happens, there is a significant risk of data corruption. The risk is significantly greater using a WAN. In either case, you WILL experience corruption at some point using WiFi
    3. A SQL Server BE is much more resilient to such issues
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    @Isladogs, post #4
    I'm trying to understand:

    1. Why must a backend be only tables? Some tables often need to remain in the front-end for specific users. It makes sense that some code would be best directed from the backend to reduce network traffic. Not all processing is a form or report. Most robust applications do a lot of updating, the kind of work that's best kept off the network and on a central processor.

    2. Access started when 10mbs network cards were the norm. Wifi is easily that fast or much faster in most situations today. So, what gives here? All networks experience "intermittent breaks" due to collisions & hardware tasking; this has always been the case and the very nature of networks (and central processors too, not to mention Windows multi-tasking). Is MS sandbagging Access in some way? I watch videos over wifi, a much more data intensive operation. They don't seem to crash like Access. MS adopted the "internet approach" two decades ago (I can't even get an offline help for Access). Why is Access the bastard child in all this? I've consulted for years with big corporations where the entire floor (60+ users) runs on wifi for everything.

    3. Again, Access and SQL server are over 20 years old now. So how can modern equipment that is umpteen times faster than the boxes from 20 years ago have a problem? What was the point of souping up JET if the DBE remains a dog incapable of performing in what most offices use these days (wifi & wans)? Is SQL Server just more resilient, meaning it too isn't stable and I'll be dealing with lots of corruption?

    Does anyone know the cause of this "corruption" and if so, why is it so hard for MS to fix? I've worked with dbs for almost 50 years, and I never heard of a slow RS232, disk or bus causing corruption at the software level. By the 1990s, the largest minicomputers were replaced with PCs, 100 times faster (or more) and virtually bullet proof from "corruption". This seems an unacceptable situation in Office/Access. What, my word documents are going to be corrupted because the refresh rate on the monitor is "slow"? Balderdash.

  6. #6
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    272
    What you say you want to do in the first post is very reasonable, but you should consider being able to roll back. Perhaps, it would be better to create a backup of the aggregator table, then send a copy to a local backup folder and another copy to a remote folder, do the same with each user table. Then perform a merge of the aggregator table with each user table. Maybe everyday at night, when all tables are merged, send a copy of the merged aggregator to each user and with that you can go back if something is wrong. The workflow you mentioned sounds like it will give me a headache if records are deleted or updated and I can no longer view their previous state.

    The key here is who's orchestrating everything, and that should be a backend server, backends in Access are just tables, but your needs are obviously much more complex, so you need a server that can perform all of these operations. No user frontend should be doing that.

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Is SQL Server just more resilient, meaning it too isn't stable and I'll be dealing with lots of corruption?
    Not only is SQL server a robust and stable database, but it has the build-in possibility to replicate data amongst several servers. So the only thing you would need to do is activate this feature. However, it is a whole different way of working and you would have to do some learning.

  8. #8
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    272
    Quote Originally Posted by NoellaG View Post
    SQL server has the build-in possibility to replicate data amongst several servers
    Wouldn't that require all his accdb users to be connected at the same time for replicated slaves to be in sync with a master?

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Access uses the SMB protocol with it's own backend tables over a network. It was designed when wi-fi didn't exist, and has never been updated.
    You will read everywhere that with an Access backend you should use a wired connection.

    SQL server handles the table data connection much more elegantly and is therefore much more resilient to a wireless connection and it's related foibles.

    This post describes the Wi-fi issue in more technical detail
    https://www.access-programmers.co.uk.../#post-1772890
    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 ↓↓

  10. #10
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Does it...?

    Quote Originally Posted by NoellaG View Post
    Not only is SQL server a robust and stable database, but it has the build-in possibility to replicate data amongst several servers. So the only thing you would need to do is activate this feature. However, it is a whole different way of working and you would have to do some learning.
    I have to confess ignorance, at this point on SQL's replication technology. It may make duplicate copies of a db and pass it all around, but I have to question if it has the ability to use business logic to determine what to move, to where and why. If SQL server doesn't date stamp every record, and know who its owner is and who should or shouldn't get copies, as well as understand my network scheme for backends, then it's no closer to solving my business logic problem than Access is. Anybody that's ported a large commercial or government application to MS "server" technology knows exactly what I'm talking about.

    Maybe MS has put more effort into their "server" technologies since the early 2000s than they have in Access.
    I gave up on MS when I took early "retirement", because the company direction was moving towards MS server products from superminis.
    Just Exchange Server alone kept me at the office continuously, not doing any "good work" for two years because Exchange Server was crap. So, I don't have a lot of hope for SQL server, even to this day. Especially when I hear it's just more resilient. As asked earlier, what does that mean, I'll be spending only half my nights reconstructing corrupted data?

    Over the years I've always questioned down-streaming blame.

    The software developer blames the RAD developer.
    The RAD developer blames the O/S.
    The O/S engineer blames the hardware.
    The hardware guys blame the power source.
    And all these "engineers" love to toss the blame back at the "stupid" user.

    Access is a thousand times more powerful (at least in doing lots of pretty little colors and panes) than any db on several different hardware foundations I ever worked with between 1970 and 2010. Especially when you consider the robustness of Windows and the current hardware. Just to boot a single Windows workstation there's more computing power going on than it took to put a man on the moon.

    There should be no reason that a single Access DBE can't replicate anything done back then, and do it without corruption. We balanced $18,000,000,000 in millions of accounts to the penny each month for hundreds of thousands of clients in a commercial banking application. On one hardware platform, we had problems, and the hardware manufacturer loved to blame our power grid (despite other sensitive equipment having no problem). Within a year I proved it was a bug in their processor chip. Oooops! Oh, oh, it's wifi or the WAN that's the problem. REALLY MS?!!! Or is it just some F...ing lazy engineers or some corporate upsell greed policy?

  11. #11
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Okay, I know that it is hard to understand any particular concept in abstract, so let's take a moment to construct a small model of what I'm asking, and see if SQL Server fits the bill.

    Say we're building a medical billing system that has all patients and service providers in the conglomerate DB.

    Fred is a customer and patient in NYC, and all his data is there in one local BE that normally serves Fred.
    His NYC doctors get paid for service they give Fred.
    NYC has dozens of local offices that serve different Freds. Each office has multiple FEs and one BE.
    These local offices share data on doctors, so there are duplicates, but only one office is responsible for the doctor's data.
    Thousands of offices across the country have offices with many FE and BE dbs, in real-time serving the needs of millions of Freds and doctors.
    The offices in Los Angeles have no idea who Fred from one office in NYC is.
    Fred visits Los Angeles and sees a doctor.
    The doctor sends the bill to his local Los Angeles office.
    The Los Angeles office needs to know who Fred is.
    The rest of the offices in the country have no need to know or store Fred's data.
    Each district can have an aggregator dbs for Fred's data as well as one large aggregator dbs for all Fred's (millions of people) in the system.

    Does SQL Server magically handle all this?

    Back when, we had offices connected via dedicated phone lines to a central server farm, and everything worked fine for the millions of Fred's/doctors and if a phone line went down, data wasn't corrupted.

  12. #12
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    First of all you have to decide on the type of replication see https://learn.microsoft.com/en-us/sq...l-server-ver16. After that you can setup the replication as you need with the filters that you want. You can choose yourself which tables or objects you want to replicate. We use a transactional replication to distribute the master data send to the central server to all local servers, and a merge replication to send all inserts/delets/updates from some local tables to the central server. It has been running stable for more than 7 years now. So, yes it can do what you describe once it is correctly set up.

  13. #13
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your fears about SQL Server are completely unfounded. It is used as a backend database for thousands if not millions of commercial applications worldwide.
    It is robust, secure and reliable, otherwise commercial application builders wouldn't use it.
    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 ↓↓

  14. #14
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    @ NoellaG & Minty. Thanks for the confidence builder, as I've said, as of yet I have no experience with SQL server, and after I get through the basic app design, I can move to testing it there. But still, I'm not sure it's needed at every office that has a MS Access style split db with maybe 10 to 30 FE users. If Access using wifi has problems for some timing reason, one can be assured that the problem will persist on a wired network, because there will always be some huge process that hogs bandwidth, thus causing corruption in the Jet/ACE db. In which case, Access simply becomes a nice toy (for building a cute cable management system for a single worker to use) but completely unusable for enterprise work (where there's robust backoffice, CSM, ERP, DM, etc. work getting done).

  15. #15
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by twgonder View Post
    If Access using Wi-Fi has problems for some timing reason, one can be assured that the problem will persist on a wired network, because there will always be some huge process that hogs bandwidth
    It's not a bandwidth or timing issue, it's a protocol/ connection issue. Bandwidth can have a performance effect obviously, but is not the issue here.
    In the background wi-fi connections are dropped and re-connected surprisingly often.

    Other protocols handle this seamlessly or the fact that it drops and reconnect doesn't upset it, whereas the SMB protocol (which Access uses) does not handle this silent dropping with any grace. It expects a permanently available connection.
    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 ↓↓

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need filter(s) on subforms of nav form to synch
    By kattatonic1 in forum Forms
    Replies: 6
    Last Post: 03-21-2017, 07:14 AM
  2. Combobox Wizard synch with Current Record.
    By MatthewGrace in forum Programming
    Replies: 3
    Last Post: 05-23-2014, 01:11 AM
  3. Table synch question...
    By MatthewGrace in forum Access
    Replies: 2
    Last Post: 01-21-2013, 12:15 AM
  4. Synch database after offline update
    By snoopy2003 in forum Database Design
    Replies: 0
    Last Post: 02-21-2011, 11:40 AM
  5. synch 2 combo boxes in datasheet view
    By kapplers in forum Forms
    Replies: 2
    Last Post: 11-09-2009, 09:09 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