The Golden VB Script that will make your life much easier

[RuralGuy]

In regards to 'Grades~1', I have never used this expression so I'm not sure if that would work or not.

That would only work if that is also the value Windows has in the 8+3 name. Having embedded spaces in the name should also work.

[usmcgrunt]

Having embedded spaces in the name should also work.

Allen, thanks for jumping in on this issue. The original DB path was as follows:

G:\Lesson Card Database\LCDBv12rc5.mdb

Everytime I run the 'Golden VB Script' I get the following series of error messages:

The command line you used to start Microsoft Office Access
contains an option that Microsoft Office Access doesn't recognize.
Exit and restart Microsoft Office Access using valid command-line options.

Then I click 'OK' and Microsoft Access starts to open and the following error message appears:

Microsoft Office Access can't find the database file 'G:\Lesson.mdb.'
Make sure you entered the correct path and file name.

So, it appears that the vbScript is missing everything after the first space in the pathname. It is not recognizing the embedded spaces.

Now if I rename the path to:

G:\LessonCardDatabase\LCDBv12rc5.mdb

all works fine so I know it has something to do with the spaces in the folder name. What I don't know is can I solve this issue with code modifications.

As always, just looking for a little spark for my fire; not water for my bucket.

Sean

[pkstormy]

Hi Sean,

In the posting, there's an important note regarding using spaces in LUName. If you use spaces anywhere, the script won't work. Any type of VB Scripting doesn't like spaces in the path's name since it is DOS based. Try Allan's suggestion to see if that works.

[usmcgrunt]

In the posting, there's an important note regarding using spaces in LUName. If you use spaces anywhere, the script won't work. Any type of VB Scripting doesn't like spaces in the path's name since it is DOS based. Try Allan's suggestion to see if that works.

pkstormy, thanks for the response. I did in fact see that message but I also remembered seeing the below message so I thought that there might be other ways to make this happen.

It's recommended to use the UNC name for the location but this is not a neccessity. If you have foldernames with spaces, use the ~1 for the 8 character foldername (just like you would do for a shortcut).

Understand the vbscript limitation. I just need to get the IT guys to rename some folders on the network to accommodate this shortcoming.

Thanks for taking the time to put together this code. It is just what I needed to distribute my database across our network.

Sean

[RuralGuy]

Good luck and have fun!

[Mr.E]

Question for you guys regarding this script:

the script seems to be working fine in creating a clone (I can see it ine the folder) but I'm getting an error "The database is in an unrecognized format."

If I then click on any MS Access link/icon I will get the same message, however, if I open MS Access and map to the file it will then open.

Do you think this is the script or do you think there is something amiss with the install of MS Office/Access?

I'm running XP Pro SP 3 and MS Access SP 2 ver:11.6566.8132.

The code looks like this:

Set WshNetwork = CreateObject("WScript.Network")

GetUser = WshNetwork.UserName
Set WshNetwork = Nothing

LUName = "H:\TestEPDB\DB2.mde"
oldname = LUName

newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
retval = 0
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

retval = objFSO.CopyFile(oldname, newName, True)
Set objFSO = Nothing
Dim objShell
Set objShell = CreateObject("Wscript.Shell")

objShell.run "MSAccess.exe " & newName
Set objShell = Nothing

Thanks for your help,

E

[Exwarrior187]

^^ Mr. E.

Looks to me like it's trying to open your file with the ".mde" extension when it's probably ".mdb".

Your code:

LUName = "H:\TestEPDB\DB2.mde"
oldname = LUName

[pkstormy]

pkstormy, thanks for the response. I did in fact see that message but I also remembered seeing the below message so I thought that there might be other ways to make this happen.

Sean

Sean -

Regarding:

LUName = \\elan87vm\g:\MyDatabaseFolder\test\LCDBv12rc5.accdb


I was reviewing these posts and I just noticed something else in your LUName which would cause the script to error. You have an accdb file versus an mde. If this is the case, you also need to change the following line in the script:

newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
to
newName = Replace(LUName, ".accdb", "") & GetUser & ".accdb"

[Mclaren]

Man, you have no idea how this simplifies updating front ends.

You may want to update comments in the script for use with 2007 and 2010 versions of access.

Works a treat, right out the box (well apart from changing the path details etc)

[pkstormy]

Thanks - I appreciate the feedback!

[TheShabz]

Is there an updated version of this with the above bugs/issues/suggestions added/changed?

[pkstormy]

Is there an updated version of this with the above bugs/issues/suggestions added/changed?

Do you mean this part that's in the comments:

'change below to "accdb" or "accde" or "mdb", whichever you're using. Below simply adds the GetUser to newName variable
'ex: PKSampleXP.mde becomes 'PKSampleXP<LoginID>.mde for newName variable
newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

For example (for an accdb)
newName = Replace(LUName, ".accdb", "") & GetUser & ".accdb" <- but don't clone backend accdb file!!

For an accde file:
newName = Replace(LUName, ".accde", "") & GetUser & ".accde"

For an mdb file:
newName = Replace(LUName, ".mdb", "") & GetUser & ".mdb" <- but don't clone backend mdb file!!

and for an mde file:
newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

Sorry about that - I should've been a bit more clearer on the comments on that part.

[pkstormy]

As a last note:

Be careful using this....

Added note courtesy of Brad Davison:

If you want to delete the cloned file, you can experiment using the code below at the end of the vb script:

This seems to work: objShell.run "cmd /K del " & newName & " & exit"

(again, experiment with the above code as I haven't thoroughly tested it myself)

I have not done this and this can have undesiriable affects. I might suggest creating a vb script that perhaps runs at midnight to delete the cloned files if you find the need to delete these files. I myself leave the cloned files as they provide some benefits for me. ** Note also that I have it so it creates the cloned files in a separate folder by itself so as to not get mixed in with any other files.

[pkstormy]

Here's another suggestion I recommend along with using the script:

Since I maintain about a dozen different mde frontends, I create one 'Main Menu' mdb which has buttons in it to launch each of the vb scripts. This way the user only needs to open one mdb file (which doesn't really need to be cloned but can).

An example of code for one of the buttons in the Main Menu is such:

Private Sub cmdQuitlineEval_Click()
Call Shell("wscript.exe F:\Data\Databases\QuitlineEval\QuitlineEval.vbs")
End Sub

(IMPORTANT NOTE: Since running wscript depends upon the Intranet setting in IE, you may need to edit your intranet setting and make sure your network drive is mapped as a trusted site. Otherwise the user will get a 'Security File' warning whereby they can click 'Run' and it will still run the vb script - just a nusance.)

See attached picture:

When I need to make a change to one of the programs, I make the changes in my development folder, create a new mde file, and then copy/paste that mde file to the LUName folder and I'm done. Out of curteousy, I'll then send out an email to users to close the app (at their convenience unless it's a criticial change) to simply close and re-open the app via the button on the Main Menu.

[pkstormy]

I also recommend a program called EXEScript. This program works very nicely and will convert a vbs script into an exe file. Price I think is around $40.

The goal though is to prevent users from finding the MyApp<UserName>.mde file and opening that (since they are bypassing the cloning process run from the vb script and may not be getting the latest source code.) That's one reason I put the cloned files in a separate folder from where the vb script is located. Users should only open the vb script (or exe file if created via the ExeScript program) each time they want to open the program (or use the Main Menu program demonstrated in the previous post).

Note also: The cloned folder must have read AND write permissions for all users, otherwise it will lock the *.ldb file (for just that user - but it won't affect other users as it would when multiple users share the same mde or accde file.) The beauty of this script is that you can copy code at any time without making users close out of the app and if a user's cloned copy get's corrupted for some reason, simply have them re-run the vb script and it will automatically correct itself with a fresh new cloned copy from the source. (nothing you need to do on your end.) This script has saved me many headaches and hours from having to track down multiple users to find the 1 specific user that has their mde file open and is locking everyone else out of the app. I hope it makes things just as easy for you. Feel free to send any comments my way.