Connect to another DB

rkadenbach

Hello,
Does anybody know, if it is possible to access any odbc-capable database through Navision C/AL-Code?
We have to access a Document Managment System which is separate from Navision-DB. We could write a program in SQL, but this query needs too much time.

khanprg11

rkadenbach ,

you can connect to outside odbc database like this


// Create one Database temp.mdb (in Access 2000) and create one Student Table in Database
// Please Create that database at your <C:\> Directory
// Student Table has two fields
// First Field is ID (Text Data Type)
// Second Field is Name (Text Data Type)
// And I Inserted 3 Records in That Table ( Please Insert Three Records)

// Now Close Accsess Database


then you need to create Following variable in Navision

varRecordset 'Microsoft ActiveX Data Objects Recordset 2.5 Library'.Recordset // Data type will be Automation ( Recordset Variable)
varFldCollection Automation 'Microsoft ActiveX Data Objects Recordset 2.5 Library'.Fields // Data type will be Automation (Fields Collection Variable)

varFldObject Automation 'Microsoft ActiveX Data Objects Recordset 2.5 Library'.Field // Data type will be Automation (Field Object Variabe)

strSql 30 // Data Type will be Text
StrConnString 300 // Data Type Will be Text
Name 30 // Data type will be text
Id 30 // Data type will be text



//Connecting to database code will look like this




CREATE(varRecordset);
strSql:= 'Select * from Student';
StrConnString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp.mdb;Persist Security Info=False ';

varRecordset.CursorType(0); // Open recordset Dynamically
varRecordset.CursorLocation(1); //Lock Type optimistic
varRecordset.Open (strSql ,StrConnString); // Connection String


// Now following routine will rotate through full table data


REPEAT
varFldCollection := varRecordset.Fields; // Extracting Fields Collection From Recordset and Storing in Fields Collection Object

varFldObject:= varFldCollection.Item(0); // Extarcting First Field from Fields Collection Object

Id:= varFldObject.Value; // Storing Field Object Value in Variable
varFldObject:= varFldCollection.Item(1); // Extarcting Second Field from Fields Collection Object

Name := varFldObject.Value; // Storing Field Object Value in Variable
MESSAGE ('Student ID [ %1] , Student Name [ %2]',Id,Name); // Dispaly Record

varRecordset.MoveNext // moving to next record


UNTIL varRecordset.EOF=TRUE ;

rkadenbach

Hello khanprg11,
thanks.

I´m not firm in Automation, but it works. Now the problem: to access the extern DB with password through our LAN. I think, i have to use Automation 'Microsoft ActiveX Data Objects 2.x Library.Record', but everytime i got an error like 'The DB is exclusiv open by another user', although there isn´t another user. I tried above-written Automation and StrConnString := 'Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=True ;Data Source=C:\Student.mdb;Password = abc';, but i doesn´t work.
Can you help, please?

Administrator

Please don't post questions in this forum.

kriki

[Topic moved from Navision Tips & Tricks forum to Navision forum]