Multi-User Shared Drive FE/BE Setup (1 Viewer)

sgthuth

New member
Local time
Today, 19:48
Joined
May 7, 2018
Messages
7
Good morning all,
I've looked as much as I can through some previous posts and have found certain ones related to my post but I am unsure if they are the right answer I am looking for.
Soon (this week), I will be implementing a database setup for 6 operators and 2 managers that will be using logged data to align daily/shift performance for manufacturing machines. My current setup is 8 FEs and a single BE all on a shared drive. All will open the files using a shortcut placed on the public desktop of their local machines. I will implement what I have for the time being as it is a high priority to at minimum start collecting the information but I do know that an issue with network connectivity will arise. With having FEs and a network interruption occurs, what happens? Does the linked tables just lose connection? Does the data become corrupt? Can the linked tables collect data while disconnected and just update the lists once connected or should I collect the data on local tables? Can someone just explain what happens when a network interruption occurs? I do not like have a single BE (all my eggs in one basket). I do not want this to be running smoothly and then out of no where, the data be gone. Trying to think of a resolution, I could write some vba in an initial db that would pull a copy of the needed FE off the shared drive to the local machine/user documents. Then on a timer, occasional "upload" the information to the shared drive/BE. Would that be better practice? I realize this is a lot at once but I work in rapid solutions for my department. This will be the first thing I implement that is not used but a single user and also the idea of having the FE/BE setup.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:48
Joined
May 7, 2009
Messages
19,169
There is a little chance that data will be lost on 1 shared BE.
You distribute your FE on each user in the network.
 

isladogs

MVP / VIP
Local time
Today, 23:48
Joined
Jan 14, 2017
Messages
18,186
1. The FEs should NOT be on a shared drive. Each user needs their own copy of the FE on their own computer

2. The reliability / stability of your system will obviously depend on your network. Is there a reason why you are so concerned about network outages? If so, focus on fixing that.

3. Using SQL Server will provide additional stability compared to Access BE

4.
Then on a timer, occasional "upload" the information to the shared drive/BE. Would that be better practice?
No - more complex & not a good idea

5. Make regular backups of your data - at least daily - more frequently if appropriate to your system. This can & should be automated
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:48
Joined
Feb 28, 2001
Messages
27,001
Your description is a bit jumbled and you DID ask more than one question.

What ideally should happen is that if everyone has a personally-assigned computer with Access installed. Then you should run the FE files from each individual computer. They would then link to the common BE file on the shared drive. In that configuration, if you design the FE correctly and set up the action queries for optimistic locking and set report-only queries for NO locking, you should not see too many bad side effects from a six-person user base. I've personally run a 40-person user base more than once and had little or no problems.

Your description implies that the FE files are NOT on the local computers. Having the FE files on the shared drive adds to your network traffic even if everyone has a separate copy of the FE. That is because Access on the local drive is doing the work. Everything that is on the shared drive is one more network I/O operation added to the mix. Even with separate FE files, file locking occurs and if the files are ALL on the shared drive, then the file LOCKS are ALSO on the shared drive's host. But if the FE files are local, then the locks associated with the FE are ALSO local and don't involve network ops.

This leads me to ask why you put all the FE files on the shared drive even though you say they will be separate per-user. Are you using a Terminal Services solution such as RDP or Citrix? If you are doing that, you should have included that in your description because it is supremely important to the understanding of your problem.

The other question has to do with your question about network interruptions. Does that happen often? Because if you are NOT using something like RDP or Citrix or some other remote-session solution, you are going to get whacked badly no matter WHERE the FE resides. It is the active computer running Access that is exposed to the danger, not the computer "hosting" the files. The BE host is essentially nothing more than a file server unless you are using RDP or Citrix or other remote connectivity software. If the FE files are also on the shared drive, then the shared drive host is a file server for the FE, too. So for us to get a better feel for things, what is your network configuration?

And as to your question regarding what happens during a network interruption, that depends on your configuration and we cannot answer your question until we know more.

Your last question regarding whether you should have "local" copies and occasionally try to synchronize FE and BE - can be done but gets deep into discussions of risk/reward and cost-benefit analysis. Synching a FE/BE pair certainly CAN be done but is a lot of extra work both at the design phase and later in the implementation phase.

The ideal situation is that you use the Access FE/BE splitter and scrupulously "purify" the FE/BE so that they have minimal function overlap. The BE should be data tables (and relationships) ONLY. The FE should ONLY have links to the tables being shared. P.S. if you build the relationships in the BE first, or if you built a unified DB first with relationships and THEN did the split, I think the relationships carry over - but I could be wrong about that.
 

sgthuth

New member
Local time
Today, 19:48
Joined
May 7, 2018
Messages
7
Sorry for the delayed reply... addressed multiple things...
@ arnelgp, Thanks...
@ ridders,
Thanks for the reply. So some slight back story... my boss placed me in the desktop support office and I have been working here for 2 years now and am the first programmer they have had in many... many years. The desktop support here is limited and can't do much of anything except put in tickets for the corporate office to address. Honestly, they are thinned out as much as they can be... I did desktop support and field work in the military for a decade so I know. They do have the ability to work on individual systems that do not connect directly to the corporate scheme. They can login locally to machines and add say a shortcut.
(1) To address the FEs, I can have an initial db (started by the desktop shortcut) that runs code to copy the needed FE from the shared drive to their user profile, then open it. I think this would resolve master FE updates too without interfering with the operation of the copy they are using locally. Also, I would not need a local admin to change the file each time an update is needed.
(2) Having a slight worry about multi-user access, I started to look at best setups and saw corrupt data problems during interruption using FE/BE. I asked the lady behind me how often we have network failures and she said less often than most here think; however, outages and interruptions occur often just not covering the entire plant. That's why I am worried.
(3) I would love to setup a server and administer it but we don't have the rights to do things like that here. I have to find solutions around current operations and ERPs I'm not allowed to touch. My boss is actually using past projects I've completed each time he gets to display how inefficient our apps/IT/systems teams are that only breathe corporate relations. This project I was just handed was actually the initial reason I was made a programmer. About a month of working on it 2 years ago (While also going to school full time), I was pulled off because an integration team was coming to work on it. They have produced no results and from what I have seen since being here, few change requests on our ERP systems get completed. So, a week ago my boss came to me and said to start it back up and since, I've made a preliminary version and they will begin using it this week.
(5) With not having the ability to setup and ensure backups are performed, I am most-likely going to use vba to make a backup.
...I do not intend on touching this unless design changes need to be made ie more data needs to be collected or it needs to be implimented in more places. I like my dbs to be self-sufficient and have the least amount of user interaction as possible.
@ The_Doc_Man
Yes that was jumbled... I was not sure where to start. I guess I should have started with "How to setup an FE/BE db that occationally has network interruptions?" Which would have easily had a response of "I'm an idiot" attached to it. All I can say is I'm doing what I can to find a solution for the problem they are having. I butt heads all the time with our corporate office but all it is, is that they signed a contract with 2 companies to handle information (active and reserve) and people like me prove they are not needed. Limiting my abilities allows them to continue contracts. (yes, I was whining)
For the operator FEs (currently 6), all they do is fill out a form and once they click submit, the information is loaded to the linked table. The form is not tied to a record source. All data is sent to the linked table via vba ... a recordset. Is that best (optimistic locking) if the individual FE is on their local machine? To address connection problems, would it be safe to have the information sent to a local table, and occasionally sent to the linked table, having less network traffic and if the network is down for any reason, movement will take place after it comes back up? The main reason why I was leaving the FEs on the shared drive is that the FE users change all the time and I would not be able to address sending each individual the file they need to operate. If I take the time to write an initial startup program with vba, I can just copy the needed FE to the user with little interference from the user.
Services: I'm unsure what our services are. I know about 2-3 months ago we swapped to a virtual server here but I haven't done server administration is about 10 years and I am unsure even where to start with describing our configuration. All I know is we have a shared drive and that is the only way I have to get things in operation.
Only reports are created from the manager FE (unsure if I will need more than one since all are getting the same reports) Not much actually occurs with the use of the FEs and BEs other than filling out a form twice by each operator per hour and then merging the data into on reportable format at the end of each shift.
... Congrats grandpa... we just had our 2nd child last month. I don't know what I'm gonna do when they have kids of their own.
 

isladogs

MVP / VIP
Local time
Today, 23:48
Joined
Jan 14, 2017
Messages
18,186
You call that a delayed reply? Thought it was quite fast myself
I'll just respond to the parts related to my reply:

(1) To address the FEs, I can have an initial db (started by the desktop shortcut) that runs code to copy the needed FE from the shared drive to their user profile, then open it. I think this would resolve master FE updates too without interfering with the operation of the copy they are using locally. Also, I would not need a local admin to change the file each time an update is needed.
Agreed - that was what I'd suggest as well

(2) Having a slight worry about multi-user access, I started to look at best setups and saw corrupt data problems during interruption using FE/BE. I asked the lady behind me how often we have network failures and she said less often than most here think; however, outages and interruptions occur often just not covering the entire plant. That's why I am worried.

As I said before, if the network is a problem , there could be issues but how much will depend on how flaky it is

(3) I would love to setup a server and administer it but we don't have the rights to do things like that here. I have to find solutions around current operations and ERPs I'm not allowed to touch. My boss is actually using past projects I've completed each time he gets to display how inefficient our apps/IT/systems teams are that only breathe corporate relations. This project I was just handed was actually the initial reason I was made a programmer. About a month of working on it 2 years ago (While also going to school full time), I was pulled off because an integration team was coming to work on it. They have produced no results and from what I have seen since being here, few change requests on our ERP systems get completed. So, a week ago my boss came to me and said to start it back up and since, I've made a preliminary version and they will begin using it this week.

Something to consider if necessary as a future upgrade

(5) With not having the ability to setup and ensure backups are performed, I am most-likely going to use vba to make a backup.
...I do not intend on touching this unless design changes need to be made ie more data needs to be collected or it needs to be implimented in more places. I like my dbs to be self-sufficient and have the least amount of user interaction as possible.

When I said it can & should be automated, I meant the backups should be run using VBA. You could also consider doing this as a scheduled task

Good luck
Also I became a granddad on Christmas Day last year. Best present I've had for years though it wasn't quite when my daughter planned it to happen :)
 

sgthuth

New member
Local time
Today, 19:48
Joined
May 7, 2018
Messages
7
@ ridder

Congrats on the Christmas present. Family means a lot.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:48
Joined
Feb 28, 2001
Messages
27,001
OK, let me address the question of saving up data for a timed synch to the BE vs. making all updates immediately. There is a chimera here. To make an update to the BE, you have to connect to the BE from the FE and an operation occurs. BUT... there is 0.00% practical difference between making a change directly vs. making a change on a timer. In EITHER case, the FE talks to the BE once to do an update. Adding the timer to do a delayed gang-update adds steps to your design that are not needed if you set up the incremental update correctly. One touch now or one touch later. Pay me now, pay me later. All the same. Stated another way... supposing your feared network outage occurs when the timer-based update goes off? An outage got you anyway. It's like that great quote from Ghost Rider as Johnny Blaze tells the Devil, "You can't live in fear."

OK, let's address the idea you described, of using a recordset on an unbound form to do an update. If you make the form correctly, you can just make a bound form. Access can make the update for you. AND there are ways in the "Form Properties" to restrict using a form for data editing. You have form properties to allow insert, allow edit, and allow delete as separate properties. So make the form "AllowInsert = Yes; AllowEdit = No; AllowDelete = No." You can set up the form so that it uses optimistic locking for updates. That is your best bet.

Here is WHY "optimistic" is your best bet. When your DB is set up for optimistic locks, NO locks are taken out until the last possible moment. Then a lock is tested; if favorable, the lock is set (but if not, you get a trap that you can intercept with an error handler); the update occurs; the lock is released; and voila your update is done.

Those steps are done behind the scenes by Access in rapid sequence. VERY rapid. Like, on a decent network speed, started and done in much less than one second. Pessimistic locking opens long-term locks and everyone would get tangled up with each other. NO LOCKS is not wise for updates only because you don't want two users to update the same exact table at the same exact time. Therefore, your best choice is optimistic locking. BUT for reports, you aren't changing anything (or at least you aren't supposed to be changing anything) so you don't need to lock anything.

You asked an earlier question about updating and how soon changes would become visible. How about... once an update occurs, the next time anyone launches the form, or the next time anyone does a refresh on an open form, the new data can be seen. Immediately.

From subsequent discussions I think you must be using direct LAN connections, local system to server WITHOUT use of any remote connectivity s/w. You would know if you were doing this because you would need TWO logins - one for the local machine to join the domain and a second one for the remote session. So the odds are this is a simple LAN setup, not the more complex case.

So that means you are probably using Access in its "native" mode, which means you are using standard Windows file exchange protocols such as Server Message Block (SMB). This is a TCP member of the TCP/IP stack of protocols. Its properties are a negotiated socket number on both ends; an incrementing sequence number on successive exchanges; a keep-alive event (default is usually 10 or 20 seconds); a self-managing time-out measured in many seconds (30 is the default, IIRC), and a total inability to reconnect broken sessions.

So what happens when you lose the network? Two cases:

(a) Access was open but nobody had done anything to cause an update. I.e. no transactions were under way. In that case, NOTHING happens. No traffic = no loss of data. No recovery required.

(b) Access was open and an update was under way. In that case, the update will not occur, either cleanly or at all. BUT if you use the shortest possible sequence to do the update, the window of opportunity for you to be caught in case (b) is VERY small. Remember I said "use optimistic locking"? Doing so narrows the window for case (b). The longer the transaction, the wider-open that window gets. So saving up a block of updates and THEN sending them invites a bigger window. Do transactions piecemeal and immediately.

In the worst-case scenario, you will have a partial update of your table. The data for that update will be lost (probably) and you will have to kick out everyone so you can do a manual compact & repair of the DB. The bigger the update, the worse your data loss will become. (Another reason for incremental vs. ganged updates.)

If you have some other questions, don't hesitate to ask. Can I respectfully ask that you try to break apart your questions a bit, though? Your last couple of posts have been a bit complex. Don't take that as chastisement. It is just easier for us if you put a little white space between multiple questions in the same post. It becomes easier to FIND your questions that way.
 

sgthuth

New member
Local time
Today, 19:48
Joined
May 7, 2018
Messages
7
@ The_Doc_Man
Thanks for all the input.
Just a quick breakdown on what should happen if no issues arrive.
The front end db is opened locally and a form asks the operator what station they are located at. Once the information is accepted, a sql query runs on the linked table and then populates the main form for that stations last entry (from the previous operator). Then once the information is viewed, the form is refreshed with the fields enabled. If the operator has to fill the form out, it's completed and submitted. The information is then written to the linked table for managerial use at a later time.

With the network issues that happen in that area, I decided to start with making sure the backend can be reached. If not, the user is notified it can't be reached and a new form is opened. All information is stored locally until the connection is established. Once connected, the information is then relayed back to the linked table. If the connection is good, the information is put on the linked table immediately. I'll use the timer just in case the connection is down during a submission.

I guess the easiest explanation for where I am now is implementation. I do not know what would be best. I do not understand the differences between runtime and accde. I know 'e' allows you to limit the access menus, navigation pane, etc. Would that be better than a runtime version 'r'? Can I run a 'e' in runtime with a shortcut? I tried it but I get errors during opening. Or is there a better way to implement the db?
 

isladogs

MVP / VIP
Local time
Today, 23:48
Joined
Jan 14, 2017
Messages
18,186
A couple of things in response to your last post

1. The workstation can be obtained using code so no need to ask users to input this
Code:
Environ("ComputerName")

2. The Access runtime is a free cut down version of Access which allows users to enter/edit data but restricts what users can do e.g. no design changes.
You can simulate a runtime version by changing the suffix from accdb to accdr.
If you distribute files as ACCDR, note that knowledgeable users can restore functionality by renaming as ACCDB.

ACCDE files are Access executables where the code is not visible to users so cannot be edited. It makes distributed files more secure because the code is protected. Changing the suffix back to ACCDB does NOT restore the code.
If you convert to ACCDE and rename as ACCDR, this gives additional security.

Two more points
Make sure you keep an ACCDB version so you can continue to do development work.
ACCDB files will work in both 32-bit and 64-bit Access providing the code is modified appropriately. However separate ACCDE files must be created for each bitness
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:48
Joined
Feb 28, 2001
Messages
27,001
I will not amplify Colin's comments on run-time and compile files and their uses. He covered that topic quite well.

Colin pointed out the Environ("ComputerName") trick. There is also an Environ("UserName") trick, so you never actually need to have the user answer any identity questions IF all stations are on a domain and they use a username & password or smartcard & PIN type of login.

The idea here is that if you have a domain login, you can have your app "trust" the domain's security. Before you question that, let me add that we used exactly that scheme for a U.S. Navy site and Sensitive (classification) machines. That level of login security was adequate for the Navy Enterprise Data Center in New Orleans, which was the south-east regional data center for all sub-branches of the Navy and Navy Reserves.

If the users do not log in to your local domain, though, you will need a way to identify each user. Then you might need to consider some type of manual login process.

I must ask how long your networks are down (when they are down at all)? And the idea of saving up an answer for later transmission still seems a bit questionable.

Are ANY PARTS of the FE linked to the BE, even if not via an open form? I would think your linked tables are statically linked. If that is the case, then your scenario of attempting to determine whether the BE is connected can never occur. If the network and BE are down when you launch the FE, the local app will simply fail to come up.

If the network goes down when you have statically linked tables, the SMB protocol's "Keep Alive" timer will go off in a few seconds. If that happens, the FE will go down within a few seconds after the BE vanishes from sight. The FE will "lock up" and refuse to function. So your FE wouldn't be able to take data in the first place. I still think that "save it for later" approach is merely making more work for yourself.

Which is not, by the way, intended to disparage hard work. I just think you are working too hard on the wrong problem. You need to find out WHY your network is cracking and address that. A stable LAN is the sine qua non for smooth Access FE/BE operation. All your design gyrations are flying in the face of the wind if you don't fix the LAN. If you don't have the authority to make it happen, then go to the guy who wanted this project and tell him that the LAN is in the way of a smooth, easy, efficient solution. See if HE has enough clout to get some attention on the problem.

Unless you have literal CRAP for equipment, or perhaps your IT staff has a vendetta against someone in your group, network outages should be few and far between, particularly for Ethernet varieties of physical layer structure. This is your REAL problem. Address the LAN and watch everyone smile more often.
 

sgthuth

New member
Local time
Today, 19:48
Joined
May 7, 2018
Messages
7
@ridders
I am unsure why I did not think of just acquiring the machine name. I was getting the login name for accountability purposes with each log; however, this project has changed... fairly significantly-ish... Lol. I'll explain that below. I think my understanding of the different file types is at least enough to judge which to use. I ended up using a shortcut to open the .accde file in runtime as I described before but for clarity, the reason for it erroring out was rather elementary. The shortcut target path was incorrect. Thanks for the insight.

@The_Doc_Man
Navy? If you don't mind me asking were you in the military? My A-School was at Corry Station, FL for the same topic you spoke of/more crypto. My class was actually the last to receive training from the Navy before the Marine Corps took over back in 2005/6. I served a decade and very little taught there was carried over to the fleet as it was so outdated... yes, off the real topic.
Just so I understand correctly, a frontend with linked tables to a backend on the network share will not work or work long if the network goes down. I don't know why but I was under the impression that if the linked tables were not open or in use by other means, the db would at least still run. If that is not the case, would a solution be to have no linked tables and just export the data submitted (when connected)? I'll have to test the no network thing. I think I tried before and ended up having to just restart because it did not connect back up on its own. I did not complete the test then as I probably forgot what I was doing after the reboot.
ADD is real bad at this point for some reason. I have a hard time just focusing on one thing (this project). I usually have several but this is 'priority'.

@ Both...
Changes: (Sorry for the rambling)
As it was described to me previously, collecting the data and at minimum pushing a raw data file out was the current goal. After a few meetings, 2 top tier guys are not in agreement with implementing. 1 wants it implemented now. The other wants a 'package' and until that's complete, this portion (maybe 15%) of the overall 'package' will not be used. The way I see it, the one guy is a dick and maybe he just feels like if his life can't be easier (or automated), no one's will be... Of those teir guys, the one that doesn't want any stage pushed out on it's own is very nonchalant stating, "oh... we have plenty of time, don't rush" and then the other guy has been waiting on this for over a year, "The integration/data team was assigned this project and has nothing showing their progress. I don't even know if they are working on it at this time." It seems to me, communication is not very good at that level.
With what is going on, we are replacing the 4 old computers down there, adding 2 wireless stations (Stick PCs), and from what has been explained to me, it's all due to this project being so important.

The main screen has changed from the main input form which was very simple... no confusing elements which is what I was aiming for. Now I'm redesigning again... they want the main screen to display the machine, its current status (running - yes/no), if no-why, current shift performance, and 2 separate listings below that for last stops (the info this is meant to collect), and previous shift performance... Not too bad. Just basically starting over again.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:48
Joined
Apr 27, 2015
Messages
6,286
Sounds like the dreaded NCTS gestapo - which you may be a member!

Won’t hold it against you, just change it from the inside(!)

Here is a link to an FE updater that has really made my life easier. I feel it is the best option for guys in our position becuase it acts as an installer package and updater all in one. Simply brilliant in my opinion and written my a member of this forum.

https://access-programmers.co.uk/forums/showthread.php?t=207187&highlight=Mosca

I think his website is no longer available but the complete code is there. If you have a problem, send me a PM with your e-mail and I will send you a txt file.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:48
Joined
Feb 28, 2001
Messages
27,001
If you don't mind me asking were you in the military?

I was never Navy or any other military. I was a Navy contractor through a contracting company - but I did that for 28 1/2 years across CNRF Code 10, NAVRESINFOSYSOFF, and SPAWAR's NEDC New Orleans. And, if you are familiar with Navy contracting, I worked for at least 7 different contractor companies - though it could be more depending on how you count mergers and acquisitions where I continued to work for the same-named company unit but it became a subsidiary of a larger company.

The purpose of the Navy group to which I was associated didn't actually change that much other than growing in size, but the Navy higher-ups couldn't leave the authority infrastructure intact. We were essentially a hosting site for some projects. Started with six, grew to 18 during the aftermath of Hurricane Katrina, had reached over 80 different projects - some simple, some Classified, some Secret - before I left.

I started as a contractor in Feb 1988 and retired at the end of Aug 2016, total of 28 1/2 years. That included being in New Orleans in 2005 when Katrina roared her way through the area. The reason our unit stayed viable and didn't get absorbed into someone else's rice bowl is that we kept vital computers running through the worst disaster ever to hit New Orleans. Those who can keep things running in times of ultimate chaos get rewarded. (Usually, with more work because everyone says, "Here, keep MY systems running, too!")

OK, back on topic. You can, indeed, take the approach of not connecting to a back end but rather just storing and forwarding data to a central site. BUT that means you need something (and usually, SOMEONE) on the central system to take in data, process it, store it or dispose of it, etc. when it comes. You just added a human operator requirement to the server AND if I recall correctly my D.o.D. policies on same, would violate the "separation of duties" concept in so doing. Because Operations is not supposed to have anything to do directly with Projects.

You are running in fear of this network outage but I still don't have a clear picture of how often or how long these outages occur. What is the story here? WHY are you so afraid of outages?

Trust me on this one. Your users, some few of whom will probably prove to be thumb-fingered sub-apes, will cause more damage than any outage ever could, mostly because of the incredible number of mistakes they will make. An incorrect and premature log-off, a failure to log off at all, hitting the wrong command button, putting in the wrong data, pressing OK, and THEN realizing they had an "Ooops" moment... these will make you tear out your hair.

If my attitude sounds a bit unkind, I think I earned it. I used to be known as the "Senior Systems Janitor" because I cleaned up everyone else's computer messes. (OK, it's a skill I learned by necessity.) Your issue is that you simply need to be able to do regular maintenance and regular backups on a system that is normally linked FE to BE and just let the system do its thing. That is the path of least effort.

To be stable, you need to assure that the users don't see the Access Navigation Pane. Instead, you have some kind of switchboard form that hides the panes and only lets them click buttons to which they are allowed access. Then be sure to trap errors so that you can clean your messes "on-the-fly" as often as possible.

The more you let your users see that they didn't NEED to see, the worse off you are. In a properly built Access app with optimized queries, normalized data, and minimal locking, your network outage will do minimum damage.

I don't know why but I was under the impression that if the linked tables were not open or in use by other means, the db would at least still run.

Can't tell you where THAT originates, but it ain't true. The idea is that on launch, Access has to verify that the static links are functional. So it tests every linked table as listed in the FE to compare it to the BE. On launch, if that verification can't work, you got nothing. But... SMB protocol has timers and time-out / keep-alive functions as a fully compliant member of the general set of TCP-class protocols. So if a keep-alive fails, Access gets warned that the network has gone sideways on you and will stop you from doing anything else WHETHER OR NOT it was going to be done on a linked table. The idea is that Access will PREVENT a change that it knows cannot be made, which is why it does what it does.

I think you STILL are missing the elephant in the room. If the network goes down ANY TIME that data transfer occurs, you have been whacked by your network. It DOES NOT MATTER that you had continuous connection FE to BE and did incremental single transactions or you had everything set up for a gang transfer and the network chose the moment of the gang-bang to go sideways. Network outages can happen ANY time. Interrupted transfers are interrupted regardless of whether you were talking one transaction or a pot-load.

The work you have to do to assure that synchronization would just be duplicating the efforts and abilities built into Access itself if you have a bound table for your updates. You are adding work to yourself. If you are doing this for the sheer joy of learning, then good luck, brother. You will learn a LOT.

If I seem harsh or a bit brusque, I am not trying to be a hard case. It is because I have the nagging feeling that we are not fully communicating. I don't know what I'm missing here. I just sense that we ARE missing something.
 

Users who are viewing this thread

Top Bottom