Page 2 of 3 <123>
Topic Options
#84898 - 2002-01-23 07:42 PM Re: Create/Read/Update MS Access Database
Breaker Offline
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
#84899 - 2002-01-23 08:01 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Breaker

Two things you might check for:

1) Check for the existence of the key "ADODB.Connection" in HKEY_CLASSES_ROOT

2) If yes, might have to create a DSN for the connection. Look in Control Panel for the ODBC Data Sources applet and have a quick boo.

Let me know what you find.

-Shawn

Top
#84900 - 2002-01-23 08:13 PM Re: Create/Read/Update MS Access Database
Breaker Offline
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
#84901 - 2002-01-23 08:33 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
What do you get when you display the error message to the console ?

...

$=SetOption("wrapateol","on") ; see big msgs

$Connection.Open()

?"@ERROR; @SERROR"

-Shawn

Top
#84902 - 2002-01-23 09:33 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Hey Breaker,

How about trying this $DSN

$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"

Im in a bummer mood ... nothing to do with this thread ...

-Shawn

Top
#84903 - 2002-01-24 03:26 PM Re: Create/Read/Update MS Access Database
Breaker Offline
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 Offline
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 Moran

Break 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
#84905 - 2002-02-05 02:17 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
You are more than welcome to code and I'm glad you could use it...

Especially, since I was looking to convert my current inventory (in CSV format) into an access MDB format...

_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#84906 - 2002-02-05 03:49 PM Re: Create/Read/Update MS Access Database
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Rad,

What you need to do now is create an ASP Page that queries this data back to your Intranet..

- Kent

[ 05 February 2002: Message edited by: kdyer ]

_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#84907 - 2002-02-05 04:03 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
That actually reminds me of another way to collect login telemetry. Setup an asp or cgi script on a Web Server and use IE to send (post) telemetry data to the page during login ... Paul Berquam and (I think) CJ were working on such a scheme once ... nice thing about this approach is that minimal stuff (components) is required on the workstation - and no sticky issues surrounding privs and rights are involved. Just another way to skin this cat I guess.

-Shawn

Top
#84908 - 2002-02-05 04:14 PM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Of course, for this code to work properly, you do need to have the requisite ADO/WMI components available on all your workstations. This isn't really a problem as such, though...
_________________________
================================================
Breaker


Top
#84909 - 2002-02-05 06:18 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
I have a problem with this...

It connects, does this...

code:

If $Recordset.RecordCount < 1
? "adding record"
$Recordset.AddNew
Endif

There are no errors, but also no data.

I made a DB and pointed the script to the right place, made 2 tables; TBL_USERS and TBL_COMPUTERS, then made the columns as specified here

code:

$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


and so for the computer table

Are the com commands for access2000 different?

_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#84910 - 2002-02-05 06:25 PM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Radimus,

I've had no difference in behaviour between systems with Access 97 or 2000, or even without Access at all, since the "Microsoft Access Driver" File DSN is installed by default on all Win32 OSes (someone correct me on this if it's just a wild generalisation!)

If you post the full code you're trying to use it might be easier to spot anything amiss... I'll try to help if I can.

_________________________
================================================
Breaker


Top
#84911 - 2002-02-05 06:35 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline
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\


_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#84912 - 2002-02-05 06:51 PM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
You might want to chuck a few @SERROR's in there to try and get a feel for where it might be failing.

If the connection is OK and the SQL query is returning a valid recordset object, then it may well be the $Recordset.AddNew() method that is failing... ...not sure why, though. MSDN says you can use the Supports() method to check whether your current Recordset object will support various methods, based on the cursor and lock types. You can check here for that. You'll need to look under ADO Reference -> ADO Methods.

I'm off home now, so I'll print this off and rack my tiny brain a little.

[ 05 February 2002: Message edited by: Breaker ]

_________________________
================================================
Breaker


Top
#84913 - 2002-02-05 07:09 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
the @serror did the trick...

One of the recordset objects was a NULL/zero length and bombed it...

I changed the value, and away it goes...

Thanks much.

_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#84914 - 2002-02-05 07:32 PM Re: Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter
*****

Registered: 2001-11-15
Posts: 513
Loc: PA, USA
I ran your script above with no error? What value have you changed?
_________________________
-Jim

...the sort of general malaise that only the genius possess and the insane lament.

Top
#84915 - 2002-02-05 08:59 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline
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.
_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#84916 - 2002-02-06 10:51 AM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Whoa... didn't think of that one. Obviously I'm only grabbing info that would be of use to us here, but I figured that any NULL values would simply write empty fields to the database.

I'm gonna have to be more careful when error handling in future...

Glad you got it sorted.

_________________________
================================================
Breaker


Top
#84917 - 2002-02-06 02:08 PM Re: Create/Read/Update MS Access Database
Alex.H Offline
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
Page 2 of 3 <123>


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 464 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.144 seconds in which 0.089 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org