[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]


==== ODBC  Connection Script (OpenInsight Specific) ====
=== At 13 SEP 2001 05:21:13AM Yogesh Kumar wrote: ===
 
{{tag>"OpenInsight Specific"}}
 
<QUOTE>
I am able to set up the ODBC connection from OI. Could you please help me with an example on accessing and updating MS-Access database from OI i.e. connection parameters
</QUOTE>
----

=== At 13 SEP 2001 03:00PM John Poremba wrote:  ===

<QUOTE>If you have setup the odbc connection in OI you can modify this code to work for you. Replace M2MDATA with your odbc connections name. SOMAST is a LH table that get updated from a liked named FoxPro table in the VFP M2MDATA.dbc The variable "Row" contains the selected dbf record data.





FUNCTION Ezdoedi_M2mFileProcess_SO (ezdoedi)

* Modified and customized 6/99 by John Poremba

* revision 4/01 by JP, sales orders only.

* get data directly from m2m 5/15/01



declare subroutine MSG

declare Function Get.RecCount

declare Function DSGetProperty

declare Function DSInstance

$insert XO_Equates

$insert MSG_Equates

$insert DS_Equates



*

MSG.NOTE:

RESPONSE=';MESSAGE='

MESSAGE=IMPORT SO DATA FOR EDI PROCESSING|Click  to CONTINUE,  to Exit."

MESSAGE=BNY"

RESPONSE=MSG(@window, MESSAGE)

IF RESPONSE=1 THEN GOTO PROCESS

MSG(@WINDOW,"EXITING UPON YOUR REQUEST.");GOTO DONE



*

PROCESS:

Results=";CNTR=0;tblna=';row=';Rec='



open "SOMAST" to fData7 then Cnt7=Get.RecCount(fData7,Flag,0) else gosub OpenError; return 0



* create connection

hXO=XOInstance("M2MDATA","ODBC","","")

hQry=0

if hXO then

   * create the query handle for the connection handle

   hQry=QryInstance(hXO)

   if hQry then

      * execute a script

      Flag=QryMethod(hQry, QRY_EXECUTE$, "select * from SoMast where fstatus=Open' and not deleted()")

      Tblna=SOMAST"; cRows=cnt7; gosub ProcessInfo

      if Flag then

         * retrieve a row at a time

         loop

            Flag=QryMethod(hQry, QRY_GETROW$, Row)

         while Flag

         	Row=iconv(Row,"MD3");Row=iconv(Row,"MD0");Row=iconv(Row,"MD5")

         	Row=iconv(Row,"MD5");Row=iconv(Row,"MD3")

         	Row=iconv(Row,"MD3")        	

         	write Row to fData7,Row else gosub WriteError

         	Msg(@window,MsgUp,CNTR,MSGINSTUPDATE$)

            CNTR=CNTR +1

         repeat

         Results 1,1="



         * cancel script

         Flag=QryMethod(hQry, QRY_CANCEL$)

      end else

         gosub error

      end

      Msg(@window,MsgUp);gosub Info; ClearSelect

      

  * close the query handle

      Flag=QryMethod(hQry, QRY_DESTROY$)

   end else

      gosub error

   end

  * close the connection

  Flag=XOMethod(hXO, XO_DESTROY$)

end else

   gosub error

end



*

DONE:



return 0



* Error handling

Error:

   if hQry then

      Flag=QryMethod(hQry, QRY_GETERROR$, "", "", "", "", Text)

   end else

      Flag=XOMethod(hXO, QRY_GETERROR$, "", "", "", "", Text)

   end

   convert @vm to @tm in Text

   Def="

   Def   =Text

   Def=ExecuteScript Error"

   Def   =!"

   Msg(@window, Def)

return



OpenError:

 * failed to open LH_TABLE$

   EQUATE LH_TABLE$ TO Tblna

   Def="

   Def   =Failed to open ": LH_TABLE$

   Def=Open Error"

   Def   =!"

   Msg(@window, Def)

return



WriteError:

 * failed to write "Row" to "Row"

   MSG("WRITE ERROR ":SRKEY:"|":status:"|":@file_error,'T.5','','')

return



InFo:

 * keep user informed.

  Message='

  MESSAGE=CNTR:" Records from ":TBLNA:" Imported."

  MESSAGE=T2'

  MESSAGE=W'

  MESSAGE=!'

  Msg(@window,Message)

  cntr=0

return



ProcessInFo:

 * keep user informed on progress.

  Message='

  MESSAGE=Importing Records from ":TBLNA:""

  MESSAGE=G'

  MESSAGE=W'

  MESSAGE=*'

  MESSAGE=cRows

  MsgUp=Msg(@window, MESSAGE)

return

</QUOTE>

----

=== At 14 SEP 2001 02:37AM Yogesh Kumar wrote:  ===

<QUOTE>John,

Thanks for the response

What I needed is sample script on inserting/updating/deleting records into MS-Access database from OI, could you please help me



</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=B97B2D5D3C6B974B85256AC60033619B|View this thread on the  forum...]]