Connect to another DB

rkadenbachrkadenbach Member Posts: 15
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.

Comments

  • khanprg11khanprg11 Member Posts: 3
    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 ;
  • rkadenbachrkadenbach Member Posts: 15
    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?
  • AdministratorAdministrator Member, Moderator, Administrator Posts: 2,502
    Please don't post questions in this forum.
  • krikikriki Member, Moderator Posts: 9,120
    [Topic moved from Navision Tips & Tricks forum to Navision forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.