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

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

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

View this thread on the forum...