Powershell: Seeking ideas to practice useful scripts in non-administrator mode

[Isaac]

I'm beginning to learn Powershell, within an environment where I do not have administrator rights and do not perform any I.T. admin type of tasks on this machine.

I've learned that Powershell, while I'm sure it can do a variety of things across both categories, is kind of intended more for the I.T. admin type audience.

However, I'm pretty sure I will be able to find other uses for it too.

This post is just an invitation for anyone who has used Powershell for miscellaneous tasks of any kind on the non-administrator side of things, please feel free to mention what they were. I will be looking for ways to practice. Because the large majority of internet information has to do with admin tasks that I won't be needing, remote machines, etc.

One of the first things I do in a new job is create a little tool, using VBA or VBScript, which converts a vertical list of items on the user's clipboard, and changes it to one long horizontal string like 'value', 'value', 'value' or value,value,value - for the purpose of pasting into a SQL In() clause. Because I have found it to be a frequent need.
(Although the more years I spent in Management Studio, the more magic I find it can do - like Alt+Mouse to write on multiple lines at once!!) which makes this probably less needed. But anyway, that's an example of something I intend to see if I can replicate using powershell, just for fun.

Finding non-admin scripts I can write will be useful to learn PS, as well as to use it on my home machine and side-job clients.

 

[Uncle Gizmo]

This post is just an invitation for anyone

Re:- "This post is just an invitation for anyone" --- I noticed that your post has not yet received a reply, hence I'm giving it a bump to increase the chances of encouraging some interaction...

 

[NauticalGent]

Sorry I missed this one Isaac. Give this a try, Daniel Pineault has done a few functions with PS and even though they are VBA-centric, they may give you a couple of ideas.

 

[JMongi]

@Isaac - Find out anything interesting?

I have my DB launch script in VBscript currently. But, with the IE deprecation invalidating VB splash screen code, I'm looking into using powershell to handle the splash screen and launch the app while running the vbscript in betweeen. Still learning. One thing I can't seem to find (because of the aforementioned focused on IT admin) is if a domain user can run my VBscript (which has been the case so far), can they run powershell scripts? Or do they still need to do the one time exception setting.

Not trying to hijack this thread, more commiserating with the focus on IT admin vs local user execution.

 

[NauticalGent]

See if the link in post #3 helps...

He uses it with VBA but I have been successful using his PS Wrapper with VBS

 

[JMongi]

@NauticalGent - That's some pretty good references. Thanks!

 

[NauticalGent]

@NauticalGent - That's some pretty good references. Thanks!

Easy day, this is the link to the PS Wrapper I was referring to:

VBA - Run PowerShell Command | DEVelopers HUT

A couple examples of how you can execute a PowerShell command through VBA a retrieve the response.

www.devhut.net

 

[Isaac]

@Isaac - Find out anything interesting?

I have my DB launch script in VBscript currently. But, with the IE deprecation invalidating VB splash screen code, I'm looking into using powershell to handle the splash screen and launch the app while running the vbscript in betweeen. Still learning. One thing I can't seem to find (because of the aforementioned focused on IT admin) is if a domain user can run my VBscript (which has been the case so far), can they run powershell scripts? Or do they still need to do the one time exception setting.

Not trying to hijack this thread, more commiserating with the focus on IT admin vs local user execution.

Interesting set of questions.

First, can I ask what you mean about IE deprecation invalidating the VB splash screen code? is it because you're automating Internet Explorer to present an HTML page or something? I usually just use VBScript in this context for details like checking the version, maybe copying network accdb/r to local, and occasionally a couple other small config things. I usually pop up a VBScript MsgBox for them to "press OK and wait for the db to open" (or something like that) so that their patience in the few seconds of silence that ensues has already been granted.

I would be very cautious in thinking powershell can be used instead of VBScript. What I found out is that there are SO many things - indeed, a big majority of things people generally like to use PS for - that can only be done if PowerShell is opened as an administrator, that I decided not to pursue trying to use it on my corporate laptop/network/virtual desktop environment at all at the moment - although I may still try a few things like copying files etc which I'm sure is fine.

Anyway - if all you want to do is copy files and fire up an Office app - literally just start it up - an Office app or an Office app's file (.accdb/r etc), then PS is probably fine for that. https://stackoverflow.com/questions/52270669/opening-ms-access-database-with-powershell

I did spend many hours on a beautiful Microsoft youtube tutorial (which can take days to work through one video, very helpful), and I found out that PowerShell is best seen as a tool for Admins, to be totally honest with you. I'm not saying there aren't, maybe, 100 non-admin uses for it, I'm just saying if there are 100 non-admin uses for it there are 10,000 admin use cases that it usually gets used for.

Curious about the switch to PS in your particular case and the deprecation and splash. Sorry not on AWF much any more and just seen this thread again

 

[JMongi]

Just posted today, no worries.
Internet Explorer is EOL (replaced by Edge for better or worse). Most VB code for a splash screen uses IE objects to display a splash screen picture. Microsoft made some changes to what a script could access (for security reasons). I don't do much scripting so I don't know the specifics, I just read that in general the landscape has changed in the last year or so.
So, I figured the Windows Form portions of powershell might be useful as a substitute to calling some IE objects via VB. I've seen many samples of this but haven't yet tried implementing it into my code. @NauticalGent 's post from devhut is very nice. But, even it does not explicitly talk about distributing a PS script for other users. It does mention that there will be a UAC prompt when running certain PS commands even with toggles to make things run in the background.

So, the whole thing might be much ado about nothing.

 

[JMongi]

@Isaac - Since you are wandering around here at the moment

I was going to resurrect another thread but I'll just ask you here as its tangentially related. In another thread about FE updating, you described your process as:

Deleting Existing Files
Copying New Files in Place of Existing Files

This was to save time checking if they exist, then do this, etc.

Is there a benefit to the delete files vs copying with overwrite? After running a separate little function that makes sure my directory structure is as required I then run a subroutine with the following code:

Sub FileXfer ()                                    'Transfer new files from shared network location to local user
sModuleName = "FileXfer"

'Set the string paths
Dim Source, Destination
Source = cServerPath & "*.*"
Destination = sLocalFE

'Copy files
oFSO.CopyFile Source, Destination, True        'The true flag suppresses the user prompt for overwrite

Call ErrHandler (sModuleName)
End Sub

Note: Please remember this is VBScript syntax not VBA.
Note2: oFSO, sModuleName, cServerPath and sLocalFE are all instantiated/defined elsewhere.

 

[JMongi]

Well, I kind of answered my own question!

To workaround the whole sync vs async question it is easiest to count the number of files in a directory to check if the files match. If I'm overwriting, the counts will always match. If I delete and then copy, then a count will be a good test of successful copying.

Sub FileXfer ()        'Transfer new files from shared network location to local user
sModuleName = "FileXfer"

'Set the string paths
Dim sSource, sDestination
sSource = cServerPath
sDestination = sLocalFE

'Delete current files and copy new ones
oFSO.DeleteFolder sDestination, False
oFSO.CopyFolder sSource, sDestination

'Check if files copied
Dim oSource, oDestination
Set oSource = oFSO.GetFolder(sSource)
Set oDestination = oFSO.GetFolder(sDestination)

Do Until oDestination.Files.Count = oSource.Files.Count
    Wscript.Sleep(1)
Loop

Call ErrHandler (sModuleName)
End Sub
'=================

 

[theDBguy]

Well, I kind of answered my own question!

To workaround the whole sync vs async question it is easiest to count the number of files in a directory to check if the files match. If I'm overwriting, the counts will always match. If I delete and then copy, then a count will be a good test of successful copying.

Sub FileXfer ()        'Transfer new files from shared network location to local user
sModuleName = "FileXfer"

'Set the string paths
Dim sSource, sDestination
sSource = cServerPath
sDestination = sLocalFE

'Delete current files and copy new ones
oFSO.DeleteFolder sDestination, False
oFSO.CopyFolder sSource, sDestination

'Check if files copied
Dim oSource, oDestination
Set oSource = oFSO.GetFolder(sSource)
Set oDestination = oFSO.GetFolder(sDestination)

Do Until oDestination.Files.Count = oSource.Files.Count
    Wscript.Sleep(1)
Loop

Call ErrHandler (sModuleName)
End Sub
'=================

Have you considered using the FileExists method?

FileExists method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[JMongi]

I copy more than one file, so this seems better than individually testing if each file is copied.

 

[theDBguy]

I copy more than one file, so this seems better than individually testing if each file is copied.

I see...

 

[Isaac]

Just posted today, no worries.
Internet Explorer is EOL (replaced by Edge for better or worse). Most VB code for a splash screen uses IE objects to display a splash screen picture. Microsoft made some changes to what a script could access (for security reasons). I don't do much scripting so I don't know the specifics, I just read that in general the landscape has changed in the last year or so.
So, I figured the Windows Form portions of powershell might be useful as a substitute to calling some IE objects via VB. I've seen many samples of this but haven't yet tried implementing it into my code. @NauticalGent 's post from devhut is very nice. But, even it does not explicitly talk about distributing a PS script for other users. It does mention that there will be a UAC prompt when running certain PS commands even with toggles to make things run in the background.

So, the whole thing might be much ado about nothing.

Ahh. Thanks for the additional info.
I guess I don't use the whole IE+Splash screen thing, but yeah they keep trying to kill it haha.

You don't find the Access form object gives you whatever you need for this splash screen? (I do understand the potential value of a well presented , strategically messaging/launching app, just hadn't done much with ie in the splash screen context.... Although I did implement it once in one db for all kinds of messages, i just used vba

Which leads me to & please forgive me for sounding picky but it's a pet peeve of mine when people use "VB". Especially in these conversations when differentiating between vba, vbscript, and potentially older things like vb6 or newer like .net is relevant if not the Gist.

I'll see what I can get into trouble on checking out ps with any kind of gui... didn't know

 

[JMongi]

I actually agree with you about VB...too inspecific. Not sure why I abbreviated it that way.

It looks like right now there is no appreciable delay in deleting/copying the FE over and launching into Access. So, no splash screen seems to be needed at this time.
At some point in the future I will dabble in PS to test my hand at the proverbial WiFi on/off issue when using a JET/Access BE. It appears to be "solved" from a code standpoint in PS but it's unclear whether that can be rolled into a launch script that won't need user input. When I find this out, I'll post back here.

 

[NauticalGent]

When I had the same problem, I used this code I found and it works as advertised. Like you, I determined the delay was not all that bad so I decided not implement it...

If you use it, let me know what you think.

 

[Isaac]

there is no appreciable delay in deleting/copying the FE over and launching into Access

That's a great (ideal) situation.
Fortunately, most of my FE files, and especially the more experience I got (or is it Discipline to clean up obsolete crap?) -- are indeed extremely small.

So unless you're on the worst VPN in the world with the worst Wi-Fi in the hemisphere it should be a pretty quick copy.

I will repeat what I have said before, I find the situation quite untenable as in expert access programmers simply going around telling people they can't use Wi-Fi over VPN.

I can't remember the last time I met someone who didn't regularly or even exclusively use Wi-Fi in their home in the year 2022! People are simply not going to tunnel ethernet cords under their walls to satisfy Microsoft Access programmers best practices advice, we must adapt somehow...
The last time I saw an Ethernet cord was cleaning out my garage a couple years ago

 

[theDBguy]

That's a great (ideal) situation.
Fortunately, most of my FE files, and especially the more experience I got (or is it Discipline to clean up obsolete crap?) -- are indeed extremely small.

So unless you're on the worst VPN in the world with the worst Wi-Fi in the hemisphere it should be a pretty quick copy.

I will repeat what I have said before, I find the situation quite untenable as in expert access programmers simply going around telling people they can't use Wi-Fi over VPN.

I can't remember the last time I met someone who didn't regularly or even exclusively use Wi-Fi in their home in the year 2022! People are simply not going to tunnel ethernet cords under their walls to satisfy Microsoft Access programmers best practices advice, we must adapt somehow...
The last time I saw an Ethernet cord was cleaning out my garage a couple years ago

@Isaac Are you sure you're not confusing "copying" a file using WiFi over "using" an active connection to the BE on a server with an Access FE over WiFi? @The_Doc_Man have said it many times here, Access uses the SMB protocol that may not be well suited over a WiFi connection. However, that doesn't mean it won't work. That's why it's only considered as "best" practice (not "never" practice).

 

[Isaac]

@Isaac Are you sure you're not confusing "copying" a file using WiFi over "using" an active connection to the BE on a server with an Access FE over WiFi? @The_Doc_Man have said it many times here, Access uses the SMB protocol that may not be well suited over a WiFi connection. However, that doesn't mean it won't work. That's why it's only considered as "best" practice (not "never" practice).

I don't actually have them confused although as I reread my post I can see how it made it look that way, since I was bringing up the idea of using Wi-Fi within the context of our discussion about simply copying a file. In truth, one thing just made me remember the other thing. Yes, I agree definitely no one is going around saying that a file can't be copied over Wi-Fi and yes I am thinking about the persistent connection to the back end on a network.
But that's the thing, if people are working from home and the corporation has decided to use VPN as its method of connectivity, I'm just suggesting that basically all of those users with very few exceptions are in a bad situation where they are going to be told that really won't work for our access database even though probably very few of them are going to change their life to stop using Wi-Fi.

Good point you're right it's the persistent connection to a back end which is on a shared network drive while other people are using Wi-Fi over VPN from their front ends is what I was thinking of.

And honestly, I think you know me well enough I'm not really intending to be mean or critical to the people who say that because they may be right strictly speaking. I'm just suggesting that that's kind of a no-go and sometimes it makes me wonder if I began what appears to be a possibly permanent switch away from access at just about the right time. The time when everybody started working from home and just like that, a huge percentage of access databases didn't work anymore.....
Although I'm very glad and relieved to hear that many people are still using SQL server or azure successfully as a back end with users in that same situation. That does make me feel better since I try to avoid using Microsoft Access as a backend in the first place anyway.

I still wonder how Wells Fargo did it. A couple years ago I had about a 9-month contracting stent there and I was very impressed with how well they're set up worked. I would walk into the office with my laptop in hand, open it and power it up and log in. As soon as I did that, despite the fact that there was no physical cord connected to my computer whatsoever, I immediately had full access to all network drives that I needed, I could use SQL server management studio, it almost my computer almost seemed to think that I had an ethernet cord connected is how stable everything was. I have a feeling that in that scenario access probably would have worked okay. And there was no virtual desktop involved! Pretty slick.