#147073 - 2005-09-04 01:18 AM
Write to Access Database
|
cyri
Getting the hang of it
Registered: 2005-06-27
Posts: 95
|
I'm trying to figure out the best way to write to an Access database. But I don't want to just append data to tables. I also want a way to verify if a particular value is already in the database. Depending on results from that check would judge whether it would do an UPDATE or an INSERT INTO. So as not to duplicate Username and/or Computer Names.
I've tried the DBExecuteSQL() UDF, but the UPDATE command always returns 0 whether it updated a record or not. And the SELECT command pretty much does the same thing.
Then I thought maybe I would try the DBCommand() UDF. The SELECT in that returns an array, but I'm not sure what to do with it from there. I tried the ASCAN against the array returned, but that doesn't find the string.
Here is a snippit of code using the DBCommand() function for the SELECT. I'm using the DBExecuteSQL() function to do the work, but I've omitted the actual functions. They were taken directly from the UDF forum.
Code:
$ConnDSN = "DRIVER={Microsoft Access Driver (*.mdb)}; UID=; PWD=; DBQ=K:\db1.mdb"
$sql = "SELECT UserName FROM sdrive WHERE UserName='@USERID';"
$recordset = DBCommand($ConnDSN,$sql)
$x=ASCAN($recordset,@USERID)
? $x
If $x = @USERID
$retcode = DBExecuteSQL($objConn,"UPDATE sdrive Set Username = '@USERID', ComputerName = '@WKSTA', PrimaryGroup = 'Testing123', SDrive = '$SDrive' WHERE Username = '@USERID'")
? "Update Records"
? "Error: " + @ERROR
Else
$retcode = DBExecuteSQL($objConn,"INSERT INTO sdrive(Username,ComputerName,PrimaryGroup,SDrive) VALUES('@USERID','@WKSTA','@PRIMARYGROUP','$SDrive')")
? "Write to SDrive"
? @ERROR
EndIf
Any ideas?
Edited by cyri (2005-09-04 01:27 AM)
|
|
Top
|
|
|
|
|
#147074 - 2005-09-04 07:38 AM
Re: Write to Access Database
|
cyri
Getting the hang of it
Registered: 2005-06-27
Posts: 95
|
I was able to get this working using a primary key in each of the tables I'm writing to. There can be no duplication because of the primary key so when kix tries to write to the table and it's a duplicate I get an error code -2147352567.
Here is the code now...
Code:
;***Logging script***
;
;---Open Database---
$objConn = DBConnOpen("DRIVER={Microsoft Access Driver (*.mdb)}; UID=@USERID; PWD=; DBQ=K:\db1.mdb")
;---Log SDrive Script---
$retcode = DBExecuteSQL($objConn,"INSERT INTO sdrive(Username,ComputerName,PrimaryGroup,SDrive) VALUES('@USERID','@WKSTA','@PRIMARYGROUP','$SDrive')")
If @ERROR = "-2147352567"
$retcode = DBExecuteSQL($objConn,"UPDATE sdrive SET Username = '@USERID', ComputerName = '@WKSTA', PrimaryGroup = '@PRIMARYGROUP', SDrive = '$SDrive' WHERE Username = '@USERID'")
EndIf
;---Log InvenBar Script---
$retcode = DBExecuteSQL($objConn,"INSERT INTO invenbar(ComputerName,UserName,PrimaryGroup,OS,OSVersion,Office,OfficeVersion,IPAddress,Speed,Memory,CDROM1,CDROM2) VALUES('@WKSTA','@USERID','$PG','$W','$WSP','$O','$OV','$IP','@mhz','$RAM','$CDROM1','$CDROM2')")
If @ERROR = "-2147352567"
$retcode = DBExecuteSQL($objConn,"UPDATE invenbar SET ComputerName = '@WKSTA', UserName = '@USERID', PrimaryGroup = '$PG', OS = '$W', OSVersion = '$WSP', Office = '$O', OfficeVersion = '$OV', IPAddress = '$IP', Speed = '@mhz', Memory = '$RAM', CDROM1 = '$CDROM1', CDROM2 = '$CDROM2' WHERE ComputerName = '@WKSTA'")
EndIf
;---Log InvenName Script---
$retcode = DBExecuteSQL($objConn,"INSERT INTO invenname(UserName,PrimaryGroup,ComputerName,OS,OSVersion,Office,OfficeVersion,IPAddress,Speed,Memory,CDROM1,CDROM2) VALUES('@USERID','$PG','@WKSTA','$W','$WSP','$O','$OV','$IP','@mhz','$RAM','$CDROM1','$CDROM2')")
If @ERROR = "-2147352567"
$retcode = DBExecuteSQL($objConn,"UPDATE invenbar SET UserName = '@USERID', PrimaryGroup = '$PG', ComputerName = '@WKSTA', OS = '$W', OSVersion = '$WSP', Office = '$O', OfficeVersion = '$OV', IPAddress = '$IP', Speed = '@mhz', Memory = '$RAM', CDROM1 = '$CDROM1', CDROM2 = '$CDROM2' WHERE UserName = '@USERID'")
EndIf
;---Close Database---
$retcode = DBConnClose($objConn)
EXIT
If anyone has a better way though I'm all ears. I'm not sure if that error code is ever received for another reason or not so I may run into issues later on.
|
|
Top
|
|
|
|
|
#147077 - 2005-09-05 05:26 AM
Re: Write to Access Database
|
cyri
Getting the hang of it
Registered: 2005-06-27
Posts: 95
|
I never found the actual DBCommand UDF before this so I had no examples to go on, only the function itself. I just realized that my searches were cutoff at 3 years old and the DBCommand UDF is older than that.
I found it now and will check it out. Thanks for the clarifcation on the ASCAN. It didn't appear real clear to me as to the result in the kixtart manual. I was just grasping at straws.
Edited by cyri (2005-09-05 05:38 AM)
|
|
Top
|
|
|
|
|
#147078 - 2005-09-05 06:43 AM
Re: Write to Access Database
|
cyri
Getting the hang of it
Registered: 2005-06-27
Posts: 95
|
Now that I found the entire DBCommand UDF I'm all set. Also, keying the date/time stamp won't work because this is set to run via login script. Chances are there would be multiple entries at the same time. And history is not necessary due to the fact that is runs at login. The database would fill up in an hurry considering we have about 2,000 users.
Thank you for everything though. DBCommand is exactly what the doctor ordered. Just didn't have all the necessary pieces of the puzzle at first. 
|
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 837 anonymous users online.
|
|
|
