getting Data from Nav 3.7 to MS Access

Business Central/NAV User Forum
1

Hello Friends

please advice me for a tool which i can use it to get Data from Nav 3.7 to MS Access

Best Regards

2

Hi Rami and welcome to forum,

Depends on what database you use - Native or MS SQL.

For Native there is NODBC driver (search here in Downloads, if you do not have 3.7 install CD), for SQL there are numerous options.

3

Hello!

You could use the ADO automations in NAV.

An example:

Here is the automations you’ll use:
ADOConnection Automation ‘Microsoft ActiveX Data Objects 2.6 Library’.Connection
ADORecordSet Automation ‘Microsoft ActiveX Data Objects 2.6 Library’.Recordset

CREATE(ADOConnection);
CREATE(ADORecordSet);

filepath := ‘c:\mydatabase.mdb’;
connectionString := ‘Provider=Microsoft.Jet.OLEDB.4.0;Data Source=’+filepath+’;’;

ADORecordSet.CursorType := 3;
ADOConnection.ConnectionString := connectionString;
ADOConnection.ConnectionTimeout := 10000;
ADOConnection.CommandTimeout := 10000;

ADOConnection.Open;

sqlQuery := ‘INSERT INTO myTable (field1,field2) values (myvalues1,myvalues2)’; //string values would be surraounded with two single-apostrophe (eg. ‘‘value’’)

ADOConnection.Execute(sqlQuery, result);
message(’%1 records affected’, result);

ADOConnection.Close();

4

HI

1.Install NODBC [Available in Navision Istallation DVD]

2.Create a data source for navision databse]

The following steps show how to create a DSN.

  • Go to Control Panel and click the Administrative Tools.
  • Click Data Sources (ODBC).The ODBC Data Source Administrator window opens.
  • Click the System DSN tab and then click Add.
  • Select Microsoft Dynamics NAV Driver and then click Finish. The Dynamics NAV ODBC Setup window opens.
  • Enter the necessary information and then click OK to complete creating the DSN.

4705.2.bmp (992 KB)

3.Consume the data source for MS access

Go to Ms Access Help(F!) and search as Import or link

Data that is entered through ODBC is not validated. It is important to
ensure the data entered meets the criteria of the data type for that
field.

Thanks

Jerome Marshal.J