WiFi or Wired?

[Gaztech]

Hi guys,

I have a situation at work whereby I have an Access stock database that has multiple access for 5 users.

The way it is set up with a split database works just fine with no hiccups. However, we have a problem with our laptop users.

Basically, they are too lazy to connect to our system using the ethernet cable on the desk and often only connect via WiFi. I do NOT want them to do this if they are going to use the database. No matter how many times I tell them NOT to use it on WiFi they still do it. So, I want to find a way to have the program detect that WiFi is in use.

If I can do that, I can pop up a box to tell them they can't do this and then exit the program.

Does this sound like something that is possible?

 

[Minty]

There has been a lot of discussion about this, the thread below is a good starting point.

Solved - Confirm LAN connection.

Hi, i'm looking for a way of determining whether an access db is being accessed via LAN or WiFi. This seems to be a perrenial question. I downloaded Daniel Pinaults solution, but it always returns 'true', LAN or WiFi connected. I've been Googling, but the more i read, the more confused i become...

www.access-programmers.co.uk

I'm not sure you can categorically work it out,

 

[Gaztech]

Wow. I thought it would be simple.

That's a big can of worms.... !

Thanks for the info. I'll certainly try out all the suggestions to see where it goes.

As far as I know, on all the laptops I've used, if a "wired" connection is present then all calls go there and not to WiFi. Maybe we can just detect the wired LAN connection only... It does rely on the fact that you are connected to the correct resource but if you're not, then Access would fail anyway.

I'm now going to experiment with it...

Cheers guys!

 

[isladogs]

Unfortunately not all laptops behave the same way as you will have seen by reading the linked thread.
After several hours of tests with different code, I've reluctantly concluded it is not possible to RELIABLY detect when WiFi is in use on a workstation which also has an Ethernet connection

 

[Gaztech]

Yes. You're right.

I just tried 3 laptops and they all behave differently. Exactly why is a mystery right now but I'm thinking that it might be down to what the adapter is named in "Network and Internet Settings". Using netsh to enable or disable an adapter, you really do need to get the name right. If you don't, the command doesn't work.

Many WiFi adapters are named just "WiFi" so I'm going to investigate. If this is the problem then at least we'll know...

 

[Gasman]

You *could* find the wifi adapter and disable it.?
Re-enable it when you exit access.?

I have to reboot my network adapter sometimes, as when I boot the computer it does not connect.
So I just created a batch file to do it from a shortcut, that disables then enables the adapter, then I am back on line.

Of course you will need to identify the adapter.

 

[Gaztech]

I do the same thing! I have two button shortcuts on my Taskbar - one for Enable and one for Disable. These use netsh and the adapter name to perform the relevant functions. I'm sure we can go down this route somehow...

 

[Gasman]

Well I have discovered some code from https://stackoverflow.com/questions...less-network-name-connected/36414339#36414339 which appears to identify my wifi network and returns nothing when I disconnect.?

So you could see if that returns a string, then display 'This application will not work on wifi' message.?

HTH

Option Compare Database
Option Explicit

Private Const DOT11_SSID_MAX_LENGTH As Long = 32
Private Const WLAN_MAX_PHY_TYPE_NUMBER As Long = 8
Private Const WLAN_AVAILABLE_NETWORK_CONNECTED As Long = 1
Private Const WLAN_AVAILABLE_NETWORK_HAS_PROFILE As Long = 2

Private Type GUID
    data1 As Long
    data2 As Integer
    data3 As Integer
    data4(7) As Byte
End Type

Private Type WLAN_INTERFACE_INFO
    ifGuid As GUID
    InterfaceDescription(255) As Byte
    IsState As Long
End Type

Private Type DOT11_SSID
    uSSIDLength As Long
    ucSSID(DOT11_SSID_MAX_LENGTH - 1) As Byte
End Type

Private Type WLAN_AVAILABLE_NETWORK
    strProfileName(511) As Byte
    dot11Ssid As DOT11_SSID
    dot11BssType As Long
    uNumberOfBssids As Long
    bNetworkConnectable As Long
    wlanNotConnectableReason As Long
    uNumberOfPhyTypes As Long
    dot11PhyTypes(WLAN_MAX_PHY_TYPE_NUMBER - 1) As Long
    bMorePhyTypes As Long
    wlanSignalQuality As Long
    bSEcurityEnabled As Long
    dot11DefaultAuthAlgorithm As Long
    dot11DefaultCipherAlgorithm As Long
    dwflags As Long
    dwreserved As Long
End Type

Private Type WLAN_INTERFACE_INFO_LIST
    dwNumberOfItems As Long
    dwIndex As Long
    InterfaceInfo As WLAN_INTERFACE_INFO
End Type

Private Type WLAN_AVAILABLE_NETWORK_LIST
    dwNumberOfItems As Long
    dwIndex As Long
    Network As WLAN_AVAILABLE_NETWORK
End Type

Private Declare Function WlanOpenHandle Lib "wlanapi.dll" (ByVal dwClientVersion As Long, _
                ByVal pdwReserved As Long, _
                ByRef pdwNegotiaitedVersion As Long, _
                ByRef phClientHandle As Long) As Long

Private Declare Function WlanEnumInterfaces Lib "wlanapi.dll" (ByVal hClientHandle As Long, _
                ByVal pReserved As Long, _
                ppInterfaceList As Long) As Long

Private Declare Function WlanGetAvailableNetworkList Lib "wlanapi.dll" (ByVal hClientHandle As Long, _
                pInterfaceGuid As GUID, _
                ByVal dwflags As Long, _
                ByVal pReserved As Long, _
                ppAvailableNetworkList As Long) As Long

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, _
                Source As Any, _
                ByVal Length As Long)

Private Declare Sub WlanFreeMemory Lib "wlanapi.dll" (ByVal pMemory As Long)

Public Function GetConnectedSSID() As String
    Dim lngReturn As Long
    Dim lngHandle As Long
    Dim lngVersion As Long
    Dim lngList As Long
    Dim lngAvailable As Long
    Dim lngStart As Long
    Dim intCount As Integer
    Dim strSSID As String
    Dim strProfile As String
    Dim udtList As WLAN_INTERFACE_INFO_LIST
    Dim udtAvailableList As WLAN_AVAILABLE_NETWORK_LIST
    Dim udtNetwork As WLAN_AVAILABLE_NETWORK
    '
    ' Get a Handle
    '
    lngReturn = WlanOpenHandle(2&, 0&, lngVersion, lngHandle)
    If lngReturn = 0 Then
        '
        ' Enumerate the Interfaces
        ' (Note: this code only looks at the first interface)
        '
        lngReturn = WlanEnumInterfaces(ByVal lngHandle, 0&, lngList)
        CopyMemory udtList, ByVal lngList, Len(udtList)
        '
        ' Get the list of available Networks
        '
        lngReturn = WlanGetAvailableNetworkList(lngHandle, udtList.InterfaceInfo.ifGuid, 2&, 0&, lngAvailable)
        CopyMemory udtAvailableList, ByVal lngAvailable, LenB(udtAvailableList)
        intCount = 0
        lngStart = lngAvailable + 8
        Do
            '
            ' Populate the Available network structure
            '
            CopyMemory udtNetwork, ByVal lngStart, Len(udtNetwork)
            '
            ' Display the Data for this Network
            '
            strProfile = ByteToString(udtNetwork.strProfileName)
            strProfile = Left$(strProfile, InStr(strProfile, Chr(0)) - 1)
            strSSID = ByteToString(udtNetwork.dot11Ssid.ucSSID, udtNetwork.dot11Ssid.uSSIDLength, False)
            strSSID = Left(strSSID, InStr(strSSID, Chr(0)) - 1)
            If (udtNetwork.dwflags And WLAN_AVAILABLE_NETWORK_CONNECTED) = WLAN_AVAILABLE_NETWORK_CONNECTED Then
                'Debug.Print "Profile "; strProfile, "SSID "; strSSID, "Connected "; udtNetwork.dwflags
                GetConnectedSSID = strSSID
            End If
            intCount = intCount + 1
            lngStart = lngStart + Len(udtNetwork)
            '
            ' Process all available networks
            '
        Loop Until intCount = udtAvailableList.dwNumberOfItems
        WlanFreeMemory lngAvailable
        WlanFreeMemory lngList
    End If
End Function

Private Function ByteToString(bytArray() As Byte, Optional lngLen As Long = 0, Optional boConvert As Boolean = True) As String
    Dim strTemp As String
    Dim intI As Integer
    Dim intEnd As Integer
    If lngLen = 0 Then
        intEnd = UBound(bytArray)
    Else
        intEnd = lngLen
    End If
    For intI = 0 To intEnd
        strTemp = strTemp & Chr(bytArray(intI))
    Next intI
    If boConvert = True Then strTemp = StrConv(strTemp, vbFromUnicode)
    ByteToString = strTemp
End Function

 

[Gaztech]

Ok,

So.... I have a simple but dirty way to solve the problem. It's a bit like a hammer to crack a nut but it works fine and doesn't involve coding up loads of stuff. IF you have several Access routines on the machine you can use this for all of them with no extra coding.

I needed to find a 100% foolproof way to deal with this as I just knew that users would abuse the system if I didn't do it.

It revolves around psexec. Now part of Microsoft's tools, the PsTools Suite is a very useful set of tools to do stuff like this. PsExec.exe can perform system tasks so I decided to investigate.

First of all I created a new user on the machine with admin rights.

I downloaded and installed the PsTools executables into C:\Pstools and then called psexec from a batch file to turn OFF the Wifi adapter when the user launches Access using the new user with the required rights. A simple "call" instruction holds the batch file in memory until the user exits Access and the rest of the code in the batch then runs.

A simple shortcut on the desktop launches the required routine. Although it's not elegant at all (no, really - it's not) it works just fine every time.

I put the Access Frontend file into folder "C:\Access_Frontend" and called the file "Access_Frontend.accdb".

Just create a batch file called "Access_Launch.bat" (doesn't matter where as you can hide it if you want - or even convert it to an exe if you want to get picky about security) then link a shortcut to it (add a nice icon) and that's it.

Batch file looks like this:

...........................

rem WiFi-Disable batch file with Elevated Rights

rem Change to PSTools Folder
C:
cd\
cd PsTools

rem Disable Wifi - "WiFi" is the NAME of the WiFi adapter
psexec -u USERNAME -p PASSWORD netsh interface set interface name="WiFi" admin=disabled

rem Now change to the Access application folder
C:
CD\
CD\Access_Frontend

rem call the Access application
call Access_Frontend.accdb

rem Batch now waits until you're done
@echo Re-Enabling WiFi

rem Re-Enable Wifi

rem Change to the PsTools folder
C:
cd\
cd PsTools

rem RE-Enable the WiFi
psexec -u USERNAME -p PASSWORD netsh interface set interface name="WiFi" admin=enabled

rem Set Dynamic Addressing on the Interface - Might not be necessary but best to do it anyway
psexec -u USERNAME -p PASSWORD netsh interface ip set address "WiFi" dhcp

rem Exit the routine - Back to Normal!
exit

...........................

I like simple solutions. No need to get complicated and unless the user is hell bent on circumventing what we're doing here (by re-enabling the Wifi manually), it should ensure that they NEVER use the database on WiFi. No need to even detect it. If the Wifi is already off, the routine does nothing, although it does enable it when the routine exits but even that doesn't really matter as if you're connected on cable it's ignored anyway as the wired connection almost always takes priority.

I tried this method on all my laptops. With a slight change on one of them to rename the interface name in the batch file (it was named differently), the routine works on all of them without incident.

Could be useful for someone I suppose...

 

[Gasman]

Nice, but I think if I had to do that, I'd do it all from within Access, so someone like me does not open the DB directly?
You could then not switch it on, if you did not find it when you went to switch it off.?
In the DB I wrote for the bank, I even checked if the file was an accde, and in the correct folder else exited the DB, in case someone decided to find and run the accdb version.

Do let us know how it goes anyway.

 

[Gaztech]

Nice, but I think if I had to do that, I'd do it all from within Access, so someone like me does not open the DB directly?
You could then not switch it on, if you did not find it when you went to switch it off.?
In the DB I wrote for the bank, I even checked if the file was an accde, and in the correct folder else exited the DB, in case someone decided to find and run the accdb version.

Do let us know how it goes anyway.

Yeah sure - they could open it directly but if they won't turn off the WiFi as they are lazy they certainly won't be bothered to look for the file and run it not using the shortcut!

However, that remains to be seen I appreciate the input. We'll see how it goes.

 

[Isaac]

Great job coming up with a workaround. I'm glad to hear it's working for you - always a great feeling when you solve a problem for your users/db!

I've also taken to using VBScript (or you can use bat files) to open databases, in some cases, so can relate to the nice feeling of extra options it gives you.

 

[The_Doc_Man]

If in fact the interface name IS "WiFi" this should work as long as your script is already on the FE machines. HOWEVER, if this were triggered as a remotely located batch job (i.e. user triggers a remote copy of this .BAT file from the local machine) then the problem is that a user EITHER would get a failure (because the WiFi interface is in use and can't be turned off at the moment) OR the user would get an ugly process crash (or worse) when an active network connection dropped out from underneath the session.

 

[Isaac]

You could always switch to VBScript and include message boxes telling the user what was going on..but that would come with its own drawbacks.

 

[Gaztech]

Yes Doc_Man - you are correct of course.

However, in my situation the users will never need to access this remotely. They only need it when they come back in to the office as it's a warehousing program.

As I said previously, I did have to change the name in the batch on one of the machines as the interface name wasn't "WiFi" but it's an easy thing to do and only has to be done once on the target machine.

It does involve a little bit of setup initially on the laptop but once done I can forget about it.

I had a couple of users using the db yesterday. Despite telling everyone all about this beforehand, the first thing one of them did was NOT connect the RJ45 cable and then complained to me that the db didn't work!

Haha... hammer to crack a nut? - Yes but now I've proved that it does do the job. Now I can go back to doing some more productive stuff.

 

[gemma-the-husky]

Have you actually had any issues caused by using Wifi? - If everything is stable it may not be ideal, but you might never have an issue. Even if you do, you will probably only need to do a C&R of the back end.

 

[Gaztech]

Actually, no. I haven't. I always connect my development laptop on WiFi (despite telling others not to!).

I don't really expect any issues either. Going forward, I don't want to have to recover things that could have been avoided.

This was more of a "belt and braces" approach as we have the backend on a NAS drive, not a server.

 

[isladogs]

Upsizing the BE to SQL Server or similar will make it much more resilient to any WiFi interruptions

 

[isladogs]

I had a couple of users using the db yesterday. Despite telling everyone all about this beforehand, the first thing one of them did was NOT connect the RJ45 cable and then complained to me that the db didn't work!

Haha... hammer to crack a nut? - Yes but now I've proved that it does do the job. Now I can go back to doing some more productive stuff.

Just a thought. If someone opens it with wireless disabled AND THEN re-enables WiFi, what will happen?

BTW - not all wireless connectors are identified as WiFi. One of my PCs its Wi-Fi and on the other Wireless
If your code doesn't allow for those, I would suggest tweaking it to "Wi*Fi" Or "Wireless"

Personally, I would feel more confident if that code could be run from Access at startup rather than being a separate script

 

[Jbooker]

Depending how critical your database is and no matter how robust your network infra, I would't even recommend running Access even over ethernet. Beyond a couple users with intermitent concurrency is asking for trouble, in my experience.

All multi-user Access apps having Access back-end that I deploy are on RDS servers with linked tables on local drives. All of them - from 5 to dozens of concurrent users.

Not only will your database be safe from certain corruption, it solves the transient laptop on wi-fi, remote support and work from home scenarios as well.