Reliability Problems split FE/BE. A better Way? (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
Hi, We've been running our quotation database for a month or so & the application works well. It has a split FE/BE. The FE is installed on each local M/C & the BE is saved on a remote networked data drive (at our head office). Unfortunately, we are now experiencing regular drop outs "Connection Interrupted" necessitating restarting the app. Our IT department is unwilling to assist (I'd suggest they are not happy that we maybe treading on their turf!). Are there any work arounds that will improve reliability as we wish to use this remotely by our sales staff in the field. Appreciate any advice. Seems we may have to skirt around the fringes!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:51
Joined
Oct 29, 2018
Messages
21,357
Hi. When you say “in the field,” how will the sales team connect to the BE?
 

Micron

AWF VIP
Local time
Today, 05:51
Joined
Oct 20, 2018
Messages
3,476
a remote networked data drive
This is Ethernet or some other type of cable? Surely you don't mean wireless - that is a recipe for corruption.

Replication is out of the question; Sharepoint is no longer being trumpeted by M$; web based databases using Access are no longer supported either. Data import may be possible from field fe's (Get External Data) but would need to be well thought out.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
Guys, we need our sales staff to have access to the application via laptops as they visit clients. I'd anticipate this maybe via mobile hotspot or wifi. I understand that this will not work reliably with 'native' Access. I ask what can be done?. We currently have 4 PC FE installations connected to a server via ethernet with the BE on the server. I have heard of SQL Express, which seems to be what we may need to handle the data transfer. However, our IT department are unwilling to assist. Any advice would be most appreciated. What is Microsoft Azure Cloud? Has anyone any experience with it?
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:51
Joined
Jan 20, 2009
Messages
12,849
You are pretty screwed trying to connect any external computers to your network without the support of your IT Department.
 

Micron

AWF VIP
Local time
Today, 05:51
Joined
Oct 20, 2018
Messages
3,476
Check out Citrix. As I mentioned, synchronizing may be possible if users are not modifying the same records. If they are, who uploads first?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:51
Joined
Apr 27, 2015
Messages
6,280
To the best of my knowledge, cloud based BE are a non-starter. Your best bet, IMHO, would be to develop a batch transaction import routine. I have read one developer even managed to this with emails.

Not that this helps, but SharePoint offers an Offline functionality for just this type of scenario, but Access and SharePoint have parted ways and unless your company already has it, I doubt they would be any help, given what you have alluded to.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
Yeah, gents thanks. Will do some further investigations (negotiations).
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
An update, configured a laptop as a file server. Migrated database BE to SQL Express & FE on local M/C. All works, am able to log in remotely BUT response is very "Laggy" & borders on unusable. Don't know why this should be. We log in remotely to a different (Corporate) server for other stuff using the same PC (primarily accounting software) & it works fine. Had a look @ Caspio & registered a demo. I was able to up load my tables, but it appears that I have to recreate all forms & reports. This is a big job. Anyone got any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:51
Joined
Oct 29, 2018
Messages
21,357
An update, configured a laptop as a file server. Migrated database BE to SQL Express & FE on local M/C. All works, am able to log in remotely BUT response is very "Laggy" & borders on unusable. Don't know why this should be. We log in remotely to a different (Corporate) server for other stuff using the same PC (primarily accounting software) & it works fine. Had a look @ Caspio & registered a demo. I was able to up load my tables, but it appears that I have to recreate all forms & reports. This is a big job. Anyone got any ideas?
Hi. Unfortunately, any migration from Access to a cloud version will pretty much almost start from scratch. Not sure if a migration tool will ever become available for it.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
theDBguy, yeah looks that way. I have Access reports that include logic statements, lookups etc & also bucket loads of queries. Does anyone have experience reproducing this stuff in Caspio? Seems to me that Caspio only uploads the tables, which are a small part of my overall project.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:51
Joined
Oct 29, 2018
Messages
21,357
theDBguy, yeah looks that way. I have Access reports that include logic statements, lookups etc & also bucket loads of queries. Does anyone have experience reproducing this stuff in Caspio? Seems to me that Caspio only uploads the tables, which are a small part of my overall project.
Caspio can do almost anything you can do in Access, but you'll have to recreate them using their own logic tools to get the job done. I think you can even tweak some of the built-in features using JavaScript. As I said earlier, Caspio (and others) does not provide a complete migration tool from Access to their cloud system).
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
So, is it possible to sync remote data back to a central database?. What I mean is a full install of the database on each remote PC that can be sync'd to a central Database upon return to the office. 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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:51
Joined
Oct 29, 2018
Messages
21,357
So, is it possible to sync remote data back to a central database?. What I mean is a full install of the database on each remote PC that can be sync'd to a central Database upon return to the office. 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.
Hi. This used to be possible with Access Replication, but it too was deprecated by MS a few versions back. SharePoint is able to do a little bit of it, so that could be an option for you.
 

Micron

AWF VIP
Local time
Today, 05:51
Joined
Oct 20, 2018
Messages
3,476
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:51
Joined
Feb 19, 2013
Messages
16,553
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.
 

Micron

AWF VIP
Local time
Today, 05:51
Joined
Oct 20, 2018
Messages
3,476
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:51
Joined
Feb 28, 2001
Messages
26,996
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.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 22:51
Joined
Apr 1, 2019
Messages
712
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.
 

Users who are viewing this thread

Top Bottom