Go Back   Access World Forums > Microsoft Access Discussion > Access Web

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-22-2019, 03:40 PM   #16
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,229
Thanks: 10
Thanked 231 Times in 219 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Reliability Problems split FE/BE. A better Way?

Consider replication id (not replication). You are virtually guaranteed a unique id. However, if 2 users create records for the same principle entity (e.g. customer) you need a way to handle such situations. Do you appnd both? Do you append one and update it? Will that never happen? Those are the types of
questions.

Micron is offline   Reply With Quote
Old 08-22-2019, 04:25 PM   #17
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,344
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Reliability Problems split FE/BE. A better Way?

replication ID should not really be used as a primary key - it is large and unwieldy for linking tables. Better to use an autonumber field set to random rather than increment - back in the day I never had an issue with id clashes using a random autonumber. Keep replication ID for replication purposes only.

Although MS deprecated replication (which included queries/forms/reports/modules), you can still write your own for tables - requires multiple queries and additional timestamp fields for each table for creation and change. Plus a routine for managing clashes. Also simpler if records are never deleted, just flagged to be ignored.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-22-2019, 06:09 PM   #18
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,229
Thanks: 10
Thanked 231 Times in 219 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Reliability Problems split FE/BE. A better Way?

Quote:
I cannot get my head around how this may work, particularly if two remote PC's make the same new record (ie key field). Could this be handled? Anyone got any experience with this? Appreciate some direction.
I admit that "key field" probably means primary key, but I initially overlooked that term so my suggestion was to use the rep id as a unique index rather than a primary key. Thus if there were 2 identical PK values, the rep id could be the deciding factor in determining that the 2 records are not related. That is a shot in the dark, which is why I suggested to "consider" it, meaning read up on it and decide if it fits the business practice and the db design. I agree, a random PK is likely better, but maybe not as good as other suggestions such as investing in appropriate systems (e.g. Citrix) so that everyone can still work remotely on the same set of tables.

Micron is offline   Reply With Quote
Old 08-22-2019, 06:51 PM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,718
Thanks: 93
Thanked 1,712 Times in 1,585 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Reliability Problems split FE/BE. A better Way?

Here is an idea but it depends on your business model being compatible.

If you do not have a rock-solid network connection, you will almost certainly corrupt an Access app having a FE remotely connected to the BE by anything other than hard Ethernet or other long-haul network wiring.

However, if you can handle a model where you can build orders and take data on a local version and then build transactions to send to a central system, and if you can handle the idea that you would be working with point-in-time snapshots, you might be able to essentially "roll your own" form of synchronization.

You would have the "field" app that is a local Access DB, maybe even monolithic rather than split. You set it up so that your company info can be imported via some type of file, perhaps a spreadsheet. You have a routine in the FE that does this import in a way that it REPLACES rather than updates your local copy of the corporate data, which has to remain as an authoritative copy of everything EXCEPT what the sales reps do.

You also have a corporate app that remembers what the sales staff tell it in a way I will describe later. This corporate app has to be zealously protected because if it ever goes bad, the backbone of this idea falls apart. This corporate app has to have a way to export data that will be needed by the sales staff, in a format that can be downloaded as a distinct file, call it the "sync" file. The corporate app must build the "sync" file in time for the sales reps who need it.

So... the sales rep remotely connects to the corporate system to download the "sync" file. You download this file using Windows File Sharing methods or FTP methods. FTP is good because that protocol was designed for remote connections of uncertain stability (but it is only good for whole file transfer, so Access can't use it.) It allows for requerying and retransmitting data buffers after an error - but more importantly, it also knows when the transfer didn't work.

The sales rep takes the downloaded file and syncs it with his field app. Now he visits his customers and does whatever he can do with that snapshot. But it's local and should be highly reliable.

Later, he has done whatever needs to be done, returns to the hotel, and triggers a function to export any transactions that need to be sent to corporate offices. Again, he connects and uploads the files.

Now either by automation or through some sort of sales assistant clerk at HQ, the field transaction file gets processed. At this point, you have your order data and the sales person made those orders with the best possible info at the time.

The critical parts are the file exchanges and those can be retried until they are good. This does, however, mean that your sales people will be working with NEAR real-time data, not actual real-time data. If your sales model can handle this, you can implement what I said fairly easily with import and export functions and a few update or append queries.

If you cannot handle near-real-time data, there is no current solution that stays within Access 100%, though if Citrix is possible, it comes close. Even Citrix can have a loss of connection and at that point you run the risk of locking up the central database app anyway. However, Citrix is LESS likely to lead to corruption, particularly if you have it set up to allow session reconnection. Note, however, that most IT guys will tell you "Not only NO, but HELL NO" on session reconnection (the ability to reconnect to a disconnected session).

That's the only way I can see to do this and stay within Access.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
HillTJ (08-25-2019)
Old 08-25-2019, 09:25 PM   #20
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 197
Thanks: 69
Thanked 2 Times in 2 Posts
HillTJ is on a distinguished road
Re: Reliability Problems split FE/BE. A better Way?

The_Doc_Man, thanks. I understand. In fact, none of the data really needs to be "Real-Time" as long as the rep has the functionality they expect. I think your option has merit as long as I can pull it off! Given time,I might have a crack at it with a test database containing only a few unimportant files. I have a Caspio demo & have been approached by their sales staff. It seems that this platform would potentially work. Within Caspio I can upload my existing tables, but I have to write a new front end! I don't envy that! Also, I would have to stick with Caspio, maybe not so good.
HillTJ is offline   Reply With Quote
Old 08-26-2019, 05:36 AM   #21
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,538
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Reliability Problems split FE/BE. A better Way?

Quote:
Originally Posted by HillTJ View Post
I have a Caspio demo & have been approached by their sales staff. It seems that this platform would potentially work. Within Caspio I can upload my existing tables, but I have to write a new front end! I don't envy that! Also, I would have to stick with Caspio, maybe not so good.
Hi. Itís been a while since I tried Caspio. But if I remember it correctly, the reason why I like them over Zoho is they allow for unlimited users.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-26-2019, 05:45 AM   #22
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,718
Thanks: 93
Thanked 1,712 Times in 1,585 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Reliability Problems split FE/BE. A better Way?

HillTJ, the trick, as always, is going to be to get within a particular mind-set. Just as it takes some mental re-adjustment going from Excel to Access, so it will take some careful mental experimentation to switch from real-time updates to time-deferred operations.

To be honest, this is what was actually done before computers were widely used in retail anyway. And heck, Navy Personnel systems used to literally "cut a transaction tape" on a daily basis and transport via a military vehicle from one office to another; at least, they did so until the advent of network file transfers. In fact, as recently as 2017, they STILL used the phrase "cut a tape" to represent making a transaction file to be transferred to another agency. And they still made backup transaction tapes until about 2005, although that WAS a backup and not the primary means of transportation by that time. I was there; I had to manage one of the tape-cutter systems. The rest of the world might have been doing more modern things, but the Navy didn't start that particular kind of network usage until the early 1990s.


__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems with Split Database cbriscoe General 13 01-14-2013 05:22 PM
reliability of an MDB file? kefka95 General 2 10-19-2012 07:39 PM
How to test reliability? meyou General 6 02-25-2010 10:26 AM
Read-only problems after split cstanley General 5 02-19-2004 09:17 AM
Reliability darag2358 General 2 10-17-2003 10:10 AM




All times are GMT -8. The time now is 09:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World