Using ADO to access outside data

ADO stands for ActiveX Data Object and is used to access data regardless of its structure. Here we will show how you can use an ADO Automation in NAV to access data from an outer database.

How to do it...

1. Create a new codeunit in Object Designer.

2. Add a function named CreateConnectionString that takes in the following parameters:

   Name	DataType	Length
   ServerName	Text	50
   DatabaseName	Text	50
   UserName	Text	50
   Password	Text	50

3. The function should return a text value of length 1024.

4. Add the following code to the function:

   EXIT(
   'Driver={SQL Server};' + 'Server=' + ServerName + ';' + 'Database=' + DatabaseName + ';' + 'Uid=' + UserName + ';' + 'Pwd=' + Password + ';');
   

5. Add the following local variables to the OnRun trigger:

   Name	DataType	SubType
   ADOConnection	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Connection
   ActiveADOConnection	Variant
   ADOCommand	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Command
   ADORecordSet...