JohnW Posted March 18, 2009 Share Posted March 18, 2009 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 Link to comment Share on other sites More sharing options...
GEOSoft Posted March 18, 2009 Share Posted March 18, 2009 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!" Link to comment Share on other sites More sharing options...
JohnW Posted March 18, 2009 Author Share Posted March 18, 2009 Take a look at some of the functions in here.My access udfI 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". Link to comment Share on other sites More sharing options...
JohnW Posted March 18, 2009 Author Share Posted March 18, 2009 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. Link to comment Share on other sites More sharing options...
GEOSoft Posted March 18, 2009 Share Posted March 18, 2009 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!" Link to comment Share on other sites More sharing options...
DaRam Posted March 18, 2009 Share Posted March 18, 2009 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. Link to comment Share on other sites More sharing options...
JohnW Posted March 18, 2009 Author Share Posted March 18, 2009 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. Link to comment Share on other sites More sharing options...
GEOSoft Posted March 18, 2009 Share Posted March 18, 2009 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!" 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