UtterAccess.com
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    
 
   
BruceM
post Mar 26 2020, 11:00 AM
Post#1


UtterAccess VIP
Posts: 8,110
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:
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
    Next

    '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
 
ADezii
post Mar 28 2020, 06:48 PM
Post#2



Posts: 2,994
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:
    CODE
    Public Declare Function InternetGetConnectedState Lib "wininet.dll" (lpdwFlags As Long, _
                            ByVal dwReserved As Long) As Boolean
  3. Function Definition:
    CODE
    Public Function IsConnectedToLAN() As Boolean
    Dim Stat As Long

    'Local system uses a LAN to connect to the Internet.
    Const INTERNET_CONNECTION_LAN As Long = &H2

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

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

    CODE
    True

Go to the top of the page
 
BruceM
post Mar 30 2020, 07:49 AM
Post#3


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


Thanks, but the user could be connected to a wired LAN off site (their home network, for instance). That's why I need to check about a specific LAN.

The problem is that a remote connection gets processed through an overburdened corporate IT process that mean it often takes thirty seconds or more just to move or copy a 200 KB file. If I need to copy a 2 MB file I may as well get a cup of coffee, especially later in the day when more users come on line. I am on the east coast, so first thing in the day it's not too terrible.

BTW, the method I described seems to be working, but I have not had an opportunity to test on other setups yet.
Go to the top of the page
 
cheekybuddha
post Mar 30 2020, 08:01 AM
Post#4


UtterAccess Moderator
Posts: 12,814
Joined: 6-December 03
From: Telegraph Hill


Seems like you're gambling on home networks using default subnets with IP's like 192.168.1.XXX rather than something like 10.10.1.XXX on a corporate LAN.

How about a slightly different approach - just thinking out loud:

Keep a log of latest db versions in your SQL Server. You can have a table with the db name, latest update date and a blob of the actual file.

Then query the table from your bootstrapper and download the blob if required. It will however require you to maintain the table, but avoids polling the network filesystem to check for new updates.

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Mar 30 2020, 08:53 AM
Post#5


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


Actually, I'm figuring that the LAN at work will have a specific default gateway (123.45.67.1), since it has had the same one all along, as far as I can recall. If the first three blocks of numbers are other than 123.45.67, it is a remote connection.I am storing the information 123.45.67 in a table of local information, so if it changes it will be a simple matter to change it.

My actual code parses the IP address for the first three number blocks. I should have mentioned that, but the main drift of my question is whether it looks like this code will be reliable in other configurations.

I had considered (and am still considering) a table such as you mention. However, updates are often for an added feature or an improved report or avoiding an annoying but harmless error. Users may prefer to work around the issue until they are on site, or maybe they will get the update and then take a break or get lunch or something. My idea was that if a user knows they need an update they can choose not to bypass the update process.

Still working on the details. Working remotely for more than an hour or two, such as I used to do when travelling, is a new thing. Your input is very much appreciated.
Go to the top of the page
 
cheekybuddha
post Mar 30 2020, 09:00 AM
Post#6


UtterAccess Moderator
Posts: 12,814
Joined: 6-December 03
From: Telegraph Hill


I get your strategy, Bruce. thumbup.gif It should be dine until some clever user has set up their home LAN on 123.45.67.XXX !

I just wondered whether the connection to the SQLServer was faster than the VPN filesystem and downloading an update might be quicker.

If your db's connect to the SQLServer through the VPN then perhaps it will be likely just as slow, but if you connect to the server directly then maybe it will lose the overhead of the VPN.

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Mar 30 2020, 09:14 PM
Post#7


UtterAccess VIP
Posts: 3,065
Joined: 12-April 07
From: Edmonton, Alberta Canada


Actually, for the company folders etc.? (including linking to a acess back end).?

As a general rule, very often the access BE was on a shared server folder. Standard fair like this:


\\SERVER-01\Database\MyData.accdb.


If your users are going to say be on a VPN? Well, then often you have use of of the network, but your client side often will NOT resolve by the server name.

so, simply swap out the server name for the server ID address.

eg:
\\10.50.100.101\Database\Mydata.accDB

Now, if you are on-site, or off site - but connected by a VPN, then both at work, and at home users should have no trouble using and connecting to that back end.

In other words, all of your "other" update code, or some kind of auto FE update etc. should continue to work.

You "might" have to relax the rights to the above folder, but in most cases I find that if you are logged in on the VPN, then the rights of the domain logon tend to still exist and still work.

So by adopting a IP address (as opposed to a UNC path), then everything should continue to work - and that would include on-site and off site users.

I don't think any of the other code should have to change, and by using IP address for the server shared folders, then no change in much of anything should have to occur here.

The only downside as you note is that VPN's tend to be slow - and that's simply because extending the windows file networking system over the internet is slow - and you not really find much of a solution to that issue/problem. The "overhead" is really due to that you part of a network, and that needs and uses windows networking. Windows networking (in fact most systems) were never designed to be used "over" the internet. Since Access BE's are "file" based, then that also tends to be why they don't work well over a VPN.

Even SQL server can be quite slow - we had some applications that are running SQL server. But over a VPN, they were too slow - we thus had to use remote desktop (terminal services).

The simple matter is that the VPN is not really causing much overhead. The issue is that windows networking extended OVER the internet is the problem, and that your internet connection speed is anywhere from 10-50 times slower then even the most el-cheapo local area network.
R
Albert

Go to the top of the page
 
BruceM
post Mar 31 2020, 07:39 AM
Post#8


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


Sounds like it may be worth a try, Albert. How do I determine the IP address of the on-site server? If my local IP address is 123.45.67.89 I expect the server IP would be the same except for the 89. I tried nslookup, but that seems to be something else, maybe the corporate DNS server (?).
Go to the top of the page
 
AlbertKallal
post Mar 31 2020, 09:13 PM
Post#9


UtterAccess VIP
Posts: 3,065
Joined: 12-April 07
From: Edmonton, Alberta Canada


You can start a command line in windows.

Type in this:

ping -4 NAMEOFSERVER

the "-4" above forces this to IPV4, and then type in the name of the server. don't recall if you need \\SERVERNAME, or just SERVERNAME.

But, the above thus should ping the server 4 times, an it will spit out the IP address on the first line.

On a number of networks, for reasons of security, we have some remote desktop machines, and a few more running SQL server. They are NOT part of the domain setup, so we simply use the IP address. And this case tends to get even worse when using a VPN since the client computer is who knows what!!!


R
Albert


Go to the top of the page
 
BruceM
post Apr 1 2020, 08:18 AM
Post#10


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


Thanks! I wonder if that number ever changes, or if there is a way to verify it via VBA or vbscript. I think it is possible to ping with VBA, and to parse the result, but I haven't had a chance to test it yet.

Anyhow, this is something I will be experimenting with as soon as I can. I can test with a hard-coded ip address, and learn whether it improves the performance.
Go to the top of the page
 
HackSlash
post Apr 3 2020, 03:12 PM
Post#11



Posts: 4
Joined: 30-October 18



I would never assume or rely on the VPN DNS being misconfigured. DNS does and should work across a VPN.

cheekybuddha was on the right track about tracking versions in Tables. I track my front end version in a table in the front DB file. It has a field for minimum back end level cited per front end version. I track my my back end revision in a table in the back end. Thus the front end is designed to work with a given back end version. You could do the same with your back end file, citing the expected front end version for this back end revision. Your front end loader would query the local DB file for it's front end version and compare it with the front end version expected by the backend and only perform the copy if the number is lower. This might be faster than the way you were checking before.

All that said, don't use your AccessDB across the VPN at all. You will corrupt your backend this way. If a user needs to get to the DB remotely they need to RDP across the VPN in to a machine that is local and then open the DB from there. This is the only safe way to do it.
Go to the top of the page
 
DanielPineault
post Apr 3 2020, 03:16 PM
Post#12


UtterAccess VIP
Posts: 7,343
Joined: 30-June 11



QUOTE
don't use your AccessDB across the VPN at all. You will corrupt your backend this way.

notworthy.gif


Your options for remote users include (refer to https://www.devhut.net/2016/09/24/access-ba...drive-dropbox/)
  • Remote Desktop
  • TeamViewer
  • CITRIX
  • Terminal Services
  • Upsize the back-end and host it on Azure, or another online host (thus creating a Hybrid database). So the back-end resides in the clouds and your front-end continues, as is, on each user’s PC.
  • Remote hosting services such as http://accesshosting.com/ (there are many others)

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
FrankRuperto
post Apr 3 2020, 05:11 PM
Post#13



Posts: 980
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
don't use your AccessDB across the VPN at all. You will corrupt your backend this way.

DITTO!
This post has been edited by FrankRuperto: Apr 3 2020, 05:11 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
molluscan
post Apr 3 2020, 05:58 PM
Post#14



Posts: 3
Joined: 17-November 16



I am in the same situation as the original poster having a very small (20mb) but important database (actually an .mdb because I still need user level security) and now working from home I need to accelerate the project of getting the backend upsized to SQLServer express. I learnt Daniels lesson the hard way having experienced data corruption due to LAN problems. Now I have set up a VPN with SSL file sharing and this is working well but I didnt realise that the VPN would be so slow as to be unworkable and now it is clear here that it is also dangerous so I will go for remote desktop now.
My question, if it not too far off topic (or maybe point me to another thread about migrating Access?), is where do I go from here with an Access to SQLServer 'hybrid' solution. I have read that the main benefit is security but there is no speed benefit unless and until the queries are re-written to run on the SQL server to limit the data required to be transferred. I guess this speed problem is particularly important if I considered a cloud solution for hosting the backend. What is involved in rewriting the queries for someone completely unfamiliar with SQL Server and only familiar with SQL through having looked at the code produced by the Access query builder when making union queries for example. Is there anything like query builder for SQLServer express. Or is there anything like as good as Access for building (web based seems to be the norm) forms and reports against SQL Server (or migrating my existing Access forms and reports)
Many thanks - there are probably a lot of other people working on this sort of project from home now!
Go to the top of the page
 
FrankRuperto
post Apr 3 2020, 08:42 PM
Post#15



Posts: 980
Joined: 21-September 14
From: Tampa, Florida USA


In a nutshell,

1) I suggest you go with a hosted SQL-Server or Azure db. A small 20MB db like yours shouldn't cost much to host. 2) Most simple Access SQL queries that don't return too many rows can be left "as is", but complicated queries with many joins and queries that return many rows should be executed in SQL Server, as ODBC will usually do a poor job of translating the AccessSQL query and create a lot of network traffic by bringing in all rows from all tables involved. 3) For most reports, especially complicated one's, you can create VIEWS in SQL-Server and link to that view, just as you would normally link to a table.
This post has been edited by FrankRuperto: Apr 3 2020, 08:47 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
molluscan
post Apr 4 2020, 01:47 PM
Post#16



Posts: 3
Joined: 17-November 16



Many thanks Frank. It sounds like I have a good way to fill my time at home - learning SQL-Server!
Should I copy this post to another part of the forum since it's a bit off topic? If so where?
Im interested in more tips how to get started in SQL-server. I have already made a successful migration of the backend tables and relationships using SSMA assistant and learnt quirks like mapping to datetime not datetime2..
Thanks again
M
Go to the top of the page
 
BruceM
post Apr 6 2020, 10:29 AM
Post#17


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


To those who are repeating that I should not use Access across a VPN, I know that. I thought I had indicated as much in my original posting. I guess I didn't make it clear enough that I know Access does not work well across a VPN (I know about the risk of corruption), and that I am trying to transitition to SQL Server. The other options would require more buy-in from corporate IT than I am likely to get. I expect reading Access data across a VPN is OK, because as I understand the corruption is a risk during a write operation.

My original question was about deferring checking the version information (modified date of the files) to determine if an update is needed unless the user is on the wired LAN, in which case the check, and the update if needed, are very fast. I could keep the version information in a table, but part of what I am trying to do is avoid updates at all except when on the LAN, unless the user specifically needs the update. For instance, I may change the format of a report or something like that, which is helpful but may not be essential to all users. On the other hand, if a remote user is getting an error that interferes with their work, they need to be able to get the update even if it takes a while.

Again, the question is about copying a file from a local network location to the user's App Data folder, NOT about running Access across a VPN. I have some databases already on SQL Server for the BE, which is working well for remote use, but opening them had been getting bogged down because of the routine version check of the front end file.
Go to the top of the page
 
BruceM
post Apr 6 2020, 10:38 AM
Post#18


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


Another consideration is that UDFs (user-defined functions), domain functions, and some Access functions do not do well with SQL Server linked tables. In my experience it is not the number of rows so much as the row-by-row processing that needs to take place with such functions that causes the real performance hit. Also, I agree about joins. Such processing is best done in SQL Server in most cases, or via pass-through queries for things like combo box row sources.
Go to the top of the page
 
BruceM
post Apr 6 2020, 10:38 AM
Post#19


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


Another consideration is that UDFs (user-defined functions), domain functions, and some Access functions do not do well with SQL Server linked tables. In my experience it is not the number of rows so much as the row-by-row processing that needs to take place with such functions that causes the real performance hit. Also, I agree about joins. Such processing is best done in SQL Server in most cases, or via pass-through queries for things like combo box row sources.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    30th May 2020 - 09:54 AM