Tips on General DB Deployment and/or Management

[JMongi]

I'm to the point of buttoning up my application UI framework so that I have something that can begin to be used even if it doesn't actually do a whole lot at the moment. So, I have some unanswered questions, loose threads of ideas floating around from the various articles/forum posts I've read. I'm going to try and capture them in this thread.

1. I've read various places about stability being improved by maintaining a connection the the BE at all times. Is this true? Is there a preferred method to do this? For reference I'm using a modified version of @isladogs login and session logging code/forms found here. (EDIT - I linked to the wrong code reference in my original post. This link is now correct.)

2. Are there any cleanups/checkups that would be good practice to do before the user quits the application?

3. I know that any type of network connection other than wired is increasing the risk of data corruption. There will be users that try to run this app over the WiFi here at work (which thankfully is almost as stable as the wired network) and some may stumble on the idea of running via VPN from their home. How can I manage this situation?

4. I know little about managing the life of an app after deployment. Any suggestions that will make my life easier in the future regarding app updates, error logging, etc would be much appreciated. As a reminder, I'm a self-taught programmer/developer with no formal training other than the school of experience.

5. Any other bits of information that I could use that I don't even know that I need to know!

Thanks again to all of the fine folks on this forum willing to share their hard won knowledge with me to make my life a little easier!

EDIT #2 - Add Punch List
1. Split the database into FE and BE
DONE

2. Create structures to handle future FE versioning
TBD (Likely @Pat Hartman suggestion .bat file for grabbing the latest FE every time).

3. Avoid WiFi logins
IN PROCESS (Warning splash screen for active WiFi connection; Flag/Log warnings in session logs.)

4. User Level Access Control
DONE (@isladogs code for RC4 password login)

5. Manage User Sessions
TBD (implement @isladogs Logged In User utility for maintenance)

6. Error Logging
IN PROCESS, Error handling is installed but my sub routine can be expanded to include better logging/tracking.

7.Crash Handling
TBD, This needs some work and I need to better understand what Access is doing. Active processes after app shutdown need cleaned up.

8.Persistent BE connection
TBD, Possibly in conjunction with session logging.

 

[Ranman256]

1. no connect problems for us. we use odbc to connect to sql server backend.
always connected.

2. no cleanup. Local tables only hold 1 record, so no db bloat.
some apps the 'local' table is in a local db linked to the master local db.

3. microsoft db corruption on network db with multi users. All my users have their FE on their PC. zero corruption now.

4. I have a db of all users and their IP address. I update all users by copying the upd db via
vsrc = "c:\update\MyApp.accdb
vTarg = "\\10.1.1.21\c$\apps\MyApp.accdb
filecopy vSrc vTarg

 

[JMongi]

Thanks! To clarify for future comments this is currently a full Access database with Access FE and JET BE. The BE will be stored on a local server location. That's the extent of it. Our network IT is administered by a 3rd party contract and I've become the defacto on site IT. FE will be on between 5-10 users.

 

[isladogs]

I have a different view on the above questions.
1. Yes. A persistent connection to the backend will improve performance. Without that, you will have the overhead of Access repeatedly dropping and recreating the ODBC connection(s).
2. That depends on what type of cleanup you are referring to. Emptying temporary tables is a Good idea. Automatic compact on close is a bad idea.
3. Using a WiFi connection to the network will lead to corruption. Its just a matter of when. If your users connected to their wired work desktop from home using Terminal Server or a remote desktop connection that would be fine. However what you describe isn't going to be safe.
Unfortunately nobody has yet devised code that can reliably detect with certainty when Wifi is actually being used
4. Probably...but as I don't know what you don't know, I don't know what to advise

 

[JMongi]

LOL! Touche!

 

[JMongi]

@isladogs - I've seen #3 stated many times. As an ignorant n00b I'm going to ask you to explain in much greater detail WHY that would be the case. While network latency and packet loss is generally lower than your typical WiFi connection, a wired connection is not magically more reliable than WiFi. In fact, for whatever reason, currently our WiFi connection to our local intranet has been rock solid while our ethernet connections have been flaky. Is there something inherently different about WiFi that leads to corruption? If the corruption is caused by the loss of connection between the FE and BE, that could happen with any network connection. Part of me feels that this is older advice when wireless connections were VERY slow and sketchy.

Again, I'm a n00b, but I've seen a lot of "trust me, don't do it, it's bad". I appreciate looking out for my well-being and the personal experience. Unfortunately, the engineer in me is going to need a more technical explanation as to why a modern WiFi connection to an intranet would cause corruption (eventually) while an ethernet connection does not.

Edit: I should be clear that I don't necessarily think you all are incorrect. I just need to understand the actual technical reasons in order to minimize risk.

 

[Isaac]

I almost have to sadly agree with what Frank R. said the other day about the relevance of Access. With everyone working from home, and everyone on VPN, (and who doesn't use WI-FI when they work from home??) our oft-repeated quote that you must not use Access with vpn over wifi is getting quite tiresome to everyone. It sounds like telling someone not to use a cell phone. That may have worked in 1998!

Get on Citrix or RDP if you can, but for anyone who can't.....it's a hard time.

 

[Isaac]

One thing you need to plan and create BEFORE deploying is a way to auto-update everyone's FE automatically.

Here is a thread where we discussed how we did it ad nauseum.

Updating the front end on local server

Hello, I always wanted to know how to update the Fe and Be using a version number. Currently I have the Be in a folder on the local server and I distribute the Fe to each employee. Is there a way when I update the FE each person using the database FE when open it runs a version check and if its...

www.access-programmers.co.uk

 

[JMongi]

@Isaac - I/you may have inadvertently stumbled on a solution. I always forget about the wide variety of usage cases that you (plural) run into offering suggestions. While we are not a tech savvy company by any stretch, since we contract with the 3rd party IT, our file server is a true server running Windows Server which should have RDP built in, yes?

 

[Isaac]

If you are in a loose enough environment where IT actually gives people permission to RDP into the server - which sounds very surprising but yeah, that might work, not sure how many concurrent connections will be allowed, but give it a try....As the years go by, Remote Desktop is being seen as more and more insecure, I haven't worked at a company that allowed it in years but if you can, go for it.

 

[theDBguy]

Is there something inherently different about WiFi that leads to corruption? If the corruption is caused by the loss of connection between the FE and BE, that could happen with any network connection. Part of me feels that this is older advice when wireless connections were VERY slow and sketchy.

Hi. Pardon me for jumping in but have you seen this old article? It discusses WAN, but it's synonymous to WiFi, in this context.

MS Access on a WAN?

 

[JMongi]

@Isaac - I guess I'll have to read up on more about it. That would probably be unlikely. As it stands right now I don't have access to anything other than the various shares setup by the IT company. Now, I have physical access and I presume that it's not locked down enough that I couldn't connect a keyboard and monitor to actually start rooting around (no pun intended) but they're always watching....

I did find some potential code to flag whether the user has an active WiFi connection.

MS Access Is User Connected by Wireless | DEVelopers HUT

A function that can be used to detect if the user is connecting via a wireless connection and can be used to control access to the database to avoid corruption.

www.devhut.net

In the end, CURRENTLY, its not a big deal, we do have three users who have laptops as their primary devices and they USUALLY plug into the network at their desks, but, they could connect via WiFi. So, I'm looking at ways to minimize the risk of corruption. Moving to SQL Server Express in the future is a possibility. I just don't want to tackle THAT learning curve to get v1 of this app rolled out.

 

[isladogs]

I agree that the oft-repeated advice to avoid Wifi causes issues for many people/organisations but its a sad reality.
WiFi will experience frequent, often short interruptions. Sometimes we may not actually notice them as users BUT if data is being written/edited when interruptions occur then there is a very strong risk of corrupted data. WiFi is more reliable than it used to be but its still too much of a risk.

It is for that reason that many developers including myself have tried to add code to detect when WiFi is being used to connect to the network. Daniel's code is one such example. I can find even better examples e.g.code by adezii at UA. None work reliably for this purpose.
Its easy to check whether a wireless connection exists and whether it is turned on. However no code I've yet seen can determine reliably whether e.g. a laptop with both wired and Wifi connections is connected using Ethernet (or not)

NOTE: If your data is stored in SQL Server then that is more resilient to handle such interruptions

If your Ethernet is even more unreliable then that needs dealing with as a matter of urgency.

 

[JMongi]

This is why my grand plan is to migrate to SQL server in the future. It just isn't practical for the development timeline.
@isladogs - do you have a link to adezii's code?

I think the WAN/WiFi discussion is what it is right now. Also, I wish I new what the ethernet issues are. It isn't flaky, until it is. It works fine and then will inexplicably need to be reset. Unfortunately, nothing I can do about that either. I guess the good news is that with a backup system in place and the expected usage rate, I don't think we'll lose much with a daily backup should something become corrupted.

 

[gemma-the-husky]

A few thoughts. I Hope this adds to the discussion

1. I've read various places about stability being improved by maintaining a connection the the BE at all times. Is this true? Is there a preferred method to do this? For reference I'm using a modified version of @isladogs login and session logging code/forms found here.

2. Are there any cleanups/checkups that would be good practice to do before the user quits the application?

3. I know that any type of network connection other than wired is increasing the risk of data corruption. There will be users that try to run this app over the WiFi here at work (which thankfully is almost as stable as the wired network) and some may stumble on the idea of running via VPN from their home. How can I manage this situation?

4. I know little about managing the life of an app after deployment. Any suggestions that will make my life easier in the future regarding app updates, error logging, etc would be much appreciated. As a reminder, I'm a self-taught programmer/developer with no formal training other than the school of experience.

1. If you don't maintain a permanent connection to a backend, then for a database that is in use by another user, Access has to negotiate a connection via the operating system. If you reconnect a database with say 20 tables, it might take 2 or 3 seconds to connect the tables. If the database is in use by someone else, then the 2 or 3 seconds can easily become 30 seconds or more. This then happens every time you need data from the backend, which is all the time. If you already have a connection then you don't get this re-negotation delay. So I have a dummy table, and I open a recordset to this table, which only closes as part of my logoff process. This dummy table is there for no other purpose.

2. Not particularly. Make sure you close any recordsets that you open, as soon as you finish with them, and don't forget to close them in error-handlers if your code falls down somewhere. Remember to close whatever you did to establish the permanent connection. For me it's just "close dummyrst"

3. If you work from home on a vpn, then loads of data has to squirt over the broadband to your home, and it can be painfully slow. Your LAN is possibly 20 times faster than even the fastest broadband, so everything works much slower. Try to design your databases to transfer as little data as possible. Instead of using a VPN, you can have a hosting server at work (a terminal server (TS)) and instead of connecting directly to the data, you run an image on the TS. Now the data is moving around the LAN, and all your connection via the vpn is doing is refreshing your screen. Instead of using a VPN at all, you would like to connect to a web or a cloud version of your database, but it turns out that Access can't do this easily (at all?) - so using this solution needs a different approach.

4. Split the database. Have plenty code backups (and data backups), but regularly take a copy of your code, and don't use the copy at all - it;s the master version so that when the in-use version of your code database develops a problem (which it will), you can use your last master copy to help you recover. Include error-handling for anything that might fail so your database is robust and resilient - all disk i/o needs error handling. Try to code in small segments. Use functions and subs, rather than spaghetti coding. Make an accde of your database, and release the accde so users can't make changes to your database. I think most of us are self taught to some degree. I think a lot of competence is hard to teach. What we need is a series of light-build moments to see the "aah - that's how it's done" revelations. The rest is just practice.

 

[JMongi]

Ok, bear with me, we are going in deep. Let's look at this from a more structured standpoint (for my own sanity).

1. Split the database into FE and BE
DONE

2. Create structures to handle future FE versioning
TBD (Likely @Pat Hartman suggestion .bat file for grabbing the latest FE every time).

3. Avoid WiFi logins
IN PROCESS (Warning splash screen for active WiFi connection; Flag/Log warnings in session logs.)

4. User Level Access Control
DONE (@isladogs code for RC4 password login)

5. Manage User Sessions
TBD (implement @isladogs Logged In User utility for maintenance)

6. Error Logging
IN PROCESS, Error handling is installed but my sub routine can be expanded to include better logging/tracking.

7.Crash Handling
TBD, This needs some work and I need to better understand what Access is doing. Active processes after app shutdown need cleaned up.

8.Persistent BE connection
TBD, Possibly in conjunction with session logging.

 

[The_Doc_Man]

Earlier, you asked WHY a Wi-Fi connection is contra-indicated for Access.

Basically, the protocol that Access uses, which is called Server Message Block or SMB, is a member of a protocol that uses message sequence numbers so that multi-part transmissions can be internally re-ordered if they have to be re-transmitted due to a network error. The message header includes this sequence number, a port number, and other elements to keep the network happy.

The problem with Wi_fi is that it commonly drops and re-connects. Oh, sure, that reconnect is usually very fast. The problem with that reconnect is that it reconnects through a different port number because the other port is open and waiting for traffic (that will never come). So you CAN'T just cold-connect to the network logical port (socket, if you prefer). The problem is that you now have TWO ports and you can never finish the transmission that was on the first port because the NEW connection started its sequence number over again. And THAT means that whatever was being sent is now irretrievably "broken" (in the network sense). The parts of the data being sent either way CANNOT be reassembled.

So, you ask, why is that so bad? Because if the transmission was setting up to transmit multiple buffers of data in an update, that update is now only partly complete and CAN'T be completed. This leads to DB corruption because of partial updates. Essentially, Wi-Fi leads you to make HALF of an update, which clearly can't be good.

Hope that helps you to understand the mechanism.

 

[JMongi]

@The_Doc_Man - Thanks for the technical explanation!

I guess I'm just failing to appreciate the difference in scope. By my understanding a wired network connection can have such small breaks too, thought far less frequently. I was always under the assumption that it was the bandwidth that was the issue, i.e. the faster that message block can be pushed through the less likely any disconnection will occur while a transmission is in process. I was also under the impression that modern WiFi protocols have their own layer abstractions to care for this in flight block to prevent it being cut in half by a network disconnect.

I definitely can see that using WiFi increases the likelihood of such transaction style issues and that the issue isn't rock solid WiFi it's using it at the edge of its range where it stops and starts all over the place. So, I guess I see the light!

 

[isladogs]

I can provide both adezii's WiFi check code and my modified version based on it...but both fail in the way I described. Is there any point forwarding it?

I have code that manages all aspects of crash handling including sending an email 'silently' to the program developer giving details of who/what/where/when/why...etc but its built into a commercial app so would require time (and money) to extract it.

A similar utility (minus the email) is available for free on Anders Ebro's website AKA TheSmileyCoder Access Crash Reporter – The Smiley Coder

 

[JMongi]

I can provide both adezii's WiFi check code and my modified version based on it...but both fail in the way I described.

I know I can't PREVENT connections, but I can detect them, warn the user and log the info. Also, our work environment is domain-join so I have a few more tricks up my sleeve to detect since it doesn't always like authenticating via two networks (LAN and WiFi).