Sign in to follow this  
Followers 0
JohnW

Access 2007 connection problem

8 posts in this topic

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.

#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

Share this post


Link to post
Share on other sites



Take a look at some of the functions in here.

My access udf


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

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".

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

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
Sign in to follow this  
Followers 0