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.

 

[LeoM]

Thank you to everyone first of all and I fully agree but when you don't have any other alternative (because this is the reality) i believe you should follow what the company allow you. All around the world i always usually used a network drive to store the BE. In the country where i'm now, the company give us a network drive where is not allowed the multiple users connection that means the first open and lock the database block all others even to read the data only. I asked an explanation, the answer was: is not allowed !!!! I was shocked and surprised but i have to deal with such "stupid" problematics. They explain me that the only solution is to use a shared folder so the only solution coming in my mind is to use a virtual disk linked to the share folder where the BE is located. If, based on your experiences, have another solution/suggestion, please share, will be more then welcome but consider that here people work (unfortunately) in this way. Cheers

 

[arnelgp]

They explain me that the only solution is to use a shared folder

can't you create a shared folder on your computer?

 

[LeoM]

can't you create a shared folder on your computer?

Sorry not clear. Do you mean I should share a folder physically located in my PC or in my OneDrive or where?

 

[arnelgp]

if you are connected to your Network, you can create a Folder (on your pc) and put the Backend there.
then you can create a shared folder from this folder, so that all users of your db can connect to your folder.
you should add account Everyone (with all rights/previlege) to this folder.

you shared a folder on your physical pc.

 

[LeoM]

ok so the users they will connect to my pc trough the Network? finding the shared folder under my pc number/name ?

 

[dalski]

create a Folder (on your pc) and put the Backend there.
then you can create a shared folder from this folder, so that all users of your db can connect to your folder.

So the second shared folder avoids the OP from having to keep his computer on for other users to be able to access?

 

[arnelgp]

So the second shared folder avoids the OP from having to keep his computer on for other users to be able to access?

yes, but will Mostly (and for sure) result in database corruption.

for the OP, does your users use your db 24 hours or just as you goes out after office hours?
if it 24 hours operation then you need to make your pc open 24 hours (that is no problem if you have A/C).

 

[LeoM]

yes, but will Mostly (and for sure) result in database corruption.

for the OP, does your users use your db 24 hours or just as you goes out after office hours?
if it 24 hours operation then you need to make your pc open 24 hours (that is no problem if you have A/C).

Yes, it was my next question on what happen when PC is off. No, this is not feasible because there are many impacts on this:
- I will go home in the afternoon, and people may work night shift so no access to my pc
- I may be on leave (so pc off)
So, the answer is yes, database should be available always (even if may or not used).
Cheers

 

[arnelgp]

Yes, it was my next question on what happen when PC is off. No, this is not feasible because there are many impacts on this:
- I will go home in the afternoon, and people may work night shift so no access to my pc
- I may be on leave (so pc off)

then you request 1 Dedicated PC that will perform as the Server for your database.
this PC will be open 24 hours. i have done this many times. have a "strict" time schedule.
the pc should be given a "rest time" and all users must be notified of this. pick a time (during lunch time is the best).
if you have a better plan, use it.

all contributor here will say that Cloud shared folder is a No, no. It will easily corrupt your database and every
hard work and records will be lost. but if you still insist just do it as you wish.

 

[arnelgp]

i browsed your old post and you seem to have Sharepoint on your premise.
why not use it? someone here more experience on Sharepoint will enlighten you.

 

[dalski]

you seem to have Sharepoint on your premise.
why not use it?

Sounds like Sharepoint can handle Access' Transfer Connection Protocols (if that's the right term). Whereas OneDrive/ Nested Shared Folder cannot. I was not aware of this - thanks.

 

[LeoM]

As far i understood (because is the first time i'm forced to use it), the only way to use a BE in a SharePoint is to create a local link to the Sharepoint (in OneDrive) then create a virtual disk. In this way my FE can use the BE tables.

 

[Minty]

Sounds like Sharepoint can handle Access' Transfer Connection Protocols (if that's the right term). Whereas OneDrive/ Nested Shared Folder cannot. I was not aware of this - thanks.

Not in my experience, it's effectively the same storage, just without the "active" file syncing.
It's a good place to distribute a FE from and an awful place to store a BE.

 

[GPGeorge]

As far i understood (because is the first time i'm forced to use it), the only way to use a BE in a SharePoint is to create a local link to the Sharepoint (in OneDrive) then create a virtual disk. In this way my FE can use the BE tables.

If you have SharePoint in your organization, you can migrate the data to SharePoint lists. That is not the same as putting the BE in a SharePoint location.