X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Update Strategy, Access 2016    
post Mar 26 2020, 11:00 AM

UtterAccess VIP
Posts: 8,049
Joined: 24-May 10
From: Downeast Maine

Like so many people these days I am working from home, as are many of the people in my company who use Access databases.

First, I know Access is not a good choice for the BE across a VPN My intent for some while has been to move toward SQL Server as the BE. One reason is that we may not always have an on-site server. Another is that people wanted to use at least some of the databases remotely before the current troubles. I have converted some databases, and the drive to convert others has accelerated.

Brief description of the setup: There is a vbscript file that loads a sort of bootstrap Access database. The bootstrap database lists databases for which the logged on user has access.

When the user selects one of the listed databases from the bootstrap database, VBA code checks for a more recent version of the front end in the master location on the network. If there is a more recent version, it is copied to the local location in AppData. In any case, a command line launches the selected database. On a wired LAN this all happens in very little time.

The difficulty is that remote access to the local network is painfully slow, apparently due in part to much higher remote use volume, but always rather pokey. The action of checking for a newer master copy bogs things down, even when not using an Access BE. I have tested this by creating a version of the database that does not check for a more recent version. Performance is greatly improved. For now this improves things for remote users.

However, rolling out updates is still needed. My idea was that if a user is logged onto the wired LAN they would be using an IP address that always starts with the same first three groups of numbers except after the last dot. If so, proceed with checking for updates. If not, skip the check. I found code here that shows how to get the local IP address. I have tested it, and it is the same as the IPv4 address in ipconfig. Here is the code:
Function GetMyLocalIP() As String

    'Declaring the necessary variables.
    Dim strComputer     As String
    Dim objWMIService   As Object
    Dim colItems        As Object
    Dim objItem         As Object
    Dim myIPAddress     As String

    'Set the computer.
    strComputer = "."

    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

    'A select query is used to get a collection of IP addresses from the network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")

    'Loop through all the objects of the collection and return the first non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myIPAddress = Trim(objItem.IPAddress(0))
        Exit For

    'Return the IP string.
    GetMyLocalIP = myIPAddress

End Function

I have only a vague idea what I am seeing in the code, and cannot evaluate whether it is likely to work on other computer configurations.

To summarize:
Is this a reasonable approach, or is there a better way to check whether the user is on a specific wired LAN? BTW, if there is a necessary update to solve an error or something the user could have an option in the bootstrap database to get the latest version no matter what, but would have to specifically select the option before opening the database.

I am also interested in doing something similar with the vbscript that launches the bootstrap database. I have seen quite a few vbscript options, but would be interested in knowing if anybody has a suggestion. This may be a subject for a separate posting, but no harm in asking.

Go to the top of the page
post Yesterday, 06:48 PM

Posts: 2,808
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. This Code Segment will tell whether or not a User is connected to a Wired LAN.
  2. Required API Declaration:
    Public Declare Function InternetGetConnectedState Lib "wininet.dll" (lpdwFlags As Long, _
                            ByVal dwReserved As Long) As Boolean
  3. Function Definition:
    Public Function IsConnectedToLAN() As Boolean
    Dim Stat As Long

    'Local system uses a LAN to connect to the Internet.

    IsConnectedToLAN = (InternetGetConnectedState(Stat, 0&) <> 0)

    IsConnectedToLAN = (IsConnectedToLAN And INTERNET_CONNECTION_LAN)
    End Function
  4. Sample Function Call and OUTPUT (my PC):
    Debug.Print IsConnectedToLAN()


Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    29th March 2020 - 02:59 AM