#84898 - 2002-01-23 07:42 PM
Re: Create/Read/Update MS Access Database
|
Breaker
Hey THIS is FUN
Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
|
Question for Shawn,What are the constraints of the code you've demonstrated above? I can pretty much see how that works, but how much differentiation between my desktops would it take into account without any modification? I'm dealing with 95/98/Me/NT4.0/2000 on the desktop, most of which run Access 97 - but some will have Access 2000, and some won't have Access at all. Do I need a consistent set of components (ADO, Access, MDAC, etc) on each desktop? I ask because while testing the script on an NT4.0 box with Access 97 (I'm working on Win2k, Office2k) the $Connection.Open() method call fails. Should there be a different provider specified in the $DSN variable? Or am I being incredibly stupid? (It wouldn't be the first time...) Yours increasingly-frustratedly, Breaker
_________________________
================================================ Breaker
|
Top
|
|
|
|
#84900 - 2002-01-23 08:13 PM
Re: Create/Read/Update MS Access Database
|
Breaker
Hey THIS is FUN
Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
|
Shawn,Wow! Quick draw response - thanks. In answer to your queries: 1. ADODB.Connection is present in the registry. 2. On my NT box there is no Access DSN specified. So I have created one - but I am still getting the same error. I'm not really sure where the problem might lie. Any ideas? Thanks in advance, Breaker
_________________________
================================================ Breaker
|
Top
|
|
|
|
#84903 - 2002-01-24 03:26 PM
Re: Create/Read/Update MS Access Database
|
Breaker
Hey THIS is FUN
Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
|
Shawn,The error message receieved when executing the Open() method is: COM Exception error "Open" (ADODB.Connection - ADO could not find the specified provider.) [1/1] I've checked the registry, and although I can find ADODB.Connection under HKCR, there's no sign of the Microsoft.Jet.OLEDB.4.0, only Microsoft.Jet.OLEDB.3.51. I think this is the difference between Access versions installed. Testing with the new value for the $DSN you suggested gives the error: COM exception error "Open" (Microsoft OLE DB Provider for ODBC Drivers - [Microsoft][ODBC Microsoft Access 97 Driver] Can't open database '(unknown)'. It may not be a database that your application recognises, or the file may be corrupt.)[1/1] However, upgrading the test system to Access 2000 allows both the suggested values for $DSN to work OK. I suspect the problem is that the native database format for Access 2000 is not recognised by Access 97. So, recreating the database in Access 97 and using the native Microsoft Access Driver DSN is doing the trick. At least now all my workstations (with Access installed) will write to the database. Now I need to test what components I should install on my non-Access workstations to allow them to write info as well, I'm not even sure if this will be possible. Before I came across this thread I was planning to run a scheduled script daily or weekly to parse the text files that my current production script generates, and import that into a database. Obviously I have been seduced by the COM/OLE Dark Side ("You don't know the power of the Dark Side!") and would prefer to write directly to the database and cut out the text files altogether, but this may require a degree of sophistication that I fear is beyond me. At least I'm getting somewhere now - many many thanks go to Shawn for his patience and persistence with us lesser mortals. When I finally get this finished, I'll post the whole thing here for all to see and deride at their leisure. Hopefully that'll be next week, if I don't get swamped by other stuff. A Bientot, mes amis!
_________________________
================================================ Breaker
|
Top
|
|
|
|
#84904 - 2002-02-05 02:06 PM
Re: Create/Read/Update MS Access Database
|
Breaker
Hey THIS is FUN
Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
|
Well, it took long enough (the suspected swamping by other stuff did indeed take place!) but here is the final script that is now happily collecting info from my network and merrily keeping my inventory database right up-to-the-minute.In the end I decided to start playing with WMI as well to pull info, and credit goes to Radimus and Kent for the WMIQuery UDF, which gets a fair outing. I have since expanded the working version to pick up some other WMI info, but this is the basic code. Hopefully it'll save someone else from the same headbanging that a few people seem to have gone through in pursuit of a similar outcome... Shawn and others - many thanks for all your advice on this. I'm sure I'll be back soon - AD is creeping nearer! Breaker code:
;WMI script to write info to database at logon ;Created Feb 2002 by Neil MoranBreak On CLS ;Time script execution ;? @TIME ;Query WMI to obtain info not available through standard KiXtart macros ;Many thanks to the authors of the WMIQuery UDF for this - two gents known as Radimus and Kent FUNCTION WMIQuery($what,$where,) dim $strQuery, $objEnumerator, $value $strQuery = "Select $what From $where" $SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//@WKSTA") $objEnumerator = $SystemSet.ExecQuery($strQuery) For Each $objInstance in $objEnumerator If @Error = 0 and $objInstance <> "" $x=execute("$$value = $$objInstance.$what") $WMIQuery="$value"+"|"+"$WMIQuery" EndIf Next $WMIQuery=left($WMIQuery,len($WMIQuery)-1) exit @error ENDFUNCTION ;Retrieve info by calls to WMIQuery() function $SystemManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem") $SystemModel = WMIQuery("Model","Win32_ComputerSystem") $SerialNo = WMIQuery("SerialNumber","Win32_BIOS") $PhysicalMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024 $ProcessorSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor") + " Mhz" $PagefileSpace = val(WMIQuery("TotalPageFileSpace","Win32_LogicalMemoryConfiguration"))/1024 ;Enumerate all disk info into arrays, then cross-reference for required info $arrDeviceIDs = Split(WMIQuery("DeviceID","Win32_LogicalDisk"),"|",-1) $arrFreeSpaces = Split(WMIQuery("FreeSpace","Win32_LogicalDisk"),"|",-1) $arrTotalSizes = Split(WMIQuery("Size","Win32_LogicalDisk"),"|",-1) $arrFormats = Split(WMIQuery("FileSystem","Win32_LogicalDisk"),"|",-1) For $Counter = 0 To UBound($arrDeviceIDs) If $arrDeviceIDs[$Counter] = "C:" $C_DriveFormat = $arrFormats[$Counter] $C_DriveFreeSpace = $arrFreeSpaces[$Counter] $C_DriveTotalSize = $arrTotalSizes[$Counter] $Counter = UBound($arrDeviceIDs) + 1 Endif Next ;Sort the returned disk spaces values into MB ;$C_DriveFreeSpace = Val($C_DriveFreeSpace)/1048576 ;$C_DriveTotalSize = Val($C_DriveTotalSize)/1048576 ;First, set variables for the connection to the database and other general options $=SetOption("WrapAtEOL","On") $DATABASE = "\\corpfs01\rollout$$\logon.mdb" $DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE" ;Create SQL statements to check/write/update database $CHECK_ENTRY_USERS = "SELECT * FROM TBL_USERS WHERE USERNAME='@USERID';" $CHECK_ENTRY_COMPUTERS = "SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='@WKSTA';" $Connection = CreateObject("ADODB.Connection") $Command = CreateObject("ADODB.Command") $Recordset = CreateObject("ADODB.Recordset") ;Check for connection object if $Connection ;Open connection to database $Connection.ConnectionString = $DSN $Connection.Open() ;Check for existing records for the current workstation/user ;Add new record or update existing as required ;Check for User details first $Command.ActiveConnection = $Connection $Recordset.CursorType = 3 $Recordset.LockType = 3 $Recordset.ActiveCommand = $Command $Command.CommandText = $CHECK_ENTRY_USERS $Recordset.Open($Command) ;Create new record if none exists to update If $Recordset.RecordCount < 1 $Recordset.AddNew Endif ;Write user values into database field by field $Recordset.Fields("UserName").Value = @USERID $Recordset.Fields("FullName").Value = @FULLNAME $Recordset.Fields("Workstation").Value = @WKSTA $Recordset.Fields("PrivilegeLevel").Value = @PRIV $Recordset.Fields("HomeDrive").Value = @HOMESHR $Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME ;Update the new record and close the recordset object $Recordset.Update $Recordset.Close() ;Check Computer details next - refresh $Recordset object with new query $Command.CommandText = $CHECK_ENTRY_COMPUTERS $Recordset.Open($Command) ;Check for existing record to update If $Recordset.RecordCount < 1 $Recordset.AddNew Endif ;Write values into table $Recordset.Fields("Workstation").Value = @WKSTA $Recordset.Fields("NTDomain").Value = @DOMAIN $Recordset.Fields("SystemManufacturer").Value = $SystemManufacturer $Recordset.Fields("SystemModel").Value = $SystemModel $Recordset.Fields("SerialNo").Value = $SerialNo $Recordset.Fields("IPAddress").Value = @IPADDRESS0 $Recordset.Fields("MACAddress").Value = @ADDRESS $Recordset.Fields("OS").Value = @PRODUCTTYPE $Recordset.Fields("ServicePack").Value = @CSD $Recordset.Fields("PhysicalMemory").Value = $PhysicalMemory $Recordset.Fields("ProcessorSpeed").Value = $ProcessorSpeed $Recordset.Fields("PagefileSpace").Value = $PagefileSpace $Recordset.Fields("C riveFormat").Value = $C_DriveFormat $Recordset.Fields("C riveFreeSpace").Value = $C_DriveFreeSpace $Recordset.Fields("C riveTotalSize").Value = $C_DriveTotalSize $Recordset.Fields("LoggedOnUser").Value = @USERID $Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME ;Update and close recordset object $Recordset.Update $Recordset.Close() ;Close connection to database $Connection.Close() ;Tidy up by releasing COM objects from memory $Connection = 0 $Recordset = 0 $Command = 0 else Goto error endif :end ;? @TIME exit 321 :error exit
Apologies if this isn't readable.
_________________________
================================================ Breaker
|
Top
|
|
|
|
#84911 - 2002-02-05 06:35 PM
Re: Create/Read/Update MS Access Database
|
Radimus
Moderator
Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
|
basically it is your code almost verbatim... with the DB name changed to protect the innocent. I figured once I make it work then I can customize it.code:
Break On;Retrieve info by calls to WMIQuery() function $SystemManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem") $SystemModel = WMIQuery("Model","Win32_ComputerSystem") $SerialNo = WMIQuery("SerialNumber","Win32_BIOS") $PhysicalMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024 $ProcessorSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor") + " Mhz" $PagefileSpace = val(WMIQuery("TotalPageFileSpace","Win32_LogicalMemoryConfiguration"))/1024 ;Enumerate all disk info into arrays, then cross-reference for required info $arrDeviceIDs = Split(WMIQuery("DeviceID","Win32_LogicalDisk"),"|",-1) $arrFreeSpaces = Split(WMIQuery("FreeSpace","Win32_LogicalDisk"),"|",-1) $arrTotalSizes = Split(WMIQuery("Size","Win32_LogicalDisk"),"|",-1) $arrFormats = Split(WMIQuery("FileSystem","Win32_LogicalDisk"),"|",-1) For $Counter = 0 To UBound($arrDeviceIDs) If $arrDeviceIDs[$Counter] = "C:" $C_DriveFormat = $arrFormats[$Counter] $C_DriveFreeSpace = $arrFreeSpaces[$Counter] $C_DriveTotalSize = $arrTotalSizes[$Counter] $Counter = UBound($arrDeviceIDs) + 1 Endif Next ;Sort the returned disk spaces values into MB ;$C_DriveFreeSpace = Val($C_DriveFreeSpace)/1048576 ;$C_DriveTotalSize = Val($C_DriveTotalSize)/1048576 ;First, set variables for the connection to the database and other general options $=SetOption("WrapAtEOL","On") $DATABASE = "$logon\inventory\inventory.mdb" $DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE" ;Create SQL statements to check/write/update database $CHECK_ENTRY_USERS = "SELECT * FROM TBL_USERS WHERE USERNAME='@USERID';" $CHECK_ENTRY_COMPUTERS = "SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='@WKSTA';" $Connection = CreateObject("ADODB.Connection") $Command = CreateObject("ADODB.Command") $Recordset = CreateObject("ADODB.Recordset") ;Check for connection object if $Connection ? "i'm in there" ;Open connection to database $Connection.ConnectionString = $DSN $Connection.Open() ;Check for existing records for the current workstation/user ;Add new record or update existing as required ;Check for User details first $Command.ActiveConnection = $Connection $Recordset.CursorType = 3 $Recordset.LockType = 3 $Recordset.ActiveCommand = $Command $Command.CommandText = $CHECK_ENTRY_USERS $Recordset.Open($Command) ;Create new record if none exists to update If $Recordset.RecordCount < 1 ? "adding new user record" $Recordset.AddNew Endif ;Write user values into database field by field $Recordset.Fields("UserName").Value = @USERID $Recordset.Fields("FullName").Value = @FULLNAME $Recordset.Fields("Workstation").Value = @WKSTA $Recordset.Fields("PrivilegeLevel").Value = @PRIV $Recordset.Fields("HomeDrive").Value = @HOMESHR $Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME ;Update the new record and close the recordset object $Recordset.Update $Recordset.Close() ;Check Computer details next - refresh $Recordset object with new query $Command.CommandText = $CHECK_ENTRY_COMPUTERS $Recordset.Open($Command) ;Check for existing record to update If $Recordset.RecordCount < 1 ? "adding new computer record" $Recordset.AddNew Endif ;Write values into table $Recordset.Fields("Workstation").Value = @WKSTA $Recordset.Fields("NTDomain").Value = @DOMAIN $Recordset.Fields("SystemManufacturer").Value = $SystemManufacturer $Recordset.Fields("SystemModel").Value = $SystemModel $Recordset.Fields("SerialNo").Value = $SerialNo $Recordset.Fields("IPAddress").Value = @IPADDRESS0 $Recordset.Fields("MACAddress").Value = @ADDRESS $Recordset.Fields("OS").Value = @PRODUCTTYPE $Recordset.Fields("ServicePack").Value = @CSD $Recordset.Fields("PhysicalMemory").Value = $PhysicalMemory $Recordset.Fields("ProcessorSpeed").Value = $ProcessorSpeed $Recordset.Fields("PagefileSpace").Value = $PagefileSpace $Recordset.Fields("C_DriveFormat").Value = $C_DriveFormat $Recordset.Fields("C_DriveFreeSpace").Value = $C_DriveFreeSpace $Recordset.Fields("C_DriveTotalSize").Value = $C_DriveTotalSize $Recordset.Fields("LoggedOnUser").Value = @USERID $Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME ;Update and close recordset object $Recordset.Update $Recordset.Close() $Connection.Close() $Connection = 0 $Recordset = 0 $Command = 0 else Goto error endif :end exit 321 :error exit\
|
Top
|
|
|
|
#84915 - 2002-02-05 08:59 PM
Re: Create/Read/Update MS Access Database
|
Radimus
Moderator
Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
|
My network doesn't specify a home drive, and it wouldn't write the @homedrive macro... I changed it to a variable that we do use and it worked fine.
|
Top
|
|
|
|
#84917 - 2002-02-06 02:08 PM
Re: Create/Read/Update MS Access Database
|
Alex.H
Seasoned Scripter
Registered: 2001-04-10
Posts: 406
Loc: France
|
Breaker, You can use NULL values, but you need to explicitly allow it in the field
_________________________
? getobject(Kixtart.org.Signature)
|
Top
|
|
|
|
Moderator: Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart
|
0 registered
and 259 anonymous users online.
|
|
|