Access 2007 connection problem

[JohnW]

I have a program that I am trying to get working with Access 2007. It works flawlessly with 2003 but as soon as I change the driver to the 2007 driver it fails on the open command. I added the com error handler in but really gives me no clue to what the problem is since it comes back with Unspecified error.

Any help on a connection would be greatly appreciated.

As you can see in the code below there is an INI file that holds the database location and the table called Table1 has a "CUSTOMER Id", "Customer Name", "CITY", "STATE", and "Phone" fileds.

The program accepts a phone number in the form 1112223333 and then adds the dashes in for lookup in the table.

expandcollapse popup

#include<array.au3>

#include<Access.au3>

#include<GuiConstantsEx.au3>

#include <GUIListBox.au3>



Opt('MustDeclareVars', 1)

Opt("GUIOnEventMode", 1)

Global $sqlCon, $adoRs, $output, $phonenumber, $address, $city, $state, $zip, $qryPhone, $phoneDash, $custID, $addressid

Global $custName, $inphone, $outputcust, $DBLoc, $count, $PickWnd, $custlist, $custlabel, $clipBtn,$oMyError

;~ $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

If $cmdline[0] <> 0 Then

    $inphone = $cmdline[1]

;~   $outputcust = $cmdline[2]

;~   msgbox(0,"error",$inphone)

Else

    MsgBox(0, "Error", "No parameters given")

EndIf

$PickWnd = GUICreate("PhonePop", 500, 250)

GUISetOnEvent($GUI_EVENT_CLOSE, "_Events")

GUISetOnEvent($GUI_EVENT_MINIMIZE, "_Events")

GUISetOnEvent($GUI_EVENT_RESTORE, "_Events")



$custlist = GUICtrlCreateList("", 10, 10, 240, 125)

$custlabel = GUICtrlCreateLabel("", 260, 10, 200, 125)

$clipBtn = GUICtrlCreateButton("Ok", 225, 175, 50)

GUICtrlSetOnEvent($clipBtn, "ClipButton")



ConnectDB()

GUISwitch($PickWnd)

GUISetState(@SW_SHOW, $PickWnd)

While 1

    Sleep(60)

WEnd



Func ConnectDB()

    $DBLoc = IniRead(".\PhonePop.ini", "DatabaseLoc", "DB", "")

    $sqlCon = ObjCreate("ADODB.Connection")

    $sqlCon.Open("DRIVER={Microsoft Access Driver (*.mdb,*.accdb)};DBQ="& $DBLoc,3,1)

;~   $qryPhone = getPhoneNo()

    $qryPhone = $inphone

    $phoneDash = StringMid($qryPhone, 1, 3) & "-" & StringMid($qryPhone, 4, 3) & "-" & StringMid($qryPhone, 7, 4)

;~   msgbox(0,"Test",$phoneDash)

;~ msgbox(0,"Test",$outputcust)

    $count = 0

    $adoRs = $sqlCon.Execute("Select DISTINCT * From Table1 WHERE Phone ='" & $phoneDash & "'")

    With $adoRs

        While Not .EOF

            $count = $count + 1

            .moveNext

        WEnd

        If $count = 0 Then

            Exit

        EndIf

    EndWith

;~   msgbox(0,"",$count)

    With $adoRs

        .moveFirst

        While Not .EOF

            $custID = $adoRs.Fields("CUSTOMER Id" ).value

            $custName = $adoRs.Fields("Customer Name" ).value

       ;$addressid = $adoRs.Fields("ADDRESS Line1").value

       ;$address = $adoRs.Fields("ADDRESS Line2").value

       ;$zip = $adoRs.Fields("ZIP CODE").value

            $city = $adoRs.Fields("CITY" ).value

            $state = $adoRs.Fields("STATE" ).value

            $phonenumber = $adoRs.Fields("Phone" ).value

            GUICtrlSetData($custlist, $custID, $custID)

            $output = $custName & @CRLF & $city & ", " & $state & @CRLF & @CRLF & $phonenumber

            GUICtrlSetData($custlabel, $output)

;~           msgbox(0,"Test",$output )

;~           ClipPut($custID)

            .moveNext

        WEnd





    EndWith

    $sqlCon.Close

    _GUICtrlListBox_ClickItem($custlist, 0, "left")



EndFunc ;==>ConnectDB



Func ClipButton()

    ClipPut(GUICtrlRead($custlist))

    WinActivate("[CLASS:WFIcaClient]")

    Exit

EndFunc ;==>ClipButton



Func _Events()

    Switch @GUI_CtrlId

        Case $GUI_EVENT_CLOSE

            Exit

        Case $GUI_EVENT_MINIMIZE

        Case $GUI_EVENT_RESTORE

        Case Else

    EndSwitch

EndFunc ;==>_Events



Func MyErrFunc()

    Local $HexNumber,$g_eventerror

    $HexNumber=hex($oMyError.Number,8)

    MsgBox(0,"","We intercepted a COM Error !" & @CRLF & _

        "Number is: " & $HexNumber & @CRLF & _

        "Windescription is: " & $oMyError.Windescription)

        

        $g_eventerror = 1

EndFunc

[GEOSoft]

Take a look at some of the functions in here.

My access udf

[JohnW]

Take a look at some of the functions in here.

My access udf

I don't believe that will work because you are using "Microsoft.Jet.OLEDB.4.0" and the new office 2007 provider is "Microsoft.ACE.OLEDB.12.0".

[JohnW]

I ended up using the provider way of connecting and was still having an issue when running it from inside of my other program and found out I needed to add the full location of the INI file for my other program to read it.

Thanks for the help GEOSoft you did get me in the right direction.

[GEOSoft]

I ended up using the provider way of connecting and was still having an issue when running it from inside of my other program and found out I needed to add the full location of the INI file for my other program to read it.

Thanks for the help GEOSoft you did get me in the right direction.

Glad it helped. I only meant for you to use the UDF for examples. That file needs to be updated with a _Access_GetProvider() function as well as a _Access_TableToArray() func that I have worked on.

[DaRam]

Since you are using an INI file, you might want to store the driver string within the INI file and read it to build the connection string.

This way when the next version of Office/Access comes along you can modify the INI file accordingly and your application would still work.

I ended up using the provider way of connecting and was still having an issue when running it from inside of my other program and found out I needed to add the full location of the INI file for my other program to read it.

Thanks for the help GEOSoft you did get me in the right direction.

[JohnW]

Glad it helped. I only meant for you to use the UDF for examples. That file needs to be updated with a _Access_GetProvider() function as well as a _Access_TableToArray() func that I have worked on.

The _Access_GetProvider() wouldn't be too hard for Access since really most people will either be using the new for office 2007 "Microsoft.ACE.OLEDB.12.0" or "Microsoft.Jet.OLEDB.4.0" for most everybody else.

If 12 or greater new Provider if less than 12 old provider.

$objCheck = ObjCreate("Access.application")
$offVersion = $objCheck.Version
If "12" == StringLeft($offVersion,2) Then
$oProvider="Microsoft.ACE.OLEDB.12.0" 
Else
 $oProvider="Microsoft.Jet.OLEDB.4.0"
EndIf</P> <P>

That is the quick and simple way at least.

[GEOSoft]

It's not the code that is difficult, it's finding the time to do the update.