You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Advanced Scripting » Writing to Access file...
Register User       Forum List       Calendar       FAQ
Page 1 of 1 1
Topic Options
#173065 - 2007-01-22 11:13 PM Writing to Access file...
wreded Offline
Fresh Scripter

Registered: 2003-11-06
Posts: 28
Loc: Montana, USA
i thought i had this down, now i'm not so sure. Short, sweet code to check one thing on lots of computers and put what it found in an Access database. "Borrowed" code from Rich Weber's inventory script to handle putting 3 fields in a database, substituting my database file name for his. The problem is that nothing ever gets put in the bucket. Watching the directory i can see that the file is opened, but nothing is ever there when i check.
Code:
$=setoption("wrapateol","on")
Dim $DBpath, $DBFile

;$DBpath = @Scriptdir ;Modify this to represent your database location if using an access database.
$DBpath = "F:\New Folder"
$DBfile = $DBpath +"\Computers.mdb"
CLS
At (5,1) "Asset Audit Script Processing..."
Sleep 2
CLS

Break ON CLS

If Exist($DBpath) = 0  ;Abort if no Database exists when using an access database.
? "IT Assets Database Not Found. Aborting..."
  Sleep 3
  Exit 1
Else
  Main($DBFile)
EndIf

Function Main($DBFile)
	Dim $CNstring, $CMDtxt, $cn, $cmd, $rs

;*** CONFIGURE DATABASE CONNECTION STRING ***
	$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBFile"  ;Next line for sql server
	;$CNstring = "Driver={SQL Server};Server=MyServer;database=MyDatabase;uid=UserName;pwd=password"

	$CMDtxt = "select * from Branded where computername = '@WKSTA'"
	$cn = CreateObject ("ADODB.Connection")
	$cmd = CreateObject ("ADODB.Command")
	$rs = CreateObject ("ADODB.RecordSet")

	$cn.connectionstring = $CNstring
	$cn.Open
	$cmd.activeconnection = $cn

	QueryLogon($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile)

	$cn.close
	$x=MessageBox("Done!","Data Collection", 4096)
	Exit 1
EndFunction

;*** COLLECT WORKSTATION ASSET INFORMATION

;Queries Logon information, updates COMPUTERS table.
Function QueryLogon($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile)
	Dim $Search1, $Search2, $Branded, $FindString, $Found, $CMDtxt, $CompName, $ModifyDate, $cmd, $rs

	? "Querying Logon Information"
	$rs.cursortype = 3
	$rs.locktype = 3
	$rs.activecommand = $cmd
	$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
	$ComputerName = @WKSTA
	$ModifyDate = @DATE
	;**** Set Variables ****
	$CompName = @WKSTA
	$Search1 = "c:\windows\system\oeminfo.ini"
	$Search2 = "c:\windows\system32\oeminfo.ini"
	$Branded = "blank"
	$FindString = "Your text here"
	$Found = "blank"
	$ModifyDate = @DATE

	;Check for file existence and check for the $FindString
	$Branded = IIF(ReadProfileString($Search1, "Support Information", "Line3"), "True", "False")
	$Branded = IIF(ReadProfileString($Search2, "Support Information", "Line3"), "True", "False")

	If $Branded = "True"
		$Found = ReadProfileString($Search1, "Support Information", "Line3")
		$Found = ReadProfileString($Search2, "Support Information", "Line3")
	Else
		$Found = "False"
	Endif

	? $CompName
	;? $Search1
	;? $Search2
	? $Branded
	? $FindString
	? $Found
	? $ModifyDate
	Get $x

	If $rs.eof = -1
		$rs.addnew
	EndIf
	$rs.fields.item("COMPNAME").value = $CompName
	$rs.fields.item("Branded").value = $Branded
	$rs.fields.item("MODIFYDATE").value = $ModifyDate
	$rs.update
	$rs.Close

	Exit @error
EndFunction


Anyone see what i can't? Thanks for any assistance (all logic/programming errors are mine, i'm a brute force programmer).

Thanks,
Dave
Top
#173068 - 2007-01-22 11:23 PM Re: Writing to Access file... [Re: wreded]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11634
Loc: Space
Perhaps a link to Rich Weber's inventory script would be in order as well.

Is this a logon type script or remote admin ?

What version of KiXtart are you using and what version of MS Access ?
Top
#173071 - 2007-01-23 12:01 AM Re: Writing to Access file... [Re: NTDOC]
wreded Offline
Fresh Scripter

Registered: 2003-11-06
Posts: 28
Loc: Montana, USA
Let's see;
KiXtart = 4.5
MS Access = 2003

i don't have rights to create a login script and, though i've mentioned it to our admin, i wanna have it working in test mode (i.e. writing to a database) before i release it to him. Given that i guess it's a remote admin script.

Inventory Script Help by Gn0stik, 12 Dec 05, Post 154171

i've used his code in a real inventory and it worked like a charm. i added a couple of things that are specific to our environment and left out things that we didn't care about, all in all a successful inventory/audit. i wouldn't be so puzzled except that nothing gets written to the file at all though i can see the file is opened.

Thanks!
Dave
Top
#173076 - 2007-01-23 01:48 AM Re: Writing to Access file... [Re: wreded]
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Put tracer messages after each COM call in the UDF, like this:

$rs.update

? "ERROR @ERROR - @SERROR"

Then keep moving the tracer further down until you hit the error.

-Shawn
Top
#173077 - 2007-01-23 01:50 AM Re: Writing to Access file... [Re: wreded]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11634
Loc: Space
KiXtart 4.5 as in:

4.50
4.51
4.52
4.53

Is it possible DB is open for lock by another account or user ?
If security is not set correctly so that each user has write access to the folder then they can't create their own locks to the DB.

Do you get any errors on screen ? If not then I'd start adding some error codes to the script so that you can see what and where it's getting to until it errors.
Top
#173082 - 2007-01-23 04:06 AM Re: Writing to Access file... [Re: NTDOC]
wreded Offline
Fresh Scripter

Registered: 2003-11-06
Posts: 28
Loc: Montana, USA
NTDOC - i'm using Ver 4.50. i know there's a new one available, but 4.50 is available on the entire network.

Shawn - i did the @ERROR thing and got this result:

Code:
DAD0
True
2007/01/22
 not allowed when the object is closed.) [-2147352567/80020009]et - Operation is


Right now the entire thing, database and all, sits on a thumb drive that i take with me so i don't have to try to remember where i left off (i do some of this at home where i don't get bothered [as much]), so database locking shouldn't be an issue. By the looks of it i haven't properly opened the database for updating. Would that be a proper interpretation of this error message? If so, where and how should i open the database for writing?
Thanks,
Dave
Top
#173083 - 2007-01-23 04:14 AM Re: Writing to Access file... [Re: wreded]
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
ok two things ...

1) Put this at the top of your script:

$= SetOption("WrapAtEol", "On")

This will make the error message easier to read.

2) Which statement did you get that error on ? And just make double sure that the previous COM statement returned a clean success code.

-Shawn
Top
#173084 - 2007-01-23 04:16 AM Re: Writing to Access file... [Re: wreded]
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
By using SetOption('WrapAtEOL','on') you will get all of the message.
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#173087 - 2007-01-23 05:43 AM Re: Writing to Access file... [Re: Les]
wreded Offline
Fresh Scripter

Registered: 2003-11-06
Posts: 28
Loc: Montana, USA
Ok, it looks like i'm not opening the database like i thought i was. i guess i'll have to "line-by-line" it tomorrow. Man, i hate it when a computer does what i say instead of what i mean!

Code:
ERROR -2147352567 - COM exception error "update" (ADODB.Recordset - Operation is
 not allowed when the object is closed.) [-2147352567/80020009]

Thanks!
Dave
Top
#173088 - 2007-01-23 05:49 AM Re: Writing to Access file... [Re: wreded]
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
ok, please keep us updated on your progress.
Top
#173090 - 2007-01-23 07:00 AM Re: Writing to Access file... [Re: Shawn]
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11165
Loc: Boston, MA, USA
You might want to take a look at the DB...() UDFs. It would require rewriting the INSERT statement to SQL standards, though.
_________________________
There are two types of vessels, submarines and targets.

Top
#173118 - 2007-01-23 07:15 PM Re: Writing to Access file... [Re: Sealeopard]
wreded Offline
Fresh Scripter

Registered: 2003-11-06
Posts: 28
Loc: Montana, USA
Ok, thanks for Your help. i went line by line from top to bottom and got it running. Checked it against one computer that meets specs and one that doesn't and got the right responses both times. Yeah Team!
i think i wasn't paying close enough attention to the database within the database file and didn't open it properly. i also discovered that i wasn't checking for what i thought i was checking for.
Final code (minus comments) is here. Thanks so much for Your help.
Dave

Code:
$=setoption("wrapateol","on")
Dim $DBpath, $DBFile

;$DBpath = @Scriptdir ;Modify this to represent your database location if using an access database.
$DBPath = "F:\New Folder"
$DBfile = $DBpath + "\computers.mdb"
CLS
At (5, 1) "Checking for Branding..."
Sleep 1
CLS

Break ON CLS

If Exist($DBpath) = 0  ;Abort if no Database exists when using an access database.
? "Branding Database Not Found. Aborting..."
  Sleep 1
  Exit 1
Else
  Main($DBFile)
EndIf

Function Main($DBFile)
Dim $CNstring, $CMDtxt, $cn, $cmd, $rs

;*** CONFIGURE DATABASE CONNECTION STRING ***
$CNstring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DBFile"  ;Next line for sql server
;$CNstring = "Driver={SQL Server};Server=MyServer;database=MyDatabase;uid=UserName;pwd=password"

$CMDtxt = "select * from Branded where compname = '@WKSTA'"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")

$cn.connectionstring = $CNstring
$cn.Open
$cmd.activeconnection = $cn

QueryBranded($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile)

$cn.close
$x=MessageBox("Done!","Branding Collection", 4096)
Exit 1
EndFunction

;*** COLLECT BRANDING INFORMATION
Function QueryBranded($cn, $cmd, $rs, $CNstring, $CMDtxt, $DBfile)
Dim $CMDtxt, $CompName, $ModifyDate, $cmd, $rs
? "Querying Branding Information"
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
	;**** Set Variables ****
	$ModifyDate = @DATE
	$CompName = @WKSTA
	$Search1 = "c:\windows\system\oeminfo.ini"
	$Search2 = "c:\windows\system32\oeminfo.ini"
	$Branded = "blank"
	$FindString = "Your String Here"
	$Found = "blank"
	$ModifyDate = @DATE

	If $rs.eof = -1
		$rs.addnew
	EndIf

	;Check for file existence and check for the $FindString
	$Found = ReadProfileString($Search1, "Support Information", "Line3")
	$Found = ReadProfileString($Search2, "Support Information", "Line3")

	$Branded = IIF($Found = $Findstring, "True", "False")
	
	$rs.fields.item("COMPNAME").value = $CompName
	$rs.fields.item("Branded").value = $Branded
	$rs.fields.item("MODIFYDATE").value = $ModifyDate

	$rs.update
	$rs.Close
EndFunction

Top
Page 1 of 1 1


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

Who's Online
0 registered and 2375 anonymous users online.
details
Newest Members
fgarcia, Viginette, ManuvdWielNL, Sir_Barrington, batdk82
17889 Registered Users
My Cookies · Mark all read Powered by UBB.threads™ Contact Us · KiXtart.org website · Top

Generated in 0.065 seconds in which 0.028 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