Hi! Can anyone help me with some proposal! How could I get data form other database by ODBC? Actually, i have to get invioce from Oracle database and then post it. With regards, Maris
You could try using ADO in navision with a ODBC connection string.
Brian, can you briefly show me how I can use ADO connection in Navision? Is it applicable to Navision Database?
You might want to consider the fact that by attempting to extract from a foreign database from Navision via ODBC, you have no native validation capability. From an accounting control perspective, you are much better off having the foreign database use ODBC to pass the data to an interim set of data tables, then have Navision bring the interim data into the transaction tables; all validation can be easily done during this process. If there are errors in the data from the outside source, this is easily identified and there is no chance that there will be bad data intered into Navision. Allen Beck President Beck Consulting Alameda, CA & Bellevue, WA allen@beckconsulting.com 800-456-8474
Thanks a lot to Allen for such a detail explaination. I really appreciate that and yes, it proves to be a valuable advice. However, as mention earlier by Brian, is ADO means ActiveX Data Object ? If yes, how can it be used in Navision ? If no, what does it mean ? Thanks in advance.
Here is a short example using ADO within Navision: Example retrieves CustomerID and ContactName from a SQL Server ‘Northwind’ database and displays it on screen using the Customer List form. 1)Create a new codeunit with the following global vars: a)Name:AdoRec Type:Automation SubType:‘Microsoft ActiveX Data Objects 2.5 Library’.Recordset b)Name:AdoCn Type:Automation SubType:‘Microsoft ActiveX Data Objects 2.5 Library’.Connection c)Name:AdoFields Type:Automation SubType:‘Microsoft ActiveX Data Objects 2.5 Library’.Fields d)sql Type:Text(250) e)custtemp Type:Record SubType:Customer (Set Table Property to Temproray) f)x Type:Integer //CODE STARTS HERE (RUN TRIGGER) CLEAR(AdoRec); CLEAR(AdoCn); CREATE(AdoRec); CREATE(AdoCn); AdoCn.ConnectionString := ‘Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=ESGDEV-BRIANS’; AdoCn.Open; sql := ‘SELECT CustomerID,ContactName FROM CUSTOMERS’; AdoRec.Open (sql,AdoCn,2); WHILE NOT AdoRec.EOF DO BEGIN AdoFields := AdoRec.Fields; custtemp.INIT; FOR x := 0 TO AdoFields.Count -1 DO BEGIN CASE UPPERCASE(AdoFields.Item(x).Name) OF ‘CUSTOMERID’: custtemp.“No.” := COPYSTR(FORMAT(AdoFields.Item(x).Value),1,20); ‘CONTACTNAME’: custtemp.Name := COPYSTR(FORMAT(AdoFields.Item(x).Value),1,30); END END; custtemp.INSERT; AdoRec.MoveNext; END; FORM.RUN(FORM::“Customer List”,custtemp); Change the connection string to conenct to your database. Note the eg. only retrieves from the customer table, its just the same to write to a customer table in navision. Since the code resides in, navision all triggers will execute. Edited by - brians on 2002 Jan 10 16:29:36
Hi, Brian Thank you for explanation in details. with regards, Maris
Thanks a lot, Brian.