papote

ADO connect to Access

4 posts in this topic

#1 ·  Posted (edited)

I am trying to use AutoIT to copy fields from an Access query onto another program.

I have the following code which is mostly copy-paste, to see if it works, but the message box displays empty even if I use the field name or field position. 

Quote

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Run_Tidy=y
#AutoIt3Wrapper_Run_Au3Stripper=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

$oAD_MyError = ObjEvent("AutoIt.Error", "_AD_ErrorHandler") ; at the top of your script

$iAD_Debug = 0  ; If I didn't add this would get the error: "Variable used without being declared"

Opt("MouseCoordMode", 2) ;1=absolute, 0=relative, 2=client
Opt("SendKeyDelay", 60) ; milliseconds

#include <MsgBoxConstants.au3>

HotKeySet("{ESC}", "Terminate2")

$MyWindowTitle = "MyWindow"
$dbname = "C:\Users\myUser\Desktop\MyFile.accdb"
$query = "SELECT * FROM qry1;"

If WinExists($MyWindowTitle) Then

Local $title
    $adoCon = ObjCreate("ADODB.Connection")
    ;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower
    $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) ;Use this line if using MS Access 2007 and using the .accdb file extension
    $adoRs = ObjCreate ("ADODB.Recordset")
    $adoRs.CursorType = 1
    $adoRs.LockType = 3
    $adoRs.Open ($query, $adoCon)
    $title = $adoRs.Fields("Acct").value    ;Retrieve value by field name
    ;$title = $adoRs.Fields(1).value         ;Retrieve value by column number
    $adoCon.Close
    MsgBox(0,"testing",$title)

    Terminate("Error Account!")

; .........

Else
    Terminate("MyWindow not found.")
EndIf

 

Func _AD_ErrorHandler()

    Local $bAD_HexNumber = Hex($oAD_MyError.number, 8)
    Local $sAD_Error = "COM Error Encountered in " & @ScriptName & @CRLF & _
            "Scriptline = " & $oAD_MyError.scriptline & @CRLF & _
            "NumberHex = " & $bAD_HexNumber & @CRLF & _
            "Number = " & $oAD_MyError.number & @CRLF & _
            "WinDescription = " & StringStripWS($oAD_MyError.WinDescription, 2) & @CRLF & _
            "Description = " & StringStripWS($oAD_MyError.description, 2) & @CRLF & _
            "Source = " & $oAD_MyError.Source & @CRLF & _
            "HelpFile = " & $oAD_MyError.HelpFile & @CRLF & _
            "HelpContext = " & $oAD_MyError.HelpContext & @CRLF & _
            "LastDllError = " & $oAD_MyError.LastDllError
    If $iAD_Debug > 0 Then
        If $iAD_Debug = 1 Then ConsoleWrite($sAD_Error & @CRLF & "========================================================" & @CRLF)
        If $iAD_Debug = 2 Then MsgBox(64, "Active Directory Functions - Debug Info", $sAD_Error)
        If $iAD_Debug = 3 Then FileWrite("AD_Debug.txt", @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & @CRLF & _
                "-------------------" & @CRLF & $sAD_Error & @CRLF & "========================================================" & @CRLF)
            EndIf
    $iAD_COMErrorDec = $oAD_MyError.number
    Switch $bAD_HexNumber
        ; 8007054B: ERROR_NO_SUCH_DOMAIN - The specified domain either does not exist or could not be contacted
        Case "8007054B"
            $iAD_COMError = 2
            ; 80020009: DISP_E_EXCEPTION - Unanticipated error occurred
            ; 80072030: LDAP_NO_SUCH_OBJECT - Object does not exist
        Case "80020009", "80072030"
            $iAD_COMError = 3
            ; 8007203A: LDAP_SERVER_DOWN - The server is not operational. Can be caused by hitting a domain controller too hard
        Case "8007203A"
            $iAD_COMError = 4
            ; 80005000: E_ADS_BAD_PATHNAME - An invalid ADSI path name was passed
        Case "80005000"
            $iAD_COMError = 5
            ; 800706BA: RPC_S_SERVICE_UNAVAILABLE - The RPC-Server is not available
        Case "800706BA"
            $iAD_COMError = 6
        Case Else
            MsgBox(262144, "Active Directory Functions", $sAD_Error)
            $iAD_COMError = 1
    EndSwitch

EndFunc ;==>_AD_ErrorHandler

 

Edited by papote

Share this post


Link to post
Share on other sites



#2 ·  Posted

Try setting $iAD_Debug to >0 to see if an error is occurring with the object.
Per the Error Func you posted, 1 will output errors to (SciTe) console, 2 will display a msgbox and 3 will output to log file.

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

I have and same results.

Share this post


Link to post
Share on other sites

#4 ·  Posted

You could try the ADO UDF written by mLipok.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now