Updating MS Access Database using WIFI over network (1 Viewer)

[sbrown106]

Hi,

Has anybody any advice as to how I might go about this please.

I work in an office with no local network, all the machine are laptops (apart from my desktop).
Ive created a database in MS Access that sits on my desktop. Ive split the database into front end and back end
and would like to put the front end on each laptop and Ive created a shared folder with permissions on my desktop
with the back end for people to use.

The part I am struggling with now because I'm not familiar with the network side is how to establish a connection from each laptop to my desktop using WiFi?

Do I need to create a new network using WiFi? because obviously I cant see anybody from my desktop at the moment because I'm not connected to anybody

Has anybody any experience with this?

Thanks for any advice

 

[Mike Krailo]

Don't use wifi. Use a wired LAN.

 

[isladogs]

Agree completely. Don't do it.
Trying to update Access data over a WiFi connection WILL lead to data corruption.
Access works well on a WIRED LAN. Not Wifi. Similarly do NOT put the BE in Dropbox or One Drive or similar

If none of that is possible, then move the BE to SQL Server as that is more resilient in case of breaks in wireless connection

 

[ADIGA88]

Regarding the wifi, I have been using wifi at my workplace for a year now (not recommend) but the no-no for me is to run an update query over the network, all update queries run on the backend directly and I am taking backups frequently in prep. for the dark day that I may encounter.

You need some knowledge to set up a shared folder because a lot of things can stop you from connecting to your backend.

What you need is the following:
1. all devices need to be on the same network (subnet) you probably are if you all using the same wifi access point (wifi and wire are the same network logic wise).
2. a static IP for your server (Backend) for stability.
https://www.thewindowsclub.com/set-a-static-ip-address-in-windows-10
3. a shared folder on your server to put your backend file in it (better to create local windows user accounts for each database user on the server for access control) using everyone user permission.
https://support.microsoft.com/en-us...ndows-10-b58704b2-f53a-4b82-7bc1-80f9994725bf
4. Mapped Network folder on your client/frontend devices directing to the server hostname/static IP address.
https://support.microsoft.com/en-us/windows/map-a-network-drive-in-windows-10-29ce55d1-34e3-a7e2-4801-131475f9557d

 

[isladogs]

Whilst the above may reduce the chances of corruption, it will not prevent it.
Backups are indeed an essential precaution

Corruption may occur at any time that a wifi connection is interrupted (even briefly) whilst data is being written / edited.
Update queries are certainly a risk but so are all other types of action query or SQL equivalent as well as any manual editing of records.

The problem is that you may not immediately realise that the connection was broken or that there were any data issues

 

[The_Doc_Man]

You've gotten other opinions on this. I'm going to add one more.

Access over a WiFi network is questionable at best and totally unstable - to the point of unusable - at worst. There is a loophole (of sorts) IF AND ONLY IF all users other than the "owner" of the back-end are read-only. You would still lose connection, but since the BE in this exceptional case is not being updated, there is a chance that it would work. However, from your original description, I'm going to bet that the loophole does not apply to what you want to do.

Since time is money and you WILL be spending time fixing "gotcha" events with WiFi, the next question is, how far apart will those laptops be? These days, Ethernet can be up to 100 meters (328 feet). If you can get an Ethernet switch, a decent 16-port switch is in the $50-$60 range and a 24-port switch would be $70-$90. Then, of course, some long cables.

You didn't say how many laptops. If you only have six (counting yourself) it is cheaper.

Amazon.com : 6 port switch

Amazon.com: TP-Link TL-SG108 | 8 Port Gigabit Unmanaged Ethernet Network Switch, Ethernet Splitter | Plug & Play | Fanless Metal Design | Shielded Ports | Traffic Optimization | Limited Lifetime Protection : TP-LINK: Electronics

Amazon.com: TP-Link TL-SG108 | 8 Port Gigabit Unmanaged Ethernet Network Switch, Ethernet Splitter | Plug & Play | Fanless Metal Design | Shielded Ports | Traffic Optimization | Limited Lifetime Protection : TP-LINK: Electronics

www.amazon.com

You can set up an unmanaged switch inside your office for the cost of the switch and some cables. You have to balance this against the cost of having to repair your database frequently based on how much you make per hour. My best guess is that you would break even by the time you had no more than four back-end "whoops" events that required fixing. Four network outages and the switch + cables will probably pay for themselves. Since this is an unmanaged switch, two things have to be assured.

1. Never let the big-I Internet on the switch. It has to be local-only.
2. All of the ethernet ports on the laptops must be set to non-DHCP and must have unique (fixed) addresses with the same class B or class C prefix.

 

[Pat Hartman]

Doc, does #1 mean that none of the laptops should connect to the internet at all? That means no email also. If only the "server" is sharing a folder, can hackers still "see" the folders on the laptops to break in? Does partitioning the laptops solve this problem if you can log onto the internet from partition A and log onto the network from partition B? That could be awkward but maybe safe enough.

@sbrown106 , The expert consensus is unanimous. WiFi is too dangerous with a Jet/ACE BE unless you are working from paper data and take multiple BE backups during the day. Your people would need to redo anything that they had done since the previous backup Hence the need for having everything on paper.

As the others also mentioned, SQL Server as the BE is much safer. Doesn't mean you won't have a problem but it will be limited to whatever was in process and not yet saved..

 

[The_Doc_Man]

On an unmanaged network, that is exactly what it means. If you want a more secure network that can be closed off with a firewall and a network management sub-system, you need a more expensive device. I was pointing out that a low-end device could solve the small-office problem. If the OP needs "big office" network abilities, that is when the big bucks start rolling in. The old rule is still true: You get what you pay for.

 

[Pat Hartman]

I understand that what you suggested wasn't really a recommendation due to its lack of security. I just haven't set up a network for anyone since the 80's so a lot has changed. When networking first became possible, I was working for a large manufacturing company that made a lot of stuff for the army and we set up at least a dozen closed networks in various engineering departments over the first few years that personal computers started replacing CRT's. I was gone before it came time to wire the entire plant so that was my only experience up close and personal with setting up a LAN.

 

[sbrown106]

Doc, does #1 mean that none of the laptops should connect to the internet at all? That means no email also. If only the "server" is sharing a folder, can hackers still "see" the folders on the laptops to break in? Does partitioning the laptops solve this problem if you can log onto the internet from partition A and log onto the network from partition B? That could be awkward but maybe safe enough.

@sbrown106 , The expert consensus is unanimous. WiFi is too dangerous with a Jet/ACE BE unless you are working from paper data and take multiple BE backups during the day. Your people would need to redo anything that they had done since the previous backup Hence the need for having everything on paper.

As the others also mentioned, SQL Server as the BE is much safer. Doesn't mean you won't have a problem but it will be limited to whatever was in process and not yet saved..

Yes thanks for that. The WiFi is unstable here Ive discovered - the connection seems unpredictable. So Ive been asked to look at Amazon. From what Ive seen Amazon provide an RDS instance ( allows the user to create an environment for a relational database), however it doesnt seem completely compatible with Access so I think I would need to redesign the front end. So now I'm looking at creating an EC2 instance using Amazon AWS. This allows the user to create a virtual machine (Windows 10) - so I am going to see if its possible for this to store the backend. You guys will know a lot more about this than me so if you have any suggestions let me know. Out of interest would you expect that I would need to install Access on this virtual windows server - or when I split the database can the backend just be placed in a folder ? is the backend just basically a file or will it need Access to operate?
Hope that makes sense. I think the company wants the database in the cloud in case people need to work from home again with COVID

 

[Pat Hartman]

Remote desktop is compatible with Access. I've used it a number of times although not with AWS. They need to create a "shared" drive for you that all the users connect to when they open their RD. In addition, the shared drive needs to keep the backup cop of the FE so that each user's start up code copies the master copy of the FE to their personal drive and runs it from there.

VERY IMPORTANT - when using Citrix or RD, the user must NOT connect to a shared FE. Only the BE is shared. Just as with a LAN installation, the FE MUST run from each user's personal "desktop"

 

[RogerCooper]

My company recently moved and for 2 weeks we were relying on WiFi, using an SQL server backend. There was no corruption issue, but speed was a problem.

 

[The_Doc_Man]

is the backend just basically a file or will it need Access to operate?

Addressing the narrow question, the back-end needs to support Server Message Block (SMB) protocol, which is the basis of Windows File Sharing (and Printer Sharing). You are correct that the BE, if still native Access, is just a file. But Access will want to access a PART of that file at a time using SMB methods.

Pat's suggestion - Remote Desktop Service - would solve lots of problems. The only danger is the configuration, which she has clearly explained.

 

[Pat Hartman]

There was no corruption issue, but speed was a problem.

You were lucky. The corruption is all about timing. If Access is in the middle of saving data when the blip happens, you're toast. If the form is dirty, you just loose what you typed. If the form is not dirty, no harm. You just have to close and reopen Access because it cannot reconnect gracefully by itself.

 

[sbrown106]

Remote desktop is compatible with Access. I've used it a number of times although not with AWS. They need to create a "shared" drive for you that all the users connect to when they open their RD. In addition, the shared drive needs to keep the backup cop of the FE so that each user's start up code copies the master copy of the FE to their personal drive and runs it from there.

VERY IMPORTANT - when using Citrix or RD, the user must NOT connect to a shared FE. Only the BE is shared. Just as with a LAN installation, the FE MUST run from each user's personal "desktop"

Hi Pat, I'm not sure I understand completely when you say shared drive, let me explain what Ive done so far and that might help me to understand.

I have created a VM in AWS, Amazon give you many options and provide different platforms/services. For the database Ive created a Virtual Windows 10 Machine with 1GB of Ram, its free for a while so you can try stuff out. The database is only small, maybe 5000 or so records in 4/5 tables, 6 users. Ive split it and put the back end on the VM

On this VM machine Ive created a shared folder with the database backend in it (Access isnt installed), and on each users laptop ive placed the front end and re-linked the tables by using the address of the shared folder. It seems to work at the moment but can be a bit slow

Could you explain further your thinking please so I can understand what I need to change?

 

[sbrown106]

You've gotten other opinions on this. I'm going to add one more.

Access over a WiFi network is questionable at best and totally unstable - to the point of unusable - at worst. There is a loophole (of sorts) IF AND ONLY IF all users other than the "owner" of the back-end are read-only. You would still lose connection, but since the BE in this exceptional case is not being updated, there is a chance that it would work. However, from your original description, I'm going to bet that the loophole does not apply to what you want to do.

Since time is money and you WILL be spending time fixing "gotcha" events with WiFi, the next question is, how far apart will those laptops be? These days, Ethernet can be up to 100 meters (328 feet). If you can get an Ethernet switch, a decent 16-port switch is in the $50-$60 range and a 24-port switch would be $70-$90. Then, of course, some long cables.

You didn't say how many laptops. If you only have six (counting yourself) it is cheaper.

Amazon.com : 6 port switch

Amazon.com: TP-Link TL-SG108 | 8 Port Gigabit Unmanaged Ethernet Network Switch, Ethernet Splitter | Plug & Play | Fanless Metal Design | Shielded Ports | Traffic Optimization | Limited Lifetime Protection : TP-LINK: Electronics

Amazon.com: TP-Link TL-SG108 | 8 Port Gigabit Unmanaged Ethernet Network Switch, Ethernet Splitter | Plug & Play | Fanless Metal Design | Shielded Ports | Traffic Optimization | Limited Lifetime Protection : TP-LINK: Electronics

www.amazon.com

You can set up an unmanaged switch inside your office for the cost of the switch and some cables. You have to balance this against the cost of having to repair your database frequently based on how much you make per hour. My best guess is that you would break even by the time you had no more than four back-end "whoops" events that required fixing. Four network outages and the switch + cables will probably pay for themselves. Since this is an unmanaged switch, two things have to be assured.

1. Never let the big-I Internet on the switch. It has to be local-only.
2. All of the ethernet ports on the laptops must be set to non-DHCP and must have unique (fixed) addresses with the same class B or class C prefix.

Hi The_Doc_Man, thanks for that reply. I'm getting a bit confused over this WiFi data corruption business. So if using products like Caspio , are you suggesting a LAN and router or to just have the database in-house and not connected to the internet at all?
How is it that the data is corrupted when using database over WI-FI, is this just for large databases - the database size at the moment about 20MB?
The company I work for is insistent that people can connect remotely to update the database - so would a VPN to server on a LAN network be a compromise?

Sorry for all these questions - I'm in the process of putting together a list of things they need to purchase and trying to argue a case for a LAN - There are 6 laptops and one desktop all connected via WIfI. The database has only been up and running for a week and the company has just purchased one of those Starlink (Sat) things to improve internet connection.

(So with that shopping list you suggested above, for a LAN I need, 6users+1desktop

Amazon.com : 6 port switch

Amazon.com: TP-Link TL-SG108 | 8 Port Gigabit Unmanaged Ethernet Network Switch, Ethernet Splitter | Plug & Play | Fanless Metal Design | Shielded Ports | Traffic Optimization | Limited Lifetime Protection : TP-LINK: Electronics

Amazon.com: TP-Link TL-SG108 | 8 Port Gigabit Unmanaged Ethernet Network Switch, Ethernet Splitter | Plug & Play | Fanless Metal Design | Shielded Ports | Traffic Optimization | Limited Lifetime Protection : TP-LINK: Electronics

www.amazon.com

Have you a suggestion for a VPN? or would you advise against?)

Many thanks for your help and guidance on this matter

 

[JMongi]

You might want to check out this thread for more information.

I was confused as well because we don't normally think about WiFI as being unstable. It turns out that what is "stable" and "unstable" is really dependent upon the software using that network. To use a terrible analogy....

We need to eat. We need a "stable" source of food. Let's say your only source of food was a buffet. Sometimes, due to many eaters, various food on the buffet aren't available when we walk up to it. No worries, right? We'll just come back later. For most humans this would suffice and we would call the buffet a stable source of food. But, what if you absolutely needed to eat every 2 minutes or you would die. Now, you walk up to the buffet and the five minute weight for the food refill is no longer sufficient. The buffet can no longer be consider a "stable" source of food for you.

It's the same with Access running on WiFi. We view WiFi as stable (aside from major hiccups) because we aren't even aware of when signal is lost and then regained because usually, it doesn't matter. From that standpoint, WiFI IS stable. Most software chugs along without a care. Due to its nature, if Access is transfering data at the same time as even an extremely minor signal blip, there is a very real chance that corruption (incomplete or incorrect digital data) can occur. These errors accumulate and can affect the validity of the data itself or even the ability to retrieve that data. For Access, a WiFi connection is NOT stable.

 

[sbrown106]

You might want to check out this thread for more information.

I was confused as well because we don't normally think about WiFI as being unstable. It turns out that what is "stable" and "unstable" is really dependent upon the software using that network. To use a terrible analogy....

We need to eat. We need a "stable" source of food. Let's say your only source of food was a buffet. Sometimes, due to many eaters, various food on the buffet aren't available when we walk up to it. No worries, right? We'll just come back later. For most humans this would suffice and we would call the buffet a stable source of food. But, what if you absolutely needed to eat every 2 minutes or you would die. Now, you walk up to the buffet and the five minute weight for the food refill is no longer sufficient. The buffet can no longer be consider a "stable" source of food for you.

It's the same with Access running on WiFi. We view WiFi as stable (aside from major hiccups) because we aren't even aware of when signal is lost and then regained because usually, it doesn't matter. From that standpoint, WiFI IS stable. Most software chugs along without a care. Due to its nature, if Access is transfering data at the same time as even an extremely minor signal blip, there is a very real chance that corruption (incomplete or incorrect digital data) can occur. These errors accumulate and can affect the validity of the data itself or even the ability to retrieve that data. For Access, a WiFi connection is NOT stable.

Thanks for that JMongi - so if the DB is on a LAN and a user wants to connect from another location via VPN does that provide the same risk?
Sorry if these questions are basic. I would like a LAN here but with covid the company might need remote access to the DB, I'm not an expert in the technology side - so thanks for any further advice

 

[JMongi]

It can be better depending on your VPN and your setup. There are some on this board that dealt with the challenges (due to COVID). Hopefully, they can chime in with some experience. I'm in a similar situation. We have a local domain, file server and a 3rd party IT company that manages the server, exchange e-mail and the like. Other than that it's kind of the wild-wild-west. Some of the users likely to use my database have laptops that CAN be wired to our network but often are just left on WiFi; used at home through VPN, etc.

If you have buy in up front and a genuine need for WiFi/remote connectivity I would recommend investigating two possibilities to see what works best for your company.
1. Some type of Remote Desktop based implementation. This involves a server, configuring RD and is not super simple. But, it is definitely doable. Further investigation here.
2. Consider developing the database using Access (it's really good at this) but use SQL Server as your backend. This involves cost and technical expertise as well but is also doable with plenty of experience on this board.

 

[sbrown106]

It can be better depending on your VPN and your setup. There are some on this board that dealt with the challenges (due to COVID). Hopefully, they can chime in with some experience. I'm in a similar situation. We have a local domain, file server and a 3rd party IT company that manages the server, exchange e-mail and the like. Other than that it's kind of the wild-wild-west. Some of the users likely to use my database have laptops that CAN be wired to our network but often are just left on WiFi; used at home through VPN, etc.

If you have buy in up front and a genuine need for WiFi/remote connectivity I would recommend investigating two possibilities to see what works best for your company.
1. Some type of Remote Desktop based implementation. This involves a server, configuring RD and is not super simple. But, it is definitely doable. Further investigation here.
2. Consider developing the database using Access (it's really good at this) but use SQL Server as your backend. This involves cost and technical expertise as well but is also doable with plenty of experience on this board.

Thanks again - I'll research into how to go about point 2.
Just one last thing if the DB is on a LAN, Ive written some stuff in VBA for emails inside the DB does that mean I wont be able to send email from the DB?