Synch tables between .accdbs

[twgonder]

@ Minty, post #15



Okay that's the best explanation I've read yet. The others were just advisement without reason.
Now, do wired WAN networks suffer this same protocol/connection issue?
If the answer is no, then we have to ask why Access doesn't work well over WANs?

[Minty]

Remember that these missives are about an Access back end.

Wired WAN is a somewhat different ball game. Because the SMB protocol is effectively a point to point connection it assumes it's quick and not laggy, e.g. it expects it to be on a local network.
Unless you have a Super reliable Gigabit connection between the two sites (FE & BE) you are still looking at poor performance, probable drop outs and then the inevitable loss of data .

You can prove this even locally, find an old 10Mbps network card and stick it in your file server, connect you network up and use a split access database over it. it will not perform very well.
Change it to 100Mbps, or better a 1Gb network card and watch the improvement, it's significant, particularly with multiple users. The same applies with a local SQL backend.

We use a lot of Azure hosted SQL backends, and with the latest versions of the ODBC Driver (17 or 18) we have very little to no issues, but you have to design the front end to minimise network traffic to ensure decent performance, and move much of the processing to the server, rather than dragging stuff to Access to process.

[NoellaG]

Hi

Access simply becomes a nice toy but completely unusable for enterprise work

I must agree to that, and not for network performance, but the main reasons for larger enterprises are:
- security: you just can't really secure access data and most auditors won't accept it
- locking mechanisms: access does not have a secure locking mechanism. I wouldn't use it beyond 10 simultaneous users and even that can give problems.
- Access data can get corrupt very easily and you don't have a log file, so you can't do rollbacks
- there is no backup system. The only thing you can do is making copies of the entire file on regular moments.

This makes it a fine tool for smaller businesses or even in schools for learning about data structures and VBA programming, but not a tool for large companies. Most of my customers don't install access on their network. This saves money on the office licenses and prevents that users create rogue data applications on the network.

[twgonder]

@NoellaG post #18

To be clear, are you saying that your typical large client, for these reasons, doesn't use Access as a front end either?

[Minty]

There are some sizeable applications and user bases that use Access as a front end but not as a backend data storage system, hence the move to a different backend such as SQL Server, either Express (free) for smaller requirements or more expensive versions for larger scale applications.
The licence cost is a bit of a moot point as you can install the runtime for free, or Access comes with nearly every Office subscription these days.

[NoellaG]

To be clear, are you saying that your typical large client, for these reasons, doesn't use Access as a front end either?

No large client uses access as front-end. We live in a time where the company apps need to be usable from the web or your smartphone. Most large customers use SAP Hannah or Microsoft Dynamics as frontend. If they use in-house development it is likely to be .net or other web ready environments. Even with CITRIX, Azure or sharepoint there can be issues with access front-ends

[kd2017]

When I'm learning to play with those powerful CTE and recursive queries in just little old SQLite of all things I get a little down on access.
Or when I'm doing fun stuff like OOP or building a gui with a functional tree view control in a python project I get a little down on access and vba... I wish ms could modernize this stuff, the RAD aspect of access is just so hard to beat.

(edit) or triggers, sql server stored procedures... this list could probably go on forever.