Definitely interested in a linking video. These are very helpful. Also a compact and repair script for the backend and the front end would be very helpful. Thank you
I don't know if you check these comments still but I appear to be having an issue getting this work when the folder doesn't already exist. I will always get a "Path not found" error on the line that is trying to create the folder in the first place. In the DoFullDeploy Sub, on the line of If blnFolderExists = False Then objFso.CreateFolder strDeployFolder. Did something get left out of the code? I've looked up other "Create Folder" code snippits and tried to use those in place of this, but they all come back with the same error of "Path not found". I know it's not found, it hasn't been created yet, that's what we are trying to do on this very line.
Interesting - it is possible that blnFolderExists is false because it could not find the folder. But it is ALSO possible that the rest of your folder path is not valid (maybe a typo). Also, are there any spaces in your path? In that case you'll need to put some quotes around the path. ie. strDeployFolder = Chr(34) & "C:\dev\my folder with some spaces" & Chr(34)
@@seanmackenziedataengineering It believe the answer is none of the above. My agency is controlled by another IT department and I don't believe we have the proper permissions to execute this directly into main C: drive folder. My permissions under C: show only Read & Execute, List folder contents, and Read. I think this is what is stopping the script from creating the folder. If I manually create the folder Dev under C: it will execute properly and create other subfolders under that. This throws a kink in my plans, lol. I was hoping to pass out this script alone to all my users. I may try to incorporate a way for the script to get the usernames so it will automatically create folders under their C:\Users\"USERNAME" folder. They should all have read/write permissions in there at least.
@@vastoholic2 I see! That makes sense. If you need it, you can use the first method from this video to get the username in vbScript: ua-cam.com/video/u9TRuwdT46U/v-deo.html Just remove "As String" from variable declarations since vbScript uses only Variant declarations (no types). Good luck!
And yet, another question. I managed to set a custom Icon to the desktop shortcut through the vbs inserting the code to copy the ico file to the deploy folder as the db file and then I added objShortCut.IconLocation = strDeployFolder & "\MyApp.ico" ... so far so good. But now I want to add /runtime in the end of the shortcut target but even if i type "/" my shortcut always gets a "\" so I have a resilt with untime and ofc this won't run. Any clues on how can I add the "/runtime"? Thank you so much for your time.
I don't think I've run into that one before! Are you able to set the target of the shortcut programmatically as well? ie. objShortCut.Target = .. To do it with double quotes, you may have to do something like: strTarget = Chr(34) & "C:\whatever\my file.accdb" & Chr(34) & " /runtime" objShortCut.Target = strTarget No idea if .Target is available, it could have another name / you may have to research it.
@@seanmackenziedataengineering No luck.... I get an error "error: Invalid procedure call or argument" "code: 800A0005" "Source: Microsoft VBScript runtime error Anyway... I managed to get what i want just pointing the shortcut to my vbs file and launch the db from it. I also changed the version check routine to get the date from a txt file... in my case it is alot faster. That txt file is created with the database everytime I change the Application Title. If you, for some reason, you want to check my changes, I can send you the code, no problem. I know I am repeating myself alot, but you can't imagine how much I am learning from you. THANKS!!!
You should be able to use the numeric constant 4 in vbScript. ie: rst = db.OpenRecordset(strSQL, 4) That will give you a nice non-editable snapshot that won't lock things up. You can check other constants in the Immediate window in Access. (Ctrl+g). Just type in ?dbOpenDynaset and it will give the constant for that.
I'm pretty sure you can! I have not done it - perhaps a future episode. These guys tried with VBA: stackoverflow.com/questions/66147047/download-google-drive-file-from-excel-sheet-using-vba
hi Sean, this is Mark. I have a database for my nonprofit. We have been using for over a year. For the past week I am wondering how to update the front end automatically. We have 5 computers that have the frontend on. I watched your video on how to update the front end. I saw you did a scrypt. Is there a way to auto update so people don't have to got to the scrypt about once a week and check. I would like it when people start the database it will check and a popup wil let them know there is an update. I have the original database on my laptop. As I work on it. I am wanting to bring up the form that i can use to deploy the new version. Then they can login and it will let them know about the new version. Is there a way you can make another video to autoupdate. Thanks.
Great question - at 31:00 I mention that we have the assumption that your FE will also have a simple look up to check if it is up to date. So, users never have to check if it is up to date; they just start every day and work. Then, one day you make changes and change the version. When the user starts the app, it says "Please get a new copy before continuing." and shuts down. They can't do anything until they run the script to update their copy.
@@seanmackenziedataengineering Hi Sean and thanks for replying. I saw the time that you mentioned and watched it. Do people open your application first. Or is it connected to access. So we can open access and it will automaticaly run your application and update? I am looking for one that we can open access and it will auto run the application and check for updates before they login.
@@markdesens3046 The user just runs the Access app each day. If they get a message in the app that they must update, they close the app, then run the script. Then, they reopen Access and continue.
@@seanmackenziedataengineering That is what I'm looking for, also how do I do a VBA code to run the script. Some people that I work with won't understand to run the script, let alone what to look for.. This one is simple. Now I just need help to understand how to impliment this in my databse. Anyone there that can help? I need to watch the video again I know. What I'm looking for is like you said above. As we open the database if there is a popup showing it needs to be updated then I'd like it to have a button to say run update. Then it will close the database and run the update.
Hi Sean, I did a little research and now I found a code to use in my dabase that will actually start the vbscript. I need help with where do I put the filepath in the script. I did a test with msgbox and using accesss to run it. And it ran. Now If you can help with where do I put the the paths in the script to look for the new version and where to put the new front end.
hi Sean. Question: is it possible, via vbscript, to open a database with a password (obviously I know it) then remove the password, make a backup of the file with compaction (I have the code ready and I know that it is not possible to compact a file with a password with VBS ) and then reset the password? Then I would perform the following steps: 1- open the db and remove the password 2.backup with compaction 3- reset the password What do you think? I need to know point 1 and 3 Thank you Lorenzo
Hey Lorenzo, I think that is possible. In step 1 you can create an Access reference, ie something like: Set acc = WScript.CreateObject("Access.Application") acc.OpenCurrentDatabase(myDBName, False, "myPassword") Set db = acc.CurrentDB() 'Do some stuff to backup acc.Quit I'm not sure about resetting the password. Great topic for a video! Needs a little research.
Hi again Sean. It's me buggin' again 🙂 One question... wouldn't it be easier to write this code in am Access Module so it gets easier to detect errors and then copy paste it to a text file? I think it's possible but is it advisable? Thanks again for your great work 👍
Actually, that's a great idea! I have done this a few times myself over the years. You just have to resist the temptation to give variables their types before copying over to .vbs 😁
Excellent video - thanks Sean!
Many thanks and glad it helped!
Definitely interested in a linking video. These are very helpful.
Also a compact and repair script for the backend and the front end would be very helpful.
Thank you
I put it on my list! Thanks for the feedback.
I don't know if you check these comments still but I appear to be having an issue getting this work when the folder doesn't already exist. I will always get a "Path not found" error on the line that is trying to create the folder in the first place. In the DoFullDeploy Sub, on the line of If blnFolderExists = False Then objFso.CreateFolder strDeployFolder.
Did something get left out of the code? I've looked up other "Create Folder" code snippits and tried to use those in place of this, but they all come back with the same error of "Path not found". I know it's not found, it hasn't been created yet, that's what we are trying to do on this very line.
Interesting - it is possible that blnFolderExists is false because it could not find the folder. But it is ALSO possible that the rest of your folder path is not valid (maybe a typo). Also, are there any spaces in your path? In that case you'll need to put some quotes around the path. ie.
strDeployFolder = Chr(34) & "C:\dev\my folder with some spaces" & Chr(34)
@@seanmackenziedataengineering It believe the answer is none of the above. My agency is controlled by another IT department and I don't believe we have the proper permissions to execute this directly into main C: drive folder. My permissions under C: show only Read & Execute, List folder contents, and Read. I think this is what is stopping the script from creating the folder. If I manually create the folder Dev under C: it will execute properly and create other subfolders under that. This throws a kink in my plans, lol. I was hoping to pass out this script alone to all my users. I may try to incorporate a way for the script to get the usernames so it will automatically create folders under their C:\Users\"USERNAME" folder. They should all have read/write permissions in there at least.
@@vastoholic2 I see! That makes sense. If you need it, you can use the first method from this video to get the username in vbScript: ua-cam.com/video/u9TRuwdT46U/v-deo.html
Just remove "As String" from variable declarations since vbScript uses only Variant declarations (no types). Good luck!
@@seanmackenziedataengineering Thanks. I realized it probably looks weird, but I made the original comment on my work account.
And yet, another question.
I managed to set a custom Icon to the desktop shortcut through the vbs inserting the code to copy the ico file to the deploy folder as the db file and then I added objShortCut.IconLocation = strDeployFolder & "\MyApp.ico" ... so far so good.
But now I want to add /runtime in the end of the shortcut target but even if i type "/" my shortcut always gets a "\" so I have a resilt with
untime and ofc this won't run.
Any clues on how can I add the "/runtime"?
Thank you so much for your time.
I don't think I've run into that one before! Are you able to set the target of the shortcut programmatically as well? ie. objShortCut.Target = ..
To do it with double quotes, you may have to do something like:
strTarget = Chr(34) & "C:\whatever\my file.accdb" & Chr(34) & " /runtime"
objShortCut.Target = strTarget
No idea if .Target is available, it could have another name / you may have to research it.
@@seanmackenziedataengineering No luck.... I get an error "error: Invalid procedure call or argument" "code: 800A0005" "Source: Microsoft VBScript runtime error
Anyway... I managed to get what i want just pointing the shortcut to my vbs file and launch the db from it.
I also changed the version check routine to get the date from a txt file... in my case it is alot faster.
That txt file is created with the database everytime I change the Application Title.
If you, for some reason, you want to check my changes, I can send you the code, no problem.
I know I am repeating myself alot, but you can't imagine how much I am learning from you. THANKS!!!
How do I add the dbOpenSnapshot?
You should be able to use the numeric constant 4 in vbScript. ie:
rst = db.OpenRecordset(strSQL, 4)
That will give you a nice non-editable snapshot that won't lock things up.
You can check other constants in the Immediate window in Access. (Ctrl+g). Just type in ?dbOpenDynaset and it will give the constant for that.
Can i dowload a .accde file from ggdrive or one drive…. By use vbs file?
Thanks you so much!
I'm pretty sure you can! I have not done it - perhaps a future episode. These guys tried with VBA: stackoverflow.com/questions/66147047/download-google-drive-file-from-excel-sheet-using-vba
@@seanmackenziedataengineering Thanks you
Great video Sean. Is there somewhere where we can download the script file?
Try this:
billzone.com/ep_146_vbscript_deploy.zip
Have fun!
hi Sean, this is Mark. I have a database for my nonprofit. We have been using for over a year. For the past week I am wondering how to update the front end automatically. We have 5 computers that have the frontend on. I watched your video on how to update the front end. I saw you did a scrypt. Is there a way to auto update so people don't have to got to the scrypt about once a week and check. I would like it when people start the database it will check and a popup wil let them know there is an update. I have the original database on my laptop. As I work on it. I am wanting to bring up the form that i can use to deploy the new version. Then they can login and it will let them know about the new version. Is there a way you can make another video to autoupdate. Thanks.
Great question - at 31:00 I mention that we have the assumption that your FE will also have a simple look up to check if it is up to date. So, users never have to check if it is up to date; they just start every day and work. Then, one day you make changes and change the version. When the user starts the app, it says "Please get a new copy before continuing." and shuts down. They can't do anything until they run the script to update their copy.
@@seanmackenziedataengineering Hi Sean and thanks for replying. I saw the time that you mentioned and watched it. Do people open your application first. Or is it connected to access. So we can open access and it will automaticaly run your application and update? I am looking for one that we can open access and it will auto run the application and check for updates before they login.
@@markdesens3046 The user just runs the Access app each day. If they get a message in the app that they must update, they close the app, then run the script. Then, they reopen Access and continue.
@@seanmackenziedataengineering That is what I'm looking for, also how do I do a VBA code to run the script. Some people that I work with won't understand to run the script, let alone what to look for.. This one is simple. Now I just need help to understand how to impliment this in my databse. Anyone there that can help? I need to watch the video again I know. What I'm looking for is like you said above. As we open the database if there is a popup showing it needs to be updated then I'd like it to have a button to say run update. Then it will close the database and run the update.
Hi Sean, I did a little research and now I found a code to use in my dabase that will actually start the vbscript. I need help with where do I put the filepath in the script. I did a test with msgbox and using accesss to run it. And it ran. Now If you can help with where do I put the the paths in the script to look for the new version and where to put the new front end.
This is great! Is there anywhere I can download your script? Thanks!
I added it to the bottom of the list: www.mackenziemackenzie.com/downloads/
@@seanmackenziedataengineering Awesome, thank you!
hi Sean. Question: is it possible, via vbscript, to open a database with a password (obviously I know it) then remove the password, make a backup of the file with compaction (I have the code ready and I know that it is not possible to compact a file with a password with VBS ) and then reset the password?
Then I would perform the following steps:
1- open the db and remove the password
2.backup with compaction
3- reset the password
What do you think?
I need to know point 1 and 3
Thank you
Lorenzo
Hey Lorenzo, I think that is possible. In step 1 you can create an Access reference, ie something like:
Set acc = WScript.CreateObject("Access.Application")
acc.OpenCurrentDatabase(myDBName, False, "myPassword")
Set db = acc.CurrentDB()
'Do some stuff to backup
acc.Quit
I'm not sure about resetting the password. Great topic for a video! Needs a little research.
Hi again Sean.
It's me buggin' again 🙂
One question... wouldn't it be easier to write this code in am Access Module so it gets easier to detect errors and then copy paste it to a text file?
I think it's possible but is it advisable?
Thanks again for your great work 👍
Actually, that's a great idea! I have done this a few times myself over the years. You just have to resist the temptation to give variables their types before copying over to .vbs 😁
@@seanmackenziedataengineering Thank you 🙂