Connection to external Databases via ODBC / ADO

Christian_Buehl

Connect external databases (Access, ODBC-Databases etc) via ADODB.
Hope this is useful for the community.
Other Databases should work with just a different connection string

Example for ADO-Access on external ODBC-Database
ADOConn 	Automation   'Microsoft ActiveX Data Objects 2.7 Library'.Connection
ADOrs   	Automation   'Microsoft ActiveX Data Objects 2.7 Library'.Recordset
ConnStr     	Text        200
AnzCount    	Text        30
OpenMethod  	Integer
LockMethod  	Integer
Name        	Text        200
Str         	Text        200


// Test ODBC-Connector
OpenMethod := 2; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic
LockMethod := 3; // 1=dLockreadonly; 2=adLockPessimistic; 3=adLockOptimistic; 4=adLockBatchOptimistic
CREATE(ADOconn);
ConnStr := 'PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=D:\TestDB.mdb';
ADOconn.Open(ConnStr);
CREATE(ADOrs);

ADOrs.Open('Select Name,Street from T1',ADOconn,OpenMethod,LockMethod);
ADOrs.MoveFirst;
Name := ADOrs.Fields.Item('Name').Value;
Str := ADOrs.Fields.Item('Street').Value;
// MESSAGE('Name %1 - Street %2', Name, Str);

ADOrs.MoveNext;
Name := ADOrs.Fields.Item('Name').Value;
Str := ADOrs.Fields.Item('Street').Value;
// MESSAGE('Name %1 - Strasse %2', Name, Str);
// Update current data
ADOrs.Update('Street','Testroad');
ADOrs.Update('Name','Anyone');


// Insert new Data
ADOrs.AddNew;
ADOrs.Update('Strasse','New Street Text');
ADOrs.Update('Name','New name');

ADOrs.Close;
ADOconn.Close;
CLEAR(ADOrs);
CLEAR(ADOconn);

Styvie

I would like to see if anyone has a way of reading NULL values from a db that allows nulls. I cannot test for NULL in Navision, so this ADO method presents quite a problem if you have nullable fields in the db to read from.

In the past I have created a wrapper for the ADO obs, in my own OCX where i return a text value (VB pseudo code - below),

if isnull(rs.field("Field1").value) then
FieldValue = ""
else
FieldVale = rs.field("Field1").value

ta5

Hi

You can try to replace the null value inside the select command. I guess, somethin like this should do the trick on sql server.

SELECT code, name,
COALESCE(address, '')
FROM myTable

Regards
H

Magno

hi, this is what i used:

Field.ActualSize
Syntax
put Field.ActualSize
Returns
Integer
value that indicates the actual length of a field's value.
Description
Returns the ActualSize property of the wrapped ADODB.Field object. Use the ActualSize property to return the actual length of a Field object's value. If ADO cannot determine the length of the Field object's value, the ActualSize property returns -1.