Get custom database properties from an Access database
in [VbScript]
|
Prev: kill process
Next: split question
From: David C. Holley on 9 Jan 2010 11:29 At one point, I was going to suggest using startup as the trigger to run the script. If you're concerned about another drive letter causing confusion, you could go with mapping the drive, copying the file and then unmapping it. Or if you are wanting it to work in the back ground, I would go with having the users open the .mdb directly and then set up Task Scheduler to run the VBScript at a specific time during the day - perhaps at Noon. You would then need to present the user with a message box that advises them that a new version is available and informing them to exit out of Access for it to be downloaded. The msgbox would include a YES/NO to download it. Alternatively, the VBScript kicked off by Task Scheduler could copy the new file to an installation folder on the local drive. A separate script that runs at startup would then check the installation folder for the .mdb file and if it exists move the .mdb file to the folder where you're keeping the local copy. Thus the user should be able to operate without seeing any of the work of the VBScript at all. "Petr Danes" <skru.spammers(a)hotmail.com> wrote in message news:u%23GW$4RkKHA.2188(a)TK2MSFTNGP04.phx.gbl... > Hi David, > > those delays are exactly what I'm working to avoid. It doesn't matter if > the VBScript process drags, since it is an independent process and so will > have no effect on the user's interaction with the database. The user can > skip merrily along, doing whatever he needs while the script invisibly > figures out the current situation in the background and decides what to do > about it. I had thought of having it display a dialog only if everything > is correct for copying a new version, but your idea of a flag somewhere is > not bad, either. Maybe I'll automate the entire process and not even > bother asking the user, just quietly copy everything over whenever the > master machine is available and have the copy simply switch linked tables > on next startup. > > I mapped a drive letter on one machine, just to see how well it would > work. It does, but I've run into issues other places on this same network, > where users needed a drive letter to a server. The 'reconnect at next > login' flag doesn't work, even with servers that run continuously. No idea > why, I suspect that it's because there's no machine in the network running > WINS or some similar service (don't get me started in the IT department > here; shrugs, blank stares and mumbled "I dunno" is about as good as it > gets) and so a machine just waking up isn't able to figure out the > necessary connections. In any case, what I had to do was create a script > that goes in the 'Startup' folder and creates the mapped network drive > letter on every start. > > I could do something similar here, but it seems a waste - nobody needs > this access to the master machine besides my database (or script) and a > new drive letter might be confusing, especially since the creation process > would not always work (machine down). It would probably be just as easy to > use \\Masek\Hyper\ as a path. > > Thanks for the links, I'll study those and see I can gin up. > > Pete > > > > "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku > news:eQAlsVMkKHA.4912(a)TK2MSFTNGP02.phx.gbl... >> http://blogs.technet.com/heyscriptingguy/archive/2004/10/01/how-can-i-write-to-the-comments-field-of-a-document.aspx >> >> This could be used as a flag to determine if the script should look for a >> new file. You would write today's date to the comments field today. >> Tommorrow, the script would see that the date doesn't match the current >> date and then go looking. If the file isn't found, the comments field >> would be updated to today's date anyway thus causing the script to skip >> looking for the machine at the next startup on the same day. >> >> "David C. Holley" <David.C.Holley> wrote in message >> news:u01cNMMkKHA.1540(a)TK2MSFTNGP06.phx.gbl... >>> Yeowsers that does sound like a mess. In all likelihood, the VBScript >>> would experience the same delay as code in Access when either reaches >>> out to look for the PC. You should be aware that if you go the Access >>> route, you're users *will* still experience a delay as the code goes >>> looking for the machine. Access is not multi-threaded. It can only do >>> one thing at a time. At least with the VBScript they'll be presented >>> with information as to what's going on. Plus if they leave it open, they >>> won't have to go through the wait of looking for the machine. >>> >>> On the other hand, you could set up the VBScript to look at the >>> properties of .mdb file on the local machine and use that as the basis >>> as to whether or not you need to go looking for the other machine. >>> >>> How exactly are you connecting the PC's together? Are you mapping a >>> drive to the other machine? If so, I think that there's function in >>> VBScript that can check if the drive is connected before trying to >>> access it. >>> >>> "Petr Danes" <skru.spammers(a)hotmail.com> wrote in message >>> news:%23ZMKJvLkKHA.4380(a)TK2MSFTNGP04.phx.gbl... >>>> Hi David, >>>> >>>> You're right, I could start a local copy only if the master is >>>> unavailable. But wouldn't the users still experience quite a long delay >>>> when the master machine is shut down? Browsing for machines on the >>>> network, sometimes I encounter delays of up to thirty seconds or so, >>>> before Windows Explorer decides that a previously visible machine is >>>> not going to respond. I don't want that kind of hang up on every start. >>>> I put quite a bit of effort into making this app start and respond >>>> quickly and I don't want the users to start bitching that it's slowing >>>> them down. >>>> >>>> As for the server, yes, that would definitely be the way to go. It's >>>> not hard to find technologically better approaches than what I have >>>> here, but I'm limited by several things. There is no server for the >>>> library, to begin with. Space on another one could certainly be found, >>>> but the department head is mired in an Austro-Hungarian bureaucratic >>>> mentality and wants to keep 'his' data on 'his' machine, to prevent >>>> evil hacker types from getting at it. It's actually not such a baseless >>>> concern, some of the data could potentially be used by thieves who >>>> specialize in old manuscripts, and this network gets attacked >>>> constantly and has been breached a few times. It was years before he >>>> even allowed his computer to be connected to the institution's >>>> network - for a long time he had his own little world on his desk and >>>> went to a coworker's machine for email and Internet access. >>>> >>>> He's finally gotten linked up and allowed me to do this, but I don't >>>> want to spend all my time battling network access issues with him. And >>>> the data is actually fairly safe the way it is. The source data is a >>>> bunch of text files maintained in an ancient DOS text editor with its >>>> own special format (another thing that got me this contract - no >>>> generic software today can read this stuff and he refuses to give up >>>> his beloved old editor, so I got a jump by writing conversion software >>>> that allows him to keep using it). My database app reads and indexes >>>> the contents of these several thousand text files, then performs >>>> specialized searches through them. For all his reactionary mentality, >>>> he does understand the importance of backing up data, and has copies of >>>> these text files squirrelled away all over the place, including at >>>> home, off site. If my database gets hosed, or even the entire machine >>>> stolen, it doesn't matter, it's only a snapshot of the actual data and >>>> can be rebuilt any time it's necessary. Drop a clean new copy of the db >>>> and text files onto any computer with Access installed, run the >>>> indexing process and you're back in business. >>>> >>>> Pete >>>> >>>> >>>> >>>> "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku >>>> news:uFSzEPKkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>>> 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: Petr Danes on 12 Jan 2010 05:15 > At one point, I was going to suggest using startup > as the trigger to run the script. That would likely work, but some of the users may go for days, or weeks even, without using the database. Some will use it often, others rarely, so it makes sense to me to have it update only when being used. > If you're concerned about another drive letter > causing confusion, you could go with mapping the > drive, copying the file and then unmapping it. If I have to map a letter , I will probably do it that way, in fact, I will write the script to look for an available letter, rather than use a fixed one, to prevent possible collisions with some later-installed stuff. I have to see if I can do it without a letter, simply use the UNC path. But the connection will require a username and password, I'm not yet sure of the syntax, whether I can do that with only a UNC path, or if I have to map a drive letter. > Or if you are wanting it to work in the back > ground,. All good ideas, thank you, but I think I will stick with having the DB spawn a process. It seems the simplest approach, and it will be completely under the control of my db, which I can adjust in code any time and install in the master copy for automatic dispersal, rather than having to manually set up scheduled tasks, install start-up scripts and the like on all user machines. Appreciate all the input, I think I have a pretty clear road ahead now. Pete "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku news:OxVv6jUkKHA.3476(a)TK2MSFTNGP06.phx.gbl... > At one point, I was going to suggest using startup as the trigger to run > the script. If you're concerned about another drive letter causing > confusion, you could go with mapping the drive, copying the file and then > unmapping it. > > Or if you are wanting it to work in the back ground, I would go with > having the users open the .mdb directly and then set up Task Scheduler to > run the VBScript at a specific time during the day - perhaps at Noon. You > would then need to present the user with a message box that advises them > that a new version is available and informing them to exit out of Access > for it to be downloaded. The msgbox would include a YES/NO to download it. > > Alternatively, the VBScript kicked off by Task Scheduler could copy the > new file to an installation folder on the local drive. A separate script > that runs at startup would then check the installation folder for the .mdb > file and if it exists move the .mdb file to the folder where you're > keeping the local copy. Thus the user should be able to operate without > seeing any of the work of the VBScript at all. > > "Petr Danes" <skru.spammers(a)hotmail.com> wrote in message > news:u%23GW$4RkKHA.2188(a)TK2MSFTNGP04.phx.gbl... >> Hi David, >> >> those delays are exactly what I'm working to avoid. It doesn't matter if >> the VBScript process drags, since it is an independent process and so >> will have no effect on the user's interaction with the database. The user >> can skip merrily along, doing whatever he needs while the script >> invisibly figures out the current situation in the background and decides >> what to do about it. I had thought of having it display a dialog only if >> everything is correct for copying a new version, but your idea of a flag >> somewhere is not bad, either. Maybe I'll automate the entire process and >> not even bother asking the user, just quietly copy everything over >> whenever the master machine is available and have the copy simply switch >> linked tables on next startup. >> >> I mapped a drive letter on one machine, just to see how well it would >> work. It does, but I've run into issues other places on this same >> network, where users needed a drive letter to a server. The 'reconnect at >> next login' flag doesn't work, even with servers that run continuously. >> No idea why, I suspect that it's because there's no machine in the >> network running WINS or some similar service (don't get me started in the >> IT department here; shrugs, blank stares and mumbled "I dunno" is about >> as good as it gets) and so a machine just waking up isn't able to figure >> out the necessary connections. In any case, what I had to do was create a >> script that goes in the 'Startup' folder and creates the mapped network >> drive letter on every start. >> >> I could do something similar here, but it seems a waste - nobody needs >> this access to the master machine besides my database (or script) and a >> new drive letter might be confusing, especially since the creation >> process would not always work (machine down). It would probably be just >> as easy to use \\Masek\Hyper\ as a path. >> >> Thanks for the links, I'll study those and see I can gin up. >> >> Pete >> >> >> >> "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku >> news:eQAlsVMkKHA.4912(a)TK2MSFTNGP02.phx.gbl... >>> http://blogs.technet.com/heyscriptingguy/archive/2004/10/01/how-can-i-write-to-the-comments-field-of-a-document.aspx >>> >>> This could be used as a flag to determine if the script should look for >>> a new file. You would write today's date to the comments field today. >>> Tommorrow, the script would see that the date doesn't match the current >>> date and then go looking. If the file isn't found, the comments field >>> would be updated to today's date anyway thus causing the script to skip >>> looking for the machine at the next startup on the same day. >>> >>> "David C. Holley" <David.C.Holley> wrote in message >>> news:u01cNMMkKHA.1540(a)TK2MSFTNGP06.phx.gbl... >>>> Yeowsers that does sound like a mess. In all likelihood, the VBScript >>>> would experience the same delay as code in Access when either reaches >>>> out to look for the PC. You should be aware that if you go the Access >>>> route, you're users *will* still experience a delay as the code goes >>>> looking for the machine. Access is not multi-threaded. It can only do >>>> one thing at a time. At least with the VBScript they'll be presented >>>> with information as to what's going on. Plus if they leave it open, >>>> they won't have to go through the wait of looking for the machine. >>>> >>>> On the other hand, you could set up the VBScript to look at the >>>> properties of .mdb file on the local machine and use that as the basis >>>> as to whether or not you need to go looking for the other machine. >>>> >>>> How exactly are you connecting the PC's together? Are you mapping a >>>> drive to the other machine? If so, I think that there's function in >>>> VBScript that can check if the drive is connected before trying to >>>> access it. >>>> >>>> "Petr Danes" <skru.spammers(a)hotmail.com> wrote in message >>>> news:%23ZMKJvLkKHA.4380(a)TK2MSFTNGP04.phx.gbl... >>>>> Hi David, >>>>> >>>>> You're right, I could start a local copy only if the master is >>>>> unavailable. But wouldn't the users still experience quite a long >>>>> delay when the master machine is shut down? Browsing for machines on >>>>> the network, sometimes I encounter delays of up to thirty seconds or >>>>> so, before Windows Explorer decides that a previously visible machine >>>>> is not going to respond. I don't want that kind of hang up on every >>>>> start. I put quite a bit of effort into making this app start and >>>>> respond quickly and I don't want the users to start bitching that it's >>>>> slowing them down. >>>>> >>>>> As for the server, yes, that would definitely be the way to go. It's >>>>> not hard to find technologically better approaches than what I have >>>>> here, but I'm limited by several things. There is no server for the >>>>> library, to begin with. Space on another one could certainly be found, >>>>> but the department head is mired in an Austro-Hungarian bureaucratic >>>>> mentality and wants to keep 'his' data on 'his' machine, to prevent >>>>> evil hacker types from getting at it. It's actually not such a >>>>> baseless concern, some of the data could potentially be used by >>>>> thieves who specialize in old manuscripts, and this network gets >>>>> attacked constantly and has been breached a few times. It was years >>>>> before he even allowed his computer to be connected to the >>>>> institution's network - for a long time he had his own little world on >>>>> his desk and went to a coworker's machine for email and Internet >>>>> access. >>>>> >>>>> He's finally gotten linked up and allowed me to do this, but I don't >>>>> want to spend all my time battling network access issues with him. And >>>>> the data is actually fairly safe the way it is. The source data is a >>>>> bunch of text files maintained in an ancient DOS text editor with its >>>>> own special format (another thing that got me this contract - no >>>>> generic software today can read this stuff and he refuses to give up >>>>> his beloved old editor, so I got a jump by writing conversion software >>>>> that allows him to keep using it). My database app reads and indexes >>>>> the contents of these several thousand text files, then performs >>>>> specialized searches through them. For all his reactionary mentality, >>>>> he does understand the importance of backing up data, and has copies >>>>> of these text files squirrelled away all over the place, including at >>>>> home, off site. If my database gets hosed, or even the entire machine >>>>> stolen, it doesn't matter, it's only a snapshot of the actual data and >>>>> can be rebuilt any time it's necessary. Drop a clean new copy of the >>>>> db and text files onto any computer with Access installed, run the >>>>> indexing process and you're back in business. >>>>> >>>>> Pete >>>>> >>>>> >>>>> >>>>> "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku >>>>> news:uFSzEPKkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>>>> 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: David C. Holley on 14 Jan 2010 00:11
The process would run when they boot up which is an entirely acceptable approach and consistent with organizations that 'push' out updates to the clients on their networks. "Petr Danes" <skruspammers(a)no.spam> wrote in message news:%236u%23nA3kKHA.2160(a)TK2MSFTNGP02.phx.gbl... >> At one point, I was going to suggest using startup > >> as the trigger to run the script. > > > > That would likely work, but some of the users may go for days, or weeks > even, without using the database. Some will use it often, others rarely, > so it makes sense to me to have it update only when being used. > > > > > >> If you're concerned about another drive letter > >> causing confusion, you could go with mapping the > >> drive, copying the file and then unmapping it. > > > > If I have to map a letter , I will probably do it that way, in fact, I > will write the script to look for an available letter, rather than use a > fixed one, to prevent possible collisions with some later-installed stuff. > I have to see if I can do it without a letter, simply use the UNC path. > But the connection will require a username and password, I'm not yet sure > of the syntax, whether I can do that with only a UNC path, or if I have to > map a drive letter. > > > > > >> Or if you are wanting it to work in the back > >> ground,. > > > > All good ideas, thank you, but I think I will stick with having the DB > spawn a process. It seems the simplest approach, and it will be completely > under the control of my db, which I can adjust in code any time and > install in the master copy for automatic dispersal, rather than having to > manually set up scheduled tasks, install start-up scripts and the like on > all user machines. > > > > Appreciate all the input, I think I have a pretty clear road ahead now. > > > > Pete > > > > > > > > "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku > news:OxVv6jUkKHA.3476(a)TK2MSFTNGP06.phx.gbl... >> At one point, I was going to suggest using startup as the trigger to run >> the script. If you're concerned about another drive letter causing >> confusion, you could go with mapping the drive, copying the file and then >> unmapping it. >> >> Or if you are wanting it to work in the back ground, I would go with >> having the users open the .mdb directly and then set up Task Scheduler to >> run the VBScript at a specific time during the day - perhaps at Noon. You >> would then need to present the user with a message box that advises them >> that a new version is available and informing them to exit out of Access >> for it to be downloaded. The msgbox would include a YES/NO to download >> it. >> >> Alternatively, the VBScript kicked off by Task Scheduler could copy the >> new file to an installation folder on the local drive. A separate script >> that runs at startup would then check the installation folder for the >> .mdb file and if it exists move the .mdb file to the folder where you're >> keeping the local copy. Thus the user should be able to operate without >> seeing any of the work of the VBScript at all. >> >> "Petr Danes" <skru.spammers(a)hotmail.com> wrote in message >> news:u%23GW$4RkKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>> Hi David, >>> >>> those delays are exactly what I'm working to avoid. It doesn't matter if >>> the VBScript process drags, since it is an independent process and so >>> will have no effect on the user's interaction with the database. The >>> user can skip merrily along, doing whatever he needs while the script >>> invisibly figures out the current situation in the background and >>> decides what to do about it. I had thought of having it display a dialog >>> only if everything is correct for copying a new version, but your idea >>> of a flag somewhere is not bad, either. Maybe I'll automate the entire >>> process and not even bother asking the user, just quietly copy >>> everything over whenever the master machine is available and have the >>> copy simply switch linked tables on next startup. >>> >>> I mapped a drive letter on one machine, just to see how well it would >>> work. It does, but I've run into issues other places on this same >>> network, where users needed a drive letter to a server. The 'reconnect >>> at next login' flag doesn't work, even with servers that run >>> continuously. No idea why, I suspect that it's because there's no >>> machine in the network running WINS or some similar service (don't get >>> me started in the IT department here; shrugs, blank stares and mumbled >>> "I dunno" is about as good as it gets) and so a machine just waking up >>> isn't able to figure out the necessary connections. In any case, what I >>> had to do was create a script that goes in the 'Startup' folder and >>> creates the mapped network drive letter on every start. >>> >>> I could do something similar here, but it seems a waste - nobody needs >>> this access to the master machine besides my database (or script) and a >>> new drive letter might be confusing, especially since the creation >>> process would not always work (machine down). It would probably be just >>> as easy to use \\Masek\Hyper\ as a path. >>> >>> Thanks for the links, I'll study those and see I can gin up. >>> >>> Pete >>> >>> >>> >>> "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku >>> news:eQAlsVMkKHA.4912(a)TK2MSFTNGP02.phx.gbl... >>>> http://blogs.technet.com/heyscriptingguy/archive/2004/10/01/how-can-i-write-to-the-comments-field-of-a-document.aspx >>>> >>>> This could be used as a flag to determine if the script should look for >>>> a new file. You would write today's date to the comments field today. >>>> Tommorrow, the script would see that the date doesn't match the current >>>> date and then go looking. If the file isn't found, the comments field >>>> would be updated to today's date anyway thus causing the script to skip >>>> looking for the machine at the next startup on the same day. >>>> >>>> "David C. Holley" <David.C.Holley> wrote in message >>>> news:u01cNMMkKHA.1540(a)TK2MSFTNGP06.phx.gbl... >>>>> Yeowsers that does sound like a mess. In all likelihood, the VBScript >>>>> would experience the same delay as code in Access when either reaches >>>>> out to look for the PC. You should be aware that if you go the Access >>>>> route, you're users *will* still experience a delay as the code goes >>>>> looking for the machine. Access is not multi-threaded. It can only do >>>>> one thing at a time. At least with the VBScript they'll be presented >>>>> with information as to what's going on. Plus if they leave it open, >>>>> they won't have to go through the wait of looking for the machine. >>>>> >>>>> On the other hand, you could set up the VBScript to look at the >>>>> properties of .mdb file on the local machine and use that as the basis >>>>> as to whether or not you need to go looking for the other machine. >>>>> >>>>> How exactly are you connecting the PC's together? Are you mapping a >>>>> drive to the other machine? If so, I think that there's function in >>>>> VBScript that can check if the drive is connected before trying to >>>>> access it. >>>>> >>>>> "Petr Danes" <skru.spammers(a)hotmail.com> wrote in message >>>>> news:%23ZMKJvLkKHA.4380(a)TK2MSFTNGP04.phx.gbl... >>>>>> Hi David, >>>>>> >>>>>> You're right, I could start a local copy only if the master is >>>>>> unavailable. But wouldn't the users still experience quite a long >>>>>> delay when the master machine is shut down? Browsing for machines on >>>>>> the network, sometimes I encounter delays of up to thirty seconds or >>>>>> so, before Windows Explorer decides that a previously visible machine >>>>>> is not going to respond. I don't want that kind of hang up on every >>>>>> start. I put quite a bit of effort into making this app start and >>>>>> respond quickly and I don't want the users to start bitching that >>>>>> it's slowing them down. >>>>>> >>>>>> As for the server, yes, that would definitely be the way to go. It's >>>>>> not hard to find technologically better approaches than what I have >>>>>> here, but I'm limited by several things. There is no server for the >>>>>> library, to begin with. Space on another one could certainly be >>>>>> found, but the department head is mired in an Austro-Hungarian >>>>>> bureaucratic mentality and wants to keep 'his' data on 'his' machine, >>>>>> to prevent evil hacker types from getting at it. It's actually not >>>>>> such a baseless concern, some of the data could potentially be used >>>>>> by thieves who specialize in old manuscripts, and this network gets >>>>>> attacked constantly and has been breached a few times. It was years >>>>>> before he even allowed his computer to be connected to the >>>>>> institution's network - for a long time he had his own little world >>>>>> on his desk and went to a coworker's machine for email and Internet >>>>>> access. >>>>>> >>>>>> He's finally gotten linked up and allowed me to do this, but I don't >>>>>> want to spend all my time battling network access issues with him. >>>>>> And the data is actually fairly safe the way it is. The source data >>>>>> is a bunch of text files maintained in an ancient DOS text editor >>>>>> with its own special format (another thing that got me this >>>>>> contract - no generic software today can read this stuff and he >>>>>> refuses to give up his beloved old editor, so I got a jump by writing >>>>>> conversion software that allows him to keep using it). My database >>>>>> app reads and indexes the contents of these several thousand text >>>>>> files, then performs specialized searches through them. For all his >>>>>> reactionary mentality, he does understand the importance of backing >>>>>> up data, and has copies of these text files squirrelled away all over >>>>>> the place, including at home, off site. If my database gets hosed, or >>>>>> even the entire machine stolen, it doesn't matter, it's only a >>>>>> snapshot of the actual data and can be rebuilt any time it's >>>>>> necessary. Drop a clean new copy of the db and text files onto any >>>>>> computer with Access installed, run the indexing process and you're >>>>>> back in business. >>>>>> >>>>>> Pete >>>>>> >>>>>> >>>>>> >>>>>> "David C. Holley" <David.C.Holley> p�e v diskusn�m p��sp�vku >>>>>> news:uFSzEPKkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>>>>> 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>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >> >> > > |