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.
0
Comments
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 ;
khan@sawindia.com
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?
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!