Get custom database properties from an Access database
in [VbScript]
|
Prev: kill process
Next: split question
From: David C. Holley on 7 Jan 2010 14:16 About a year ago, I went down that road however I ended up abadoning it and went with a VBScript that simply copies the Front End DB from the server to the local machine and then opens it and creates a Shortcut if needed on the desktop. I then changed the icon from the VBScript icon to the Access icon and trained the users to always use that to open the database. We don't have anything special going on with the Front End so need to deal with an installation package, just a copy. It ensures that the users *always* have an up to date copy. Granted it'll copy a new file -each time- the user opens the database throughout the day, but that was seen as the more desirable scenario. Plus all of the local machines are in the same building as the server. The script below actually creates an instance of IE and provides status updates during startup. The seemingly random numbers are there to mark various points in the script for debuging. '-------------------------------------------------------------------------------------------- ' Author : David C. Holley ' Date : 9/1/2008 ' Purpose : Temporary startup script which backsup the local .MDE file (if present) and then ' copies the current .MDE file from the server to the local machine. Currently, ' there are two temp tables in the front end which can lead to database bloat. ' Copying the front end each time its started is a temporary stop-gap until I ' can spin out the temp tables to their own .MDB. '-------------------------------------------------------------------------------------------- '10 Dim wshShell Dim fso Dim strMsgText Dim strLocalTargetFolder Dim strServerSourceFolder Dim appIE Dim ieWindow '18 Set fso = createObject("Scripting.FileSystemObject") Set wshShell = createObject("Wscript.shell") '-------------------------------------------------------------------------------------------- Set appIE = CreateObject("InternetExplorer.Application") appIE.Offline = True appIE.AddressBar = False appIE.Height = 200 appIE.Width = 350 appIE.MenuBar = False appIE.StatusBar = False appIE.Silent = True appIE.ToolBar = False appIE.Title = "Trailer Management and Manifest Database" appIE.Navigate "" Do While appIE.Busy WScript.Sleep 100 Loop appIE.document.Open Set ieWindow = appIE.document ieWindow.Write "<html><head><title>" ieWindow.Write "Microsoft Access" ieWindow.Write "</title></head><body id='bodyTag' scroll=nostyle=font-size:8pt;font-family:Tahoma;background-color:#D4D0C8>" ieWindow.Write "<div style=font-weight:800;>" strMsgText = "" strMsgText = strMsgText & "The application will start momentarily.Please wait while your PC is checked for the most current version." &Chr(13) strMsgText = strMsgText & "There may be a short delay if files need to becopied to your machine. Please wait.<br>" ieWindow.write strMsgText ieWindow.write "</div>" ieWindow.Write "</body></html>" appIE.Visible = True '--------------------------------------------------------------------------------------------'50 strLocalTargetFolder = "C:\\Documents and Settings\\AllUsers\\Documents\\Trailer Management and Manifest\\Application" strServerSourceFolder ="P:\\Orlando\\Branch\\Data\\Production\\Operations\\Trailer Management andManifest\\Application" ieWindow.GetElementById("bodyTag").innerHTML =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking localfolders..." 'Create the folders if they don't exist call createLocaLFoLders'58 ieWindow.GetElementById("bodyTag").innerHTML =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Local folders exist..." ieWindow.GetElementById("bodyTag").innerHTML =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Copying Front End .MDEfile to local machine..." 'Copy the front end to the hard drive, overright if it already exists fso.copyfile strServerSourceFolder & "\\Trailer Management and ManifestFront End.mde", strLocalTargetFolder & "\\Trailer Management and ManifestFront End.mde", true ieWindow.GetElementById("bodyTag").innerHTML =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking desktopshortcut..." createShortcut 'Copy the short cut to this script if it doesn't exist fso.copyfile strServerSourceFolder & "\\Start Trailer Management andManifest Database.lnk", "C:\\Documents and Settings\\AllUsers\\Desktop\\Start Trailer Management and Manifest Database.lnk", true'71 ieWindow.GetElementById("bodyTag").innerHTML =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Starting Access..." wshShell.run "Msaccess.exe " & chr(34) & strLocalTargetFolder & "\\TrailerManagement and Manifest Front End.mde" & Chr(34) appIE.quit Set IEwindow = Nothing Set appIE = nothing Set wshShell = Nothing Set fso = nothing'82sub createLocaLFoLders Dim targetFolder Dim folderExists'87 targetFolder = "C:\\Documents and Settings\\All Users\\Documents\\TrailerManagement and Manifest" folderExists = FSO.FolderExists(targetFolder)'90 if folderExists = False then fso.CreateFolder targetFolder end if targetFolder = "C:\\Documents and Settings\\AllUsers\\Documents\\Trailer Management and Manifest\\Application"'95 folderExists = FSO.FolderExists(targetFolder) if folderExists = False then fso.CreateFolder targetFolder end ifend sub'103sub createShortcut aSplit = split(wscript.scriptname, ".") if Not fso.fileExists (aSplit(0) & ".lnk") then set newShortcut = wshShell.createShortcut (strServerSourceFolder & "\\" &aSplit(0) & ".lnk") newShortcut.TargetPath = WScript.ScriptFullName newShortCut.WorkingDirectory = strServerSourceFolder newShortcut.IconLocation = "C:\Program Files\MicrosoftOffice\OFFICE11\\msaccess.exe, 0" newShortCut.Description = "Start Trailer Management and ManifestDatabase" newShortCut.Save end ifend sub"Petr Danes" <skru.spammers(a)hotmail.com> wrote in messagenews:%23BP%23rfyjKHA.1540(a)TK2MSFTNGP06.phx.gbl...> Most excellent, Bob. Thankyouthankyouthankyou, that's exactly it. May Iask where you found this?>> And no, it's not anything like you describe. It's several copies of asingle-machine, single-user, mostly read-only database, running on variouscomputers in a library department. I want the copies to occasionallyinterrogate the master copy, but in a very non-intrusive way. The mastermachine may be shut down (it's not a server), or the db in use and I don'twant the users being pestered with error messages, or to have to wait fortimeouts. The intention here is for the db copies to spawn an invisiblescript process that quietly checks for certain conditions in the mastercopy, and only if all conditions are met, does it inform the user that anewer version is available and ask if it should be copied over. And amaximum of once per day - I don't want the users to be pestered by thisdialog every time they start the db to look up something. If the properconditions do not exist, or if the update dialog has already been dismissedonce that day, the script process will simply close and the user shouldnever even know that such an event took place. The data are notmission-critical or time-sensitive in any way - an update of once or twiceper month should be more than adequate.>> Pete>>>> "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> p�e v diskusn�m p��sp�vkunews:ug20oTxjKHA.1824(a)TK2MSFTNGP04.phx.gbl...>> Petr Danes wrote:>>> I use the following code in VBA in an Access database to retrieve a>>> custom database property:>>>>>> currentdb.Containers(1).Documents("Uzivatelske").Properties("Version")>>>>>> I would like to get this value via VBScript without opening the>>> database. I have found many tutorials and examples on how to retrieve>>> values form tables, but this is different and none of my attempts>>> have met with any success. I have tried connections,>>>> You mean ADO connections? That will not work. This collection is not>> available for ADO>>>>> objAcc.OpenCurrentDatabase>>>> No that won't help. You need to use DAO. Start by creating an instance>> of the DBEngine:>>>> Set DE = CreateObject("DAO.DBEngine.36")>>>> Then open your database:>> Set db = DE.OpenDatabase("C:\Program Files\Microsoft" & _>> "Office\Office\Samples\Northwind.mdb")>>>> Then go after the collection:>> msgbox db.Containers(1).Documents("Uzivatelske").Properties("Version")>>>> PS. I hope you are not doing this in ASP server-side code. DAO is>> single-threaded so it will make your ASP application thread-bound,>> impairing performance. Unfortunately, DAO is the only way to get at>> those properties.>>>> -->> HTH,>> Bob Barrows>>>>>>>
From: David C. Holley on 7 Jan 2010 14:19 I forgot that it also solves the problem of DB bloat on the local machines due to temp tables. "David C. Holley" <David.C.Holley> wrote in message news:Ofx%23z38jKHA.1540(a)TK2MSFTNGP06.phx.gbl... > About a year ago, I went down that road however I ended up abadoning it > and went with a VBScript that simply copies the Front End DB from the > server to the local machine and then opens it and creates a Shortcut if > needed on the desktop. I then changed the icon from the VBScript icon to > the Access icon and trained the users to always use that to open the > database. We don't have anything special going on with the Front End so > need to deal with an installation package, just a copy. It ensures that > the users *always* have an up to date copy. > > Granted it'll copy a new file -each time- the user opens the database > throughout the day, but that was seen as the more desirable scenario. Plus > all of the local machines are in the same building as the server. > > The script below actually creates an instance of IE and provides status > updates during startup. The seemingly random numbers are there to mark > various points in the script for debuging. > > '-------------------------------------------------------------------------------------------- > ' Author : David C. Holley > ' Date : 9/1/2008 > ' Purpose : Temporary startup script which backsup the local .MDE file > (if present) and then > ' copies the current .MDE file from the server to the local > machine. Currently, > ' there are two temp tables in the front end which can lead to > database bloat. > ' Copying the front end each time its started is a temporary > stop-gap until I > ' can spin out the temp tables to their own .MDB. > '-------------------------------------------------------------------------------------------- > '10 > Dim wshShell > Dim fso > Dim strMsgText > Dim strLocalTargetFolder > Dim strServerSourceFolder > Dim appIE > Dim ieWindow > '18 > Set fso = createObject("Scripting.FileSystemObject") > Set wshShell = createObject("Wscript.shell") > > '-------------------------------------------------------------------------------------------- > Set appIE = CreateObject("InternetExplorer.Application") appIE.Offline = > True appIE.AddressBar = False appIE.Height = 200 appIE.Width = 350 > appIE.MenuBar = False appIE.StatusBar = False appIE.Silent = True > appIE.ToolBar = False appIE.Title = "Trailer Management and Manifest > Database" appIE.Navigate "" Do While appIE.Busy WScript.Sleep 100 > Loop appIE.document.Open Set ieWindow = appIE.document ieWindow.Write > "<html><head><title>" ieWindow.Write "Microsoft Access" ieWindow.Write > "</title></head><body id='bodyTag' > scroll=nostyle=font-size:8pt;font-family:Tahoma;background-color:#D4D0C8>" > ieWindow.Write "<div style=font-weight:800;>" strMsgText = "" > strMsgText = strMsgText & "The application will start momentarily.Please > wait while your PC is checked for the most current version." &Chr(13) > strMsgText = strMsgText & "There may be a short delay if files need to > becopied to your machine. Please wait.<br>" ieWindow.write strMsgText > ieWindow.write "</div>" ieWindow.Write "</body></html>" appIE.Visible = > True > '--------------------------------------------------------------------------------------------'50 > strLocalTargetFolder = "C:\\Documents and > Settings\\AllUsers\\Documents\\Trailer Management and > Manifest\\Application" strServerSourceFolder > ="P:\\Orlando\\Branch\\Data\\Production\\Operations\\Trailer Management > andManifest\\Application" ieWindow.GetElementById("bodyTag").innerHTML > =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking > localfolders..." 'Create the folders if they don't exist call > createLocaLFoLders'58 ieWindow.GetElementById("bodyTag").innerHTML > =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Local folders > exist..." ieWindow.GetElementById("bodyTag").innerHTML > =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Copying Front End > .MDEfile to local machine..." 'Copy the front end to the hard drive, > overright if it already exists fso.copyfile strServerSourceFolder & > "\\Trailer Management and ManifestFront End.mde", strLocalTargetFolder & > "\\Trailer Management and ManifestFront End.mde", true > ieWindow.GetElementById("bodyTag").innerHTML > =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking > desktopshortcut..." createShortcut 'Copy the short cut to this script if > it doesn't exist fso.copyfile strServerSourceFolder & "\\Start Trailer > Management andManifest Database.lnk", "C:\\Documents and > Settings\\AllUsers\\Desktop\\Start Trailer Management and Manifest > Database.lnk", true'71 ieWindow.GetElementById("bodyTag").innerHTML > =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Starting Access..." > wshShell.run "Msaccess.exe " & chr(34) & strLocalTargetFolder & > "\\TrailerManagement and Manifest Front End.mde" & Chr(34) appIE.quit Set > IEwindow = Nothing Set appIE = nothing Set wshShell = Nothing Set fso = > nothing'82sub createLocaLFoLders Dim targetFolder Dim folderExists'87 > targetFolder = "C:\\Documents and Settings\\All > Users\\Documents\\TrailerManagement and Manifest" folderExists = > FSO.FolderExists(targetFolder)'90 if folderExists = False then > fso.CreateFolder targetFolder end if targetFolder = "C:\\Documents and > Settings\\AllUsers\\Documents\\Trailer Management and > Manifest\\Application"'95 folderExists = FSO.FolderExists(targetFolder) if > folderExists = False then fso.CreateFolder targetFolder end ifend > sub'103sub createShortcut aSplit = split(wscript.scriptname, ".") if Not > fso.fileExists (aSplit(0) & ".lnk") then set newShortcut = > wshShell.createShortcut (strServerSourceFolder & "\\" &aSplit(0) & ".lnk") > newShortcut.TargetPath = WScript.ScriptFullName > newShortCut.WorkingDirectory = strServerSourceFolder > newShortcut.IconLocation = "C:\Program > Files\MicrosoftOffice\OFFICE11\\msaccess.exe, 0" > newShortCut.Description = "Start Trailer Management and ManifestDatabase" > newShortCut.Save end ifend sub"Petr Danes" <skru.spammers(a)hotmail.com> > wrote in messagenews:%23BP%23rfyjKHA.1540(a)TK2MSFTNGP06.phx.gbl...> Most > excellent, Bob. Thankyouthankyouthankyou, that's exactly it. May Iask > where you found this?>> And no, it's not anything like you describe. It's > several copies of asingle-machine, single-user, mostly read-only database, > running on variouscomputers in a library department. I want the copies to > occasionallyinterrogate the master copy, but in a very non-intrusive way. > The mastermachine may be shut down (it's not a server), or the db in use > and I don'twant the users being pestered with error messages, or to have > to wait fortimeouts. The intention here is for the db copies to spawn an > invisiblescript process that quietly checks for certain conditions in the > mastercopy, and only if all conditions are met, does it inform the user > that anewer version is available and ask if it should be copied over. And > amaximum of once per day - I don't want the users to be pestered by > thisdialog every time they start the db to look up something. If the > properconditions do not exist, or if the update dialog has already been > dismissedonce that day, the script process will simply close and the user > shouldnever even know that such an event took place. The data are > notmission-critical or time-sensitive in any way - an update of once or > twiceper month should be more than adequate.>> Pete>>>> "Bob Barrows" > <reb01501(a)NOyahoo.SPAMcom> p�e v diskusn�m > p��sp�vkunews:ug20oTxjKHA.1824(a)TK2MSFTNGP04.phx.gbl...>> Petr Danes > wrote:>>> I use the following code in VBA in an Access database to > retrieve a>>> custom database property:>>>>>> > currentdb.Containers(1).Documents("Uzivatelske").Properties("Version")>>>>>> > I would like to get this value via VBScript without opening the>>> > database. I have found many tutorials and examples on how to retrieve>>> > values form tables, but this is different and none of my attempts>>> have > met with any success. I have tried connections,>>>> You mean ADO > connections? That will not work. This collection is not>> available for > ADO>>>>> objAcc.OpenCurrentDatabase>>>> No that won't help. You need to > use DAO. Start by creating an instance>> of the DBEngine:>>>> Set DE = > CreateObject("DAO.DBEngine.36")>>>> Then open your database:>> Set db = > DE.OpenDatabase("C:\Program Files\Microsoft" & _>> > "Office\Office\Samples\Northwind.mdb")>>>> Then go after the collection:>> > msgbox db.Containers(1).Documents("Uzivatelske").Properties("Version")>>>> > PS. I hope you are not doing this in ASP server-side code. DAO is>> > single-threaded so it will make your ASP application thread-bound,>> > impairing performance. Unfortunately, DAO is the only way to get at>> > those properties.>>>> -->> HTH,>> Bob Barrows>>>>>>> >
From: Petr Danes on 8 Jan 2010 04:15 Thanks, David, that's also a good approach. It has one problem for my situation, that the master machine may not be running. It's not a server, just the department head's PC. The copies need to be capable of independent function, regardless of whether anybody else is awake or not. That's why I want to use the spawned script approach, so that the database does not bog down looking for a machine that may be turned off. And DB bloat is not a problem for this app, the temp tables I use are truly tiny. It's a set of catalogs that are generated on the master machine and the copies are used only to look up information - no updating at all. The temp tables are simply where I keep track of some lookup parameters and results, and the occasional updates are more than enough to keep bloat under control. Pete "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku news:OnPGY58jKHA.5520(a)TK2MSFTNGP06.phx.gbl... >I forgot that it also solves the problem of DB bloat on the local machines >due to temp tables. > > "David C. Holley" <David.C.Holley> wrote in message > news:Ofx%23z38jKHA.1540(a)TK2MSFTNGP06.phx.gbl... >> About a year ago, I went down that road however I ended up abadoning it >> and went with a VBScript that simply copies the Front End DB from the >> server to the local machine and then opens it and creates a Shortcut if >> needed on the desktop. I then changed the icon from the VBScript icon to >> the Access icon and trained the users to always use that to open the >> database. We don't have anything special going on with the Front End so >> need to deal with an installation package, just a copy. It ensures that >> the users *always* have an up to date copy. >> >> Granted it'll copy a new file -each time- the user opens the database >> throughout the day, but that was seen as the more desirable scenario. >> Plus all of the local machines are in the same building as the server. >> >> The script below actually creates an instance of IE and provides status >> updates during startup. The seemingly random numbers are there to mark >> various points in the script for debuging. >> >> '-------------------------------------------------------------------------------------------- >> ' Author : David C. Holley >> ' Date : 9/1/2008 >> ' Purpose : Temporary startup script which backsup the local .MDE file >> (if present) and then >> ' copies the current .MDE file from the server to the local >> machine. Currently, >> ' there are two temp tables in the front end which can lead >> to database bloat. >> ' Copying the front end each time its started is a temporary >> stop-gap until I >> ' can spin out the temp tables to their own .MDB. >> '-------------------------------------------------------------------------------------------- >> '10 >> Dim wshShell >> Dim fso >> Dim strMsgText >> Dim strLocalTargetFolder >> Dim strServerSourceFolder >> Dim appIE >> Dim ieWindow >> '18 >> Set fso = createObject("Scripting.FileSystemObject") >> Set wshShell = createObject("Wscript.shell") >> >> '-------------------------------------------------------------------------------------------- >> Set appIE = CreateObject("InternetExplorer.Application") appIE.Offline = >> True appIE.AddressBar = False appIE.Height = 200 appIE.Width = 350 >> appIE.MenuBar = False appIE.StatusBar = False appIE.Silent = True >> appIE.ToolBar = False appIE.Title = "Trailer Management and Manifest >> Database" appIE.Navigate "" Do While appIE.Busy WScript.Sleep 100 >> Loop appIE.document.Open Set ieWindow = appIE.document ieWindow.Write >> "<html><head><title>" ieWindow.Write "Microsoft Access" ieWindow.Write >> "</title></head><body id='bodyTag' >> scroll=nostyle=font-size:8pt;font-family:Tahoma;background-color:#D4D0C8>" >> ieWindow.Write "<div style=font-weight:800;>" strMsgText = "" >> strMsgText = strMsgText & "The application will start momentarily.Please >> wait while your PC is checked for the most current version." &Chr(13) >> strMsgText = strMsgText & "There may be a short delay if files need to >> becopied to your machine. Please wait.<br>" ieWindow.write strMsgText >> ieWindow.write "</div>" ieWindow.Write "</body></html>" appIE.Visible = >> True >> '--------------------------------------------------------------------------------------------'50 >> strLocalTargetFolder = "C:\\Documents and >> Settings\\AllUsers\\Documents\\Trailer Management and >> Manifest\\Application" strServerSourceFolder >> ="P:\\Orlando\\Branch\\Data\\Production\\Operations\\Trailer Management >> andManifest\\Application" ieWindow.GetElementById("bodyTag").innerHTML >> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking >> localfolders..." 'Create the folders if they don't exist call >> createLocaLFoLders'58 ieWindow.GetElementById("bodyTag").innerHTML >> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Local folders >> exist..." ieWindow.GetElementById("bodyTag").innerHTML >> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Copying Front End >> .MDEfile to local machine..." 'Copy the front end to the hard drive, >> overright if it already exists fso.copyfile strServerSourceFolder & >> "\\Trailer Management and ManifestFront End.mde", strLocalTargetFolder & >> "\\Trailer Management and ManifestFront End.mde", true >> ieWindow.GetElementById("bodyTag").innerHTML >> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking >> desktopshortcut..." createShortcut 'Copy the short cut to this script if >> it doesn't exist fso.copyfile strServerSourceFolder & "\\Start Trailer >> Management andManifest Database.lnk", "C:\\Documents and >> Settings\\AllUsers\\Desktop\\Start Trailer Management and Manifest >> Database.lnk", true'71 ieWindow.GetElementById("bodyTag").innerHTML >> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Starting Access..." >> wshShell.run "Msaccess.exe " & chr(34) & strLocalTargetFolder & >> "\\TrailerManagement and Manifest Front End.mde" & Chr(34) appIE.quit Set >> IEwindow = Nothing Set appIE = nothing Set wshShell = Nothing Set fso = >> nothing'82sub createLocaLFoLders Dim targetFolder Dim folderExists'87 >> targetFolder = "C:\\Documents and Settings\\All >> Users\\Documents\\TrailerManagement and Manifest" folderExists = >> FSO.FolderExists(targetFolder)'90 if folderExists = False then >> fso.CreateFolder targetFolder end if targetFolder = "C:\\Documents and >> Settings\\AllUsers\\Documents\\Trailer Management and >> Manifest\\Application"'95 folderExists = FSO.FolderExists(targetFolder) >> if folderExists = False then fso.CreateFolder targetFolder end ifend >> sub'103sub createShortcut aSplit = split(wscript.scriptname, ".") if Not >> fso.fileExists (aSplit(0) & ".lnk") then set newShortcut = >> wshShell.createShortcut (strServerSourceFolder & "\\" &aSplit(0) & >> ".lnk") newShortcut.TargetPath = WScript.ScriptFullName >> newShortCut.WorkingDirectory = strServerSourceFolder >> newShortcut.IconLocation = "C:\Program >> Files\MicrosoftOffice\OFFICE11\\msaccess.exe, 0" newShortCut.Description >> = "Start Trailer Management and ManifestDatabase" newShortCut.Save end >> ifend sub"Petr Danes" <skru.spammers(a)hotmail.com> wrote in >> messagenews:%23BP%23rfyjKHA.1540(a)TK2MSFTNGP06.phx.gbl...> Most excellent, >> Bob. Thankyouthankyouthankyou, that's exactly it. May Iask where you >> found this?>> And no, it's not anything like you describe. It's several >> copies of asingle-machine, single-user, mostly read-only database, >> running on variouscomputers in a library department. I want the copies to >> occasionallyinterrogate the master copy, but in a very non-intrusive way. >> The mastermachine may be shut down (it's not a server), or the db in use >> and I don'twant the users being pestered with error messages, or to have >> to wait fortimeouts. The intention here is for the db copies to spawn an >> invisiblescript process that quietly checks for certain conditions in the >> mastercopy, and only if all conditions are met, does it inform the user >> that anewer version is available and ask if it should be copied over. And >> amaximum of once per day - I don't want the users to be pestered by >> thisdialog every time they start the db to look up something. If the >> properconditions do not exist, or if the update dialog has already been >> dismissedonce that day, the script process will simply close and the user >> shouldnever even know that such an event took place. The data are >> notmission-critical or time-sensitive in any way - an update of once or >> twiceper month should be more than adequate.>> Pete>>>> "Bob Barrows" >> <reb01501(a)NOyahoo.SPAMcom> p�e v diskusn�m >> p��sp�vkunews:ug20oTxjKHA.1824(a)TK2MSFTNGP04.phx.gbl...>> Petr Danes >> wrote:>>> I use the following code in VBA in an Access database to >> retrieve a>>> custom database property:>>>>>> >> currentdb.Containers(1).Documents("Uzivatelske").Properties("Version")>>>>>> >> I would like to get this value via VBScript without opening the>>> >> database. I have found many tutorials and examples on how to retrieve>>> >> values form tables, but this is different and none of my attempts>>> have >> met with any success. I have tried connections,>>>> You mean ADO >> connections? That will not work. This collection is not>> available for >> ADO>>>>> objAcc.OpenCurrentDatabase>>>> No that won't help. You need to >> use DAO. Start by creating an instance>> of the DBEngine:>>>> Set DE = >> CreateObject("DAO.DBEngine.36")>>>> Then open your database:>> Set db = >> DE.OpenDatabase("C:\Program Files\Microsoft" & _>> >> "Office\Office\Samples\Northwind.mdb")>>>> Then go after the >> collection:>> msgbox >> db.Containers(1).Documents("Uzivatelske").Properties("Version")>>>> PS. I >> hope you are not doing this in ASP server-side code. DAO is>> >> single-threaded so it will make your ASP application thread-bound,>> >> impairing performance. Unfortunately, DAO is the only way to get at>> >> those properties.>>>> -->> HTH,>> Bob Barrows>>>>>>> >> > >
From: David C. Holley on 8 Jan 2010 15:47 The script could be modified so that if the copy fails, the .mdb file on the local drive is started instead. On a side note, I'd move the file to a server which is most likely being backed up nightly. If anything happens to the machine that you're working with, say someone steals it, you may end up SOL. "Petr Danes" <skruspammers(a)no.spam> wrote in message news:%23$FJUMEkKHA.5020(a)TK2MSFTNGP02.phx.gbl... > Thanks, David, that's also a good approach. It has one problem for my > situation, that the master machine may not be running. It's not a server, > just the department head's PC. The copies need to be capable of > independent function, regardless of whether anybody else is awake or not. > > That's why I want to use the spawned script approach, so that the database > does not bog down looking for a machine that may be turned off. And DB > bloat is not a problem for this app, the temp tables I use are truly tiny. > It's a set of catalogs that are generated on the master machine and the > copies are used only to look up information - no updating at all. The temp > tables are simply where I keep track of some lookup parameters and > results, and the occasional updates are more than enough to keep bloat > under control. > > Pete > > > > "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku > news:OnPGY58jKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>I forgot that it also solves the problem of DB bloat on the local machines >>due to temp tables. >> >> "David C. Holley" <David.C.Holley> wrote in message >> news:Ofx%23z38jKHA.1540(a)TK2MSFTNGP06.phx.gbl... >>> About a year ago, I went down that road however I ended up abadoning it >>> and went with a VBScript that simply copies the Front End DB from the >>> server to the local machine and then opens it and creates a Shortcut if >>> needed on the desktop. I then changed the icon from the VBScript icon to >>> the Access icon and trained the users to always use that to open the >>> database. We don't have anything special going on with the Front End so >>> need to deal with an installation package, just a copy. It ensures that >>> the users *always* have an up to date copy. >>> >>> Granted it'll copy a new file -each time- the user opens the database >>> throughout the day, but that was seen as the more desirable scenario. >>> Plus all of the local machines are in the same building as the server. >>> >>> The script below actually creates an instance of IE and provides status >>> updates during startup. The seemingly random numbers are there to mark >>> various points in the script for debuging. >>> >>> '-------------------------------------------------------------------------------------------- >>> ' Author : David C. Holley >>> ' Date : 9/1/2008 >>> ' Purpose : Temporary startup script which backsup the local .MDE file >>> (if present) and then >>> ' copies the current .MDE file from the server to the local >>> machine. Currently, >>> ' there are two temp tables in the front end which can lead >>> to database bloat. >>> ' Copying the front end each time its started is a temporary >>> stop-gap until I >>> ' can spin out the temp tables to their own .MDB. >>> '-------------------------------------------------------------------------------------------- >>> '10 >>> Dim wshShell >>> Dim fso >>> Dim strMsgText >>> Dim strLocalTargetFolder >>> Dim strServerSourceFolder >>> Dim appIE >>> Dim ieWindow >>> '18 >>> Set fso = createObject("Scripting.FileSystemObject") >>> Set wshShell = createObject("Wscript.shell") >>> >>> '-------------------------------------------------------------------------------------------- >>> Set appIE = CreateObject("InternetExplorer.Application") appIE.Offline >>> = True appIE.AddressBar = False appIE.Height = 200 appIE.Width = 350 >>> appIE.MenuBar = False appIE.StatusBar = False appIE.Silent = True >>> appIE.ToolBar = False appIE.Title = "Trailer Management and Manifest >>> Database" appIE.Navigate "" Do While appIE.Busy WScript.Sleep 100 >>> Loop appIE.document.Open Set ieWindow = appIE.document ieWindow.Write >>> "<html><head><title>" ieWindow.Write "Microsoft Access" ieWindow.Write >>> "</title></head><body id='bodyTag' >>> scroll=nostyle=font-size:8pt;font-family:Tahoma;background-color:#D4D0C8>" >>> ieWindow.Write "<div style=font-weight:800;>" strMsgText = "" >>> strMsgText = strMsgText & "The application will start momentarily.Please >>> wait while your PC is checked for the most current version." &Chr(13) >>> strMsgText = strMsgText & "There may be a short delay if files need to >>> becopied to your machine. Please wait.<br>" ieWindow.write strMsgText >>> ieWindow.write "</div>" ieWindow.Write "</body></html>" appIE.Visible >>> = True >>> '--------------------------------------------------------------------------------------------'50 >>> strLocalTargetFolder = "C:\\Documents and >>> Settings\\AllUsers\\Documents\\Trailer Management and >>> Manifest\\Application" strServerSourceFolder >>> ="P:\\Orlando\\Branch\\Data\\Production\\Operations\\Trailer Management >>> andManifest\\Application" ieWindow.GetElementById("bodyTag").innerHTML >>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking >>> localfolders..." 'Create the folders if they don't exist call >>> createLocaLFoLders'58 ieWindow.GetElementById("bodyTag").innerHTML >>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Local folders >>> exist..." ieWindow.GetElementById("bodyTag").innerHTML >>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Copying Front End >>> .MDEfile to local machine..." 'Copy the front end to the hard drive, >>> overright if it already exists fso.copyfile strServerSourceFolder & >>> "\\Trailer Management and ManifestFront End.mde", strLocalTargetFolder & >>> "\\Trailer Management and ManifestFront End.mde", true >>> ieWindow.GetElementById("bodyTag").innerHTML >>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking >>> desktopshortcut..." createShortcut 'Copy the short cut to this script if >>> it doesn't exist fso.copyfile strServerSourceFolder & "\\Start Trailer >>> Management andManifest Database.lnk", "C:\\Documents and >>> Settings\\AllUsers\\Desktop\\Start Trailer Management and Manifest >>> Database.lnk", true'71 ieWindow.GetElementById("bodyTag").innerHTML >>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Starting >>> Access..." wshShell.run "Msaccess.exe " & chr(34) & strLocalTargetFolder >>> & "\\TrailerManagement and Manifest Front End.mde" & Chr(34) appIE.quit >>> Set IEwindow = Nothing Set appIE = nothing Set wshShell = Nothing Set >>> fso = nothing'82sub createLocaLFoLders Dim targetFolder Dim >>> folderExists'87 targetFolder = "C:\\Documents and Settings\\All >>> Users\\Documents\\TrailerManagement and Manifest" folderExists = >>> FSO.FolderExists(targetFolder)'90 if folderExists = False then >>> fso.CreateFolder targetFolder end if targetFolder = "C:\\Documents >>> and Settings\\AllUsers\\Documents\\Trailer Management and >>> Manifest\\Application"'95 folderExists = FSO.FolderExists(targetFolder) >>> if folderExists = False then fso.CreateFolder targetFolder end ifend >>> sub'103sub createShortcut aSplit = split(wscript.scriptname, ".") if Not >>> fso.fileExists (aSplit(0) & ".lnk") then set newShortcut = >>> wshShell.createShortcut (strServerSourceFolder & "\\" &aSplit(0) & >>> ".lnk") newShortcut.TargetPath = WScript.ScriptFullName >>> newShortCut.WorkingDirectory = strServerSourceFolder >>> newShortcut.IconLocation = "C:\Program >>> Files\MicrosoftOffice\OFFICE11\\msaccess.exe, 0" newShortCut.Description >>> = "Start Trailer Management and ManifestDatabase" newShortCut.Save end >>> ifend sub"Petr Danes" <skru.spammers(a)hotmail.com> wrote in >>> messagenews:%23BP%23rfyjKHA.1540(a)TK2MSFTNGP06.phx.gbl...> Most >>> excellent, Bob. Thankyouthankyouthankyou, that's exactly it. May Iask >>> where you found this?>> And no, it's not anything like you describe. >>> It's several copies of asingle-machine, single-user, mostly read-only >>> database, running on variouscomputers in a library department. I want >>> the copies to occasionallyinterrogate the master copy, but in a very >>> non-intrusive way. The mastermachine may be shut down (it's not a >>> server), or the db in use and I don'twant the users being pestered with >>> error messages, or to have to wait fortimeouts. The intention here is >>> for the db copies to spawn an invisiblescript process that quietly >>> checks for certain conditions in the mastercopy, and only if all >>> conditions are met, does it inform the user that anewer version is >>> available and ask if it should be copied over. And amaximum of once per >>> day - I don't want the users to be pestered by thisdialog every time >>> they start the db to look up something. If the properconditions do not >>> exist, or if the update dialog has already been dismissedonce that day, >>> the script process will simply close and the user shouldnever even know >>> that such an event took place. The data are notmission-critical or >>> time-sensitive in any way - an update of once or twiceper month should >>> be more than adequate.>> Pete>>>> "Bob Barrows" >>> <reb01501(a)NOyahoo.SPAMcom> p�e v diskusn�m >>> p��sp�vkunews:ug20oTxjKHA.1824(a)TK2MSFTNGP04.phx.gbl...>> Petr Danes >>> wrote:>>> I use the following code in VBA in an Access database to >>> retrieve a>>> custom database property:>>>>>> >>> currentdb.Containers(1).Documents("Uzivatelske").Properties("Version")>>>>>> >>> I would like to get this value via VBScript without opening the>>> >>> database. I have found many tutorials and examples on how to retrieve>>> >>> values form tables, but this is different and none of my attempts>>> >>> have met with any success. I have tried connections,>>>> You mean ADO >>> connections? That will not work. This collection is not>> available for >>> ADO>>>>> objAcc.OpenCurrentDatabase>>>> No that won't help. You need to >>> use DAO. Start by creating an instance>> of the DBEngine:>>>> Set DE = >>> CreateObject("DAO.DBEngine.36")>>>> Then open your database:>> Set db = >>> DE.OpenDatabase("C:\Program Files\Microsoft" & _>> >>> "Office\Office\Samples\Northwind.mdb")>>>> Then go after the >>> collection:>> msgbox >>> db.Containers(1).Documents("Uzivatelske").Properties("Version")>>>> PS. >>> I hope you are not doing this in ASP server-side code. DAO is>> >>> single-threaded so it will make your ASP application thread-bound,>> >>> impairing performance. Unfortunately, DAO is the only way to get at>> >>> those properties.>>>> -->> HTH,>> Bob Barrows>>>>>>> >>> >> >> > >
From: Bob Barrows on 8 Jan 2010 18:27
I think that's pretty good advice. David C. Holley wrote: > The script could be modified so that if the copy fails, the .mdb file > on the local drive is started instead. > > On a side note, I'd move the file to a server which is most likely > being backed up nightly. If anything happens to the machine that you're > working with, say someone steals it, you may end up SOL. > > "Petr Danes" <skruspammers(a)no.spam> wrote in message > news:%23$FJUMEkKHA.5020(a)TK2MSFTNGP02.phx.gbl... >> Thanks, David, that's also a good approach. It has one problem for my >> situation, that the master machine may not be running. It's not a >> server, just the department head's PC. The copies need to be capable of >> independent function, regardless of whether anybody else is awake or >> not. That's why I want to use the spawned script approach, so that the >> database does not bog down looking for a machine that may be turned off. >> And >> DB bloat is not a problem for this app, the temp tables I use are truly >> tiny. It's a set of catalogs that are generated on the master machine and >> the copies are used only to look up information - no updating at all. >> The temp tables are simply where I keep track of some lookup parameters >> and >> results, and the occasional updates are more than enough to keep >> bloat under control. >> >> Pete >> >> >> >> "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku >> news:OnPGY58jKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>> I forgot that it also solves the problem of DB bloat on the local >>> machines due to temp tables. >>> >>> "David C. Holley" <David.C.Holley> wrote in message >>> news:Ofx%23z38jKHA.1540(a)TK2MSFTNGP06.phx.gbl... >>>> About a year ago, I went down that road however I ended up >>>> abadoning it and went with a VBScript that simply copies the Front End >>>> DB from >>>> the server to the local machine and then opens it and creates a >>>> Shortcut if needed on the desktop. I then changed the icon from the >>>> VBScript >>>> icon to the Access icon and trained the users to always use that to >>>> open >>>> the database. We don't have anything special going on with the Front >>>> End so need to deal with an installation package, just a copy. It >>>> ensures >>>> that the users *always* have an up to date copy. >>>> >>>> Granted it'll copy a new file -each time- the user opens the >>>> database throughout the day, but that was seen as the more desirable >>>> scenario. Plus all of the local machines are in the same building as >>>> the >>>> server. The script below actually creates an instance of IE and >>>> provides >>>> status updates during startup. The seemingly random numbers are there >>>> to >>>> mark various points in the script for debuging. >>>> >>>> '-------------------------------------------------------------------------------------------- >>>> ' Author : David C. Holley >>>> ' Date : 9/1/2008 >>>> ' Purpose : Temporary startup script which backsup the local >>>> .MDE file (if present) and then >>>> ' copies the current .MDE file from the server to the >>>> local machine. Currently, >>>> ' there are two temp tables in the front end which can >>>> lead to database bloat. >>>> ' Copying the front end each time its started is a >>>> temporary stop-gap until I >>>> ' can spin out the temp tables to their own .MDB. >>>> '-------------------------------------------------------------------------------------------- >>>> '10 >>>> Dim wshShell >>>> Dim fso >>>> Dim strMsgText >>>> Dim strLocalTargetFolder >>>> Dim strServerSourceFolder >>>> Dim appIE >>>> Dim ieWindow >>>> '18 >>>> Set fso = createObject("Scripting.FileSystemObject") >>>> Set wshShell = createObject("Wscript.shell") >>>> >>>> '-------------------------------------------------------------------------------------------- >>>> Set appIE = CreateObject("InternetExplorer.Application") appIE.Offline >>>> = True appIE.AddressBar = False appIE.Height = 200 >>>> appIE.Width = 350 appIE.MenuBar = False appIE.StatusBar = False >>>> appIE.Silent = True >>>> appIE.ToolBar = False appIE.Title = "Trailer Management and >>>> Manifest Database" appIE.Navigate "" Do While appIE.Busy >>>> WScript.Sleep >>>> 100 Loop appIE.document.Open Set ieWindow = appIE.document >>>> ieWindow.Write "<html><head><title>" ieWindow.Write "Microsoft >>>> Access" ieWindow.Write "</title></head><body id='bodyTag' >>>> scroll=nostyle=font-size:8pt;font-family:Tahoma;background-color:#D4D0C8>" >>>> ieWindow.Write "<div style=font-weight:800;>" strMsgText = "" >>>> strMsgText = strMsgText & "The application will start >>>> momentarily.Please wait while your PC is checked for the most current >>>> version." >>>> &Chr(13) strMsgText = strMsgText & "There may be a short delay if files >>>> need to becopied to your machine. Please wait.<br>" ieWindow.write >>>> strMsgText ieWindow.write "</div>" ieWindow.Write "</body></html>" >>>> appIE.Visible = True >>>> '--------------------------------------------------------------------------------------------'50 >>>> strLocalTargetFolder = "C:\\Documents and >>>> Settings\\AllUsers\\Documents\\Trailer Management and >>>> Manifest\\Application" strServerSourceFolder >>>> ="P:\\Orlando\\Branch\\Data\\Production\\Operations\\Trailer >>>> Management andManifest\\Application" >>>> ieWindow.GetElementById("bodyTag").innerHTML >>>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking >>>> localfolders..." 'Create the folders if they don't exist call >>>> createLocaLFoLders'58 ieWindow.GetElementById("bodyTag").innerHTML >>>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Local folders >>>> exist..." ieWindow.GetElementById("bodyTag").innerHTML >>>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Copying >>>> Front End .MDEfile to local machine..." 'Copy the front end to the >>>> hard drive, overright if it already exists fso.copyfile >>>> strServerSourceFolder & >>>> "\\Trailer Management and ManifestFront End.mde", >>>> strLocalTargetFolder & "\\Trailer Management and ManifestFront >>>> End.mde", true ieWindow.GetElementById("bodyTag").innerHTML >>>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Checking >>>> desktopshortcut..." createShortcut 'Copy the short cut to this >>>> script if it doesn't exist fso.copyfile strServerSourceFolder & >>>> "\\Start >>>> Trailer Management andManifest Database.lnk", "C:\\Documents and >>>> Settings\\AllUsers\\Desktop\\Start Trailer Management and Manifest >>>> Database.lnk", true'71 ieWindow.GetElementById("bodyTag").innerHTML >>>> =ieWindow.GetElementById("bodyTag").innerHTML & "<br>-Starting >>>> Access..." wshShell.run "Msaccess.exe " & chr(34) & >>>> strLocalTargetFolder & "\\TrailerManagement and Manifest Front >>>> End.mde" & Chr(34) appIE.quit Set IEwindow = Nothing Set appIE = >>>> nothing Set wshShell = Nothing >>>> Set fso = nothing'82sub createLocaLFoLders Dim targetFolder Dim >>>> folderExists'87 targetFolder = "C:\\Documents and Settings\\All >>>> Users\\Documents\\TrailerManagement and Manifest" folderExists = >>>> FSO.FolderExists(targetFolder)'90 if folderExists = False then >>>> fso.CreateFolder targetFolder end if targetFolder = >>>> "C:\\Documents and Settings\\AllUsers\\Documents\\Trailer Management >>>> and >>>> Manifest\\Application"'95 folderExists = >>>> FSO.FolderExists(targetFolder) if folderExists = False then >>>> fso.CreateFolder targetFolder end >>>> ifend sub'103sub createShortcut aSplit = split(wscript.scriptname, ".") >>>> if Not fso.fileExists (aSplit(0) & ".lnk") then set newShortcut = >>>> wshShell.createShortcut (strServerSourceFolder & "\\" &aSplit(0) & >>>> ".lnk") newShortcut.TargetPath = WScript.ScriptFullName >>>> newShortCut.WorkingDirectory = strServerSourceFolder >>>> newShortcut.IconLocation = "C:\Program >>>> Files\MicrosoftOffice\OFFICE11\\msaccess.exe, 0" >>>> newShortCut.Description = "Start Trailer Management and >>>> ManifestDatabase" newShortCut.Save end ifend sub"Petr Danes" >>>> <skru.spammers(a)hotmail.com> wrote in >>>> messagenews:%23BP%23rfyjKHA.1540(a)TK2MSFTNGP06.phx.gbl...> Most >>>> excellent, Bob. Thankyouthankyouthankyou, that's exactly it. May >>>> Iask where you found this?>> And no, it's not anything like you >>>> describe. It's several copies of asingle-machine, single-user, mostly >>>> read-only database, running on variouscomputers in a library >>>> department. I >>>> want the copies to occasionallyinterrogate the master copy, but in a >>>> very non-intrusive way. The mastermachine may be shut down (it's not a >>>> server), or the db in use and I don'twant the users being pestered >>>> with error messages, or to have to wait fortimeouts. The intention here >>>> is for the db copies to spawn an invisiblescript process that quietly >>>> checks for certain conditions in the mastercopy, and only if all >>>> conditions are met, does it inform the user that anewer version is >>>> available and ask if it should be copied over. And amaximum of >>>> once per day - I don't want the users to be pestered by thisdialog >>>> every >>>> time they start the db to look up something. If the properconditions do >>>> not exist, or if the update dialog has already been dismissedonce that >>>> day, the script process will simply close and the user shouldnever even >>>> know that such an event took place. The data are notmission-critical or >>>> time-sensitive in any way - an update of once or twiceper month >>>> should be more than adequate.>> Pete>>>> "Bob Barrows" >>>> <reb01501(a)NOyahoo.SPAMcom> p�e v diskusn�m >>>> p��sp�vkunews:ug20oTxjKHA.1824(a)TK2MSFTNGP04.phx.gbl...>> Petr Danes >>>> wrote:>>> I use the following code in VBA in an Access database to >>>> retrieve a>>> custom database property:>>>>>> >>>> currentdb.Containers(1).Documents("Uzivatelske").Properties("Version")>>>>>> >>>> I would like to get this value via VBScript without opening the>>> >>>> database. I have found many tutorials and examples on how to >>>> retrieve>>> values form tables, but this is different and none of my >>>> attempts>>> have met with any success. I have tried connections,>>>> >>>> You mean >>>> ADO connections? That will not work. This collection is not>> >>>> available for >>>>>>>>> objAcc.OpenCurrentDatabase>>>> No that won't help. You need to >>>> use DAO. Start by creating an instance>> of the DBEngine:>>>> Set >>>> DE = CreateObject("DAO.DBEngine.36")>>>> Then open your >>>> database:>> Set db = DE.OpenDatabase("C:\Program Files\Microsoft" >>>> & _>> "Office\Office\Samples\Northwind.mdb")>>>> Then go after the >>>> collection:>> msgbox >>>> db.Containers(1).Documents("Uzivatelske").Properties("Version")>>>> >>>> PS. I hope you are not doing this in ASP server-side code. DAO is>> >>>> single-threaded so it will make your ASP application >>>> thread-bound,>> impairing performance. Unfortunately, DAO is the only >>>> way to get >>>> at>> those properties.>>>> -->> HTH,>> Bob Barrows>>>>>>> -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |