[solved] Delete records from Access

[JLogan3o13]

I have been attempting to come up with a script that can connect to a local access database and run a SQL query to delete rows (query is below). I've been searching the forums, and have fund several articles on using the ADODB to make the connection. I also stumbled across a forum member that wrote a UDF for access (many thanks) and have been attempting to use that. However, when I run the script, it returns an exit code of 0, but does not delete the needed records.

Below are the particulars of what I'm trying to do, and the script I borrowed with permission. Any assistance on what I am doing wrong would be greatly apprciated.

SQL Query: DELETE * FROM CODECLAS WHERE CC_ClassSuffix is null; (inputted manually in Access this works just fine, so I know it is not the query itself).

<script>

#Include-once

#include <AccessConstants.au3>

$adSource = "C:\Test.mdb"

$adTable = "CODECLAS"

$adCol = "CC_ClassSuffix"

$Find = (this is where I'm fuzzy. Not sure how to set this to null properly. I've been just putting in "null;" or "null")

$ad0cc = 0

Func _accessDeleteRecord($adSource,$adTable, $adCol,$Find,$adOcc)

$oADO = 'ADODB.Connection'

If IsObj($oADO) Then

$oADO = ObjGet('',$oADO)

Else

$oADO = _dbOpen($adSource)

EndIf

If IsObj($oADO) = 0 Then Return SetError(1)

$oRec = _dbOpenRecordset()

If IsObj($oRec) = 0 Then Return SetError(2)

$Search = $adCol & " = '" & $Find & Chr(39)

With $oRec

.CursorLocation = $adUseClient

If $adOcc = 1 Then

.Open ("SELECT * FROM " & $adTable , $oADO, $adOpenStatic, $adLockOptimistic)

.find($Search)

.Delete()

.close

Else

.Open("DELETE * FROM " & $adTable & " WHERE " & $adCol & " = '" & $Find & Chr(39), $oADO, $adOpenStatic, $adLockOptimistic)

EndIf

EndWith

$oADO.Close()

EndFunc

Edited May 28, 2010 by JLogan3o13

[Juvigy]

Try

""

if that doesnt work try something like:

Local $oVBS = ObjCreate("ScriptControl") 
$oVBS.language = "VBScript" 
Global Const $Nothing = $oVBS.eval("Nothing") 
$oVBS = $Nothing

[JLogan3o13]

Try

""

if that doesnt work try something like:

Local $oVBS = ObjCreate("ScriptControl") 
$oVBS.language = "VBScript" 
Global Const $Nothing = $oVBS.eval("Nothing") 
$oVBS = $Nothing

Thanks for the suggestions. I tried both, and neither performed the delete as needed. Still no error, but it does not delete the records.

[JLogan3o13]

[solved]

After monkeying around with it a bit more, this proved to be more simple than I thought. I ended up just creating an ODBC connection of my own that I could call in the script:

Dim $strDSN
Dim $strSQL
Dim $objCN

;Create ODBC Connection if necessary

$var = RegRead( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "DBQ" )
IF $var <> "C:\Program Files\...\DATA1\my db file" Then
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\..." )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "DBQ", "REG_SZ", "C:\Program Files\...\DATA1\My db file" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "Driver", "REG_SZ", "C:\WINDOWS\system32\odbcjt32.dll" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "DriverId", "REG_DWORD", "25" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "FIL", "REG_SZ", "MS Access;" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "SafeTransactions", "REG_DWORD", "0" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...", "UID", "REG_SZ", "" )  
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines\Jet" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines\Jet", "ImplicitCommitSync", "REG_SZ", "" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines\Jet", "MaxBufferSize", "REG_DWORD", "2048" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines\Jet", "PageTimeout", "REG_DWORD", "5" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines\Jet", "Threads", "REG_DWORD", "3" )
  RegWrite( "HKCU\SOFTWARE\ODBC\ODBC.INI\...\Engines\Jet", "UserCommitSync", "REG_SZ", "Yes" )
EndIf

;Connect using ODBC Connection
$strDSN = "MyConnection" 
$strSQL = "Delete * FROM CODECLAS WHERE CODECLAS.CC_ClassSuffix Is Null;"
$objCN = ObjCreate("ADODB.Connection")
$objCN.Open($strDSN) 
$objCN.Execute($strSQL) 
$objCN.close