Avoiding opening the database from OneDrive (1 Viewer)

[Marshall Brooks]

I have code to basically only allow my FrontEnd to open from the users desktop. Skeleton code looks like this:

Location1 = "c:\users\" & GetUserName & "\desktop"
If LCase(CurrentProject.Path) = Location1 Then
 ' Do nothing
 Else
 ' Close Database with message
 End If

Our company is getting ready to implement OneDrive Backup which would copy all of the files from the Desktop (including my Database) to OneDrive.

I want to change the database to open from anywhere in the users directory, EXCEPT the desktop and particurlarly not OneDrive.

One option would be to specify a Directory Name - "C:\Users\<username>\Access Database" for example, and require the database to be there. But I would prefer the database to be able to be located in any folder in the user directory.

In words, I want

If CurrentPath is <any subfolder of C:\Users\<username>\, but not any subfolder of C:\Users\<username>\OneDrive and not any subfolder of C:\Users\<username>\Desktop and not any subfolder of C:\Users\<username>\Documents Then Open Normally.

How would I write this in code? (The wildcards are where I am confused.)

 

[tvanstiphout]

Open a command prompt (cmd.exe).
Type: SET
The list contains all available environment variables. Note the one called USERPROFILE.
Use the VBA function Environ("USERPROFILE") to return this string.

 

[Marshall Brooks]

You misunderstood. I have a function called GetUserName, which basically does that.

Let's say my user name is "12345".

I want the database to open if the database is located in:
C:\Users\12345\Access Database
C:\Users\12345\B
C:\Users\12345\C
etc.

But not in:
C:\Program Files\Access Database or
C:\Users\12345\OneDrive <or subfolders> or
C:\Users\12345\Desktop <or subfolders>
etc.

How do I code that, please?

 

[CJ_London]

Why not just put the FE in the c drive or a folder in the c drive -c:\databases

Then put a shortcut on the desktop or the windows bar

 

[tvanstiphout]

You misunderstood. I have a function called GetUserName, which basically does that.

Let's say my user name is "12345".

I want the database to open if the database is located in:
C:\Users\12345\Access Database
C:\Users\12345\B
C:\Users\12345\C
etc.

But not in:
C:\Program Files\Access Database or
C:\Users\12345\OneDrive <or subfolders> or
C:\Users\12345\Desktop <or subfolders>
etc.

How do I code that, please?

Seems to me you want the path to be: Environ("USERPROFILE") & "\" & "yourdatabasename.accde"

 

[tvanstiphout]

Seems to me you want the path to be: Environ("USERPROFILE") & "\" & CurrentProject.Name

 

[theDBguy]

How would I write this in code? (The wildcards are where I am confused.)

Maybe here's a start?

Location1 = "c:\users\" & GetUserName & "\desktop"
If Left(CurrentProject.Path, Len(Location1)) = Location1 Then
 ' Do nothing
 Else
 ' Close Database with message
 End If

 

[The_Doc_Man]

But I would prefer the database to be able to be located in any folder in the user directory.

Not all users would be able to recognize a non-OneDrive folder and thus choose a safe place. If your company is setting up OneDrive for all users, they might have an all-encompassing site policy on drive setup. This is a question you MUST bring up with your IT staff ASAP to explain that OneDrive is not compatible with MS Access because of its use of Server Message Block (SMB) protocols that OneDrive doesn't honor. You might not have enough clout, but the boss who wants the database working correctly certainly WILL have more clout.

For that matter, I hope your back-end file isn't subject to OneDrive predation, either. That is just as incompatible as the FE file would be.

 

[arnelgp]

you can also put the db to each users "Documents" folder

Environ$("UserProfile") & "\Documents\YourdbName.accdb

 

[Marshall Brooks]

I think I can get there with variations of @theDBguy code.

Answering some other questions:

@CJ_London - I don't think that we have write-access to C:\Databases. We do have write-access to C:\Users\<username> - and putting it in this subtree ensures that the logged in user is the only one opening the FE. (Unlikely, but if somehow two users mapped an external drive to C:\). Right now, the database only opens from C:\Users\<username>\Desktop or U:\, with a check that U:\ is mapped to the current user. As I said initially, I could come up with a new folder name - for example C:\Users\<username>\Access_Database and restrict opening to that, but I'd rather give the user more flexibility.

@The_Doc_Man - Actually, I brought this up with IT yesterday and pointed to the previous thread where you mentioned that and the IT Associate researched it and confirmed that Access is not compatible with OneDrive. You can copy the database to one drive, but you shouldn't open it from there. Currently they block .pst and .swf files from OneDrive Backup. I recommended that they add all database extensions also - .mdb, mde, .accdb, .accdb, .accdr etc. Importantly, they were not aware of the incompatibility until I mentioned it. We'll see how it goes.

Not all users would be able to recognize a non-OneDrive folder and thus choose a safe place.

Didn't word my comment correctly. We only have two OneDrive Folders: "C:\Users\<username>\OneDrive" (which comes with Windows) and "C:\Users\<username>\OneDrive - Company Name." The plan will be to have two checks: 1) Is the db in the C:\Users\<username>\ subtree? If not, exit. If so, is the database in one of the OneDrive folders? if so, exit.

you can also put the db to each users "Documents" folder

The Documents folder is also going to be backed up (moved) to OneDrive ... - Not an option, but good thought.

Maybe here's a start?

That's close to what I'm looking for. I changed my mind and do not want to exclude the desktop at this time. Reason being that if I put out the new version, the current version will auto-update and then try to open and say it is in a prohibited location. The current database will not open if OneDrive Backup is enabled (after OneDrive moves the database out of the desktop folder), and I will just send a notice with the new version that I recommend creating it's own folder rather than using the desktop. AirCode would be something like this:

Location1 = "c:\users\" & GetUserName
Location1Exclude = "c:\users\" & GetUserName & "\OneDrive"
If Left(CurrentProject.Path, Len(Location1)) = Location1 Then ' if we are in a subtree of UserName
     If Left(CurrentProject.Path, Len(Location1Exclude)) = Location1Exclude Then ' if we are in a OneDrive folder
        'Close Database with message'
    End If
 Else
 ' Close Database with message
 End If

 

[CJ_London]

I don't think that we have write-access to C:\Databases. We do have write-access to C:\Users\<username>

there is a hidden folder called appdata off the username, This has 4 folders, one of which is called Local.

C:\Users\<username>\AppData\Local

You'll find a lot of folders related to various apps on your device You can create a folder in there called say 'databases' and place your apps in there.

 

[Mark_]

Are your users opening the front end directly or through a batch file?

 

[Marshall Brooks]

Are your users opening the front end directly or through a batch file?

Directly. (Probably via a shortcut after this update, but ...)

I possibly where you are coming from and it leads to a good follow-up question:

@The_Doc_Man: Currently the database opens, imports an Excel file, checks if the user is authorized, and then checks if it is on the User's Desktop or on the U:\ Drive. If it isn't, it closes. When OneDrive Backup is installed, it is going to MOVE the database from C:\Users\<username>\Desktop to C:\Users\<username>\OneDrive - CompanyName\Desktop and the original folder will not exist. The database will see it is not in the correct folder and will close. Is this an issue? i.e. is it a problem if the database opens from a OneDrive folder and then closes? No forms are loaded, so no BE data can be changed by the user.

If it is a problem, then I need to figure out a better way to prevent it.

Fortunately, most of our users are pretty co-operative, so if it comes down to it, I can probably just pick a location and say "Put the new version of the database in C:\Users\<username>\Database and create a desktop shortcut and don't try putting it anywhere else b/c it won't open from any other folder, and they might try one or two folders and give up before they try a OneDrive folder ... (hopefully ...)

 

[The_Doc_Man]

If the backend file is subject to OneDrive backup, it will fail at some point with an "inconsistent database format" or some similar error. Exactly how long it takes to crash around you is not exactly predictable, but the odds go up very quickly. I would say that the fact the DB opens and does something active BEFORE it tests its location is a written invitation to disaster.

I had a case where I had to protect against running the wrong file. Technically, if you have a split FE/BE combination, you only indirectly open the BE file in its shared area. (This indirect opening is due to use of SMB protocol, mentioned in an earlier post.) You directly open the FE file. In that FE file, if you have identified an Opening Form - perhaps that eventually becomes a switchboard or dispatcher - you can do location tests in that opening form's Form_Open event - and Form_Open can be canceled BEFORE you let anything else happen. You can test where your DB is running and you can test where the BE is located (as separate tests). Of course, if the BE is in the wrong place, you won't see it and the FE would crash anyway unless you were using dynamic linking - which is a digression I don't need to explore for THIS discussion.

The ideal case for an Access FE/BE pair is that neither file is subject to OneDrive backup. However, there IS an exception. If you have the FE file in a public folder that IS backed up via OneDrive but you use a batch script (launched by an icon) or manual process to COPY that FE to a non-OneDrive, per-user, private working area that is also a Trusted Location, then open the FE from there, then it doesn't matter where the master copy of the FE is stored. You just can't run the FE directly from the shared "distribution" area OR anywhere that OneDrive can touch it while it runs, and that is what you must prevent.

The shared but non-OneDrive common area for the DB has to be set up so the users of the DB have MODIFY permissions on the folder and its files. If your IT guy balks, you can point him to this comment:

The U.S. Navy Enterprise Data Center in New Orleans had a few Access databases with split FE/BE and had no problem with what we did, even for machines with SBU or FOUO classification (machines holding Privacy Act and HIPAA data). We created a group Identifier. We granted MODIFY permissions for the target folder itself and all of its contained files to that identifier. We verified that PASSTHRU applied to any folders "above" the working folder. (Since most of the time, PASSTHRU was already granted to the AuthenticatedUsers intrinsic group, this was no stretch.) Then, when we needed to add a user to the DB, we just granted them membership to the group (assigned them the group identifier). When they left or changed jobs, we removed the identifier (removed them from the group). Easy, peasey, lemon squeezy. We just assured that users put the FE in an appropriate place on their private machines - a place where OneDrive would NOT get involved AND that could be marked as a Trusted Location.

 

[Pat Hartman]

I don't know if you will also have users running using Citrix. This is the batch file I use for Citrix:

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
start %USERPROFILE%\DwgLog\DrawingLog.accdb

This is the same batch file running locally:

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
start c:\DwgLog\DrawingLog.accdb

As you can see, I dictate the folder name for both instances but for Citrix, I must use the userprofile to create a valid path but on the local PC, I can just make it whatever I want. Just make the path name short.

The User has a desktop shortcut in both environments that runs one of the two batch files. The batch files themselves are stored on the server in the folder where the master copy of the FE is stored so I can change them easily without disturbing the users if I ever need to.

as you can see, neither batch file includes error handling. Once they have been tested, they would only break on one condition - someone moved the FE or renamed it.

The App contains version tables in both FE and VE. on open, the app compares the versions and won't open unless they match. This about as simple a set up as I could devise. My goal of simplicity comes from always working as a consultant and knowing that probably someone less experienced would have to manage the app once I was gone.

 

[Mark_]

One other reason to use a batch file; USER'S generally don't edit them, but they like renaming things.

Friend had to set up a script that ran often. It would delete all batch files in the target directory then copy in the proper batch file from its source location. Put an end to renaming "Prog.Bat" to "Accounting database.bat". This was in the 90's, but I really don't see users playing nicer.

 

[The_Doc_Man]

This was in the 90's, but I really don't see users playing nicer.

We must recognize that USER is a four-letter word, often used in the same context as many other, less reputable four-letter words.

 

[Marshall Brooks]

Not quite clear to me below:

I would say that the fact the DB opens and does something active BEFORE it tests its location is a written invitation to disaster.

You directly open the FE file. In that FE file, if you have identified an Opening Form - perhaps that eventually becomes a switchboard or dispatcher - you can do location tests in that opening form's Form_Open event - and Form_Open can be canceled BEFORE you let anything else happen. You can test where your DB is running and you can test where the BE is located (as separate tests). Of course, if the BE is in the wrong place, you won't see it and the FE would crash anyway unless you were using dynamic linking - which is a digression I don't need to explore for THIS discussion.

I gave you bad information earlier.

The database uses an AutoExec macro that calls an AutoExec VBA script. The VBA script opens a hidden Down For Maintenance timer form, then it calls an AutoUpdate subroutine that checks if the user is authorized, if the database is in a proper folder, and if there is an updated version on the server. After the AutoUpdate routine finishes, it imports an Excel File into a local table in the FE, and opens the switchboard form.

So the check and closing IS occurring before the database does anything - other than opening itself. I think it is is probably okay.

At this time, I'm not worried about the back end. It should not be subject to OneDrive backup, and as you said, if it was, it wouldn't be found by the front end anyway.

 

[The_Doc_Man]

It should not be subject to OneDrive backup, and as you said, if it was, it wouldn't be found by the front end anyway.

If I gave that impression somehow, I was incorrect. If the file was moved to a folder subject to OneDrive you would still see it, and if OneDrive was imposed on the file wherever it was, you would STILL see it. OneDrive doesn't make the file invisible. It makes it unusable by Access.

 

[Marshall Brooks]

If I gave that impression somehow, I was incorrect. If the file was moved to a folder subject to OneDrive you would still see it, and if OneDrive was imposed on the file wherever it was, you would STILL see it. OneDrive doesn't make the file invisible. It makes it unusable by Access.

No I understood what you meant, I wasn't clear on my reply.

Let's say the BE is loaded on \\NetworkPath\BE_Folder.

So far (knock on wood), my company hasn't been saying "In two months, we are going to change \\NetworkPath\BE_Folder" to a OneDrive file folder. They have been saying things like "We are implementing OneDrive Backup and when it runs, your files will be MOVED from Folder A to 'path\OneDrive' where they will be backed up." They haven't given any indication of intending to do this for network drives, but if they did, the front end wouldn't find the BE b/c it wouldn't be in the expected location, so (hopefully), no corruption of the data.