Solved Run Dos Command in VBA (1 Viewer)

[LeoM]

Goodmorning everyone.
I'm actually mapping a virtual drive connecting to a share folder which all users have already accessed.
I'm, using the following simple code which work perfect for one user (C020411):

Dim objShell As Object
Set objShell = CreateObject("WScript.Shell")
objShell.Run "cmd.exe /c Subst J: ""C:\Users\C020411\OneDrive - Document\""", 0, True

Now, since this mapping is valid only for the user "C020411" (window username), I have to do it dynamically based on the window user. I have a function to get the window username named actNames(1) and I'm trying, in all ways, to use it in the connection string but so far i was not able to do it. I tried the following and others but i have difficulty to locate the " in the correct way.

objShell.Run "cmd.exe /c Subst J: ""C:\Users\" & actNames(1) & ""\OneDrive - Document\""", 0, True

Someone can kindly tell me how to use that variable actNames(1) in the string?

Thank you,
Cheers

 

[Gasman]

I would just use Environ().

Put it all into a string variable and debug.print it until you get it correct.

The use that in the command

? environ("username")
Paul

?  "C:\Users\" & environ("username") & "\OneDrive - Document\"
C:\Users\Paul\OneDrive - Document\

You can also use CHR(34) if all the double quotes confuse you.

 

[LeoM]

Great, good idea, i used to check the string but not with Debug.Print but directly in the execution so it was not visible if correct or not.
Thanks a lot, problem solved.

 

[RonPaii]

Great, good idea, i used to check the string but not with Debug.Print but directly in the execution so it was not visible if correct or not.
Thanks a lot, problem solved.

I don't think you should put the FE or BE on OneDrive. Windows copies changes to the cloud which could slow down both your FE and keep OneDrive busy uploading the changes. The BE very likely and will be corrupted.

 

[The_Doc_Man]

Concur with @RonPaii - OneDrive doesn't use an update method consistent with Access operations. It uses whole-file transfer methods, but Access is specifically designed to use SMB (Server Message Block) protocols, which allow partial file updates via essentially random file access. If you have a network problem, the difficulty is that the BE file's last "snapshot" might have been taken during a partial file update, i.e. an update in-progress but not yet completed. This is actually not an Access problem, because I've even had ORACLE whole-file backups fail after restoration if the DB was not idle when the backup occurred. (The industry often uses the term "quiescent" - I guess because it sounds a bit classier than "ain't busy.")

 

[GPGeorge]

One more concurring opinion regarding active execution of any accdb FE or BE from OneDrive, DropBox or similar cloud drives.

They are fine for static backups or storage -- as long as that's the only reason an accdb ends up there, and it's not done during use as The_Doc_Man explained.

The worst that can happen is corruption. The best that can happen is "last change wins" meaning you overwrite someone else's changes, or someone else overwrites your changes. Maybe that is even worse, now that I think about it, because you end up with unexplained data changes that can't be sorted out easily.