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)