Moderators JLogan3o13 Posted May 25, 2010 Moderators Share Posted May 25, 2010 (edited) 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 = 0Func _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 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
Juvigy Posted May 25, 2010 Share Posted May 25, 2010 Try "" if that doesnt work try something like: Local $oVBS = ObjCreate("ScriptControl") $oVBS.language = "VBScript" Global Const $Nothing = $oVBS.eval("Nothing") $oVBS = $Nothing Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted May 25, 2010 Author Moderators Share Posted May 25, 2010 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. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted May 28, 2010 Author Moderators Share Posted May 28, 2010 [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 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now