Microsoft access - Forum - OpenEdge General - Progress Community
 Forum

Microsoft access

This question is not answered

Hi to all

What's the best way to connect OE 11.7 with Microsoft access dB?

Many thanks.

G.

All Replies
  • Are you talking about doing it in the ABLlanguage?

    In general the OE ABL language has little in the way of accessing data outside of its own OE database.  Some of the types of resources it can connect to are:  OE database, local file system, JMS message brokers, soap/rest services.  It does have something called "DataServer" to connect to SQL Server and Oracle but that option will cost quite a bit, and it has a lot of moving parts that you might not expect (ie. "schema holders").  

    If you are on then Windows platform, then I'd suggest you do all the Microsoft access stuff outside of ABL (eg. in a local command line program or WCF service).  You could redirect the input/output via the file system in a pinch.

  • and ActiveX Automation (original poster should consider using ADO if they want to interact with Access from the ABL)
     
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     
    ProgressNext2018_Email_Signature
     

  • You can read and write using the System.Data.OleDB.*. class.  Here is a small example of a procedure to write to an access db table.  

    /* ***************************  Definitions  ************************** */

    DEFINE VARIABLE Conn       AS CLASS     System.Data.OleDb.OleDbConnection  NO-UNDO.

    DEFINE VARIABLE Cmd        AS CLASS     System.Data.OleDb.OleDbCommand     NO-UNDO.

    DEFINE VARIABLE Adapter    AS CLASS     System.Data.OleDb.OleDbDataAdapter NO-UNDO.

    DEFINE VARIABLE Reader     AS CLASS     System.Data.OleDb.OleDbDataReader  NO-UNDO.

    DEFINE VARIABLE Writer     AS CLASS     System.Data.OleDb.OleDbTransaction NO-UNDO.

    DEFINE VARIABLE chrPicPath AS CHARACTER NO-UNDO.

    DEFINE VARIABLE chrQuery   AS CHARACTER NO-UNDO.

    /* ********************  Preprocessor Definitions  ******************** */

    /* ***************************  Main Block  *************************** */

    FOR EACH emp-mstr NO-LOCK

    where emp-mstr.psoftid <> ?.

       ASSIGN

           chrPicPath = "I:\Personnel\Employee Pictures\" + string(emp-Mstr.psoftid,'999999') + ".jpg"

           chrQuery = "Insert into Employee ([EmpID],[PSoftID],[FName],[PrefFName],[LName],[DeptID],[NasEmp],[PicPath],[CSC-Member],[FAT-Member],[FC-Member],[FRT-Member],[TMatics],[Active]) values (" + string(emp-mstr.empid) + "," + string(emp-mstr.psoftid) + ",'" + emp-mstr.fname + "','" + emp-mstr.Preffname + "','" + emp-mstr.lname + "'," + string(emp-mstr.deptid) + "," + string(emp-mstr.nasemp) + ",'" + chrPicPath + "'," + string(emp-mstr.csc-member) + "," + string(emp-mstr.fat-member) + "," + string(emp-mstr.fc-member) + "," + string(emp-mstr.frt-member) + "," + string(emp-mstr.tmatics) + "," + string(emp-mstr.active) + ");".

       Conn = NEW System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=H:\Zebra_Cardstudio\nascote.mdb;").

       Conn:Open().

       Writer = Conn:BeginTransaction(System.Data.IsolationLevel:Unspecified).

       Cmd = NEW System.Data.OleDb.OleDBCommand(chrQuery,conn,Writer).

       Cmd:CommandText = chrQuery.

       Cmd:ExecuteNonQuery().

       Writer:Commit().

       Conn:Close().

    END.

  • Hi,

    thanks for your help.

    What's I can find System.Data.OleDB.*. class. ?

    Thx a lot

  • You need to add System.Data to assemblies.xml using the Progress Assembly References program.

    You should see it in the Global Assemblies tab.

  • Thanks.

  • Hello,

    is it possible to create dinamically table and field (from DB OE) on ACCESS DB using Sistem.Data?

    Thx

  • Hi Scott,

    do you suggest manual/document in order to use OE with ADO,NET. I need more example.

    many thanks.

    G.

  • Sure, but you have to write the code. We do that today with a SQLite DB.

    By using the BUFFER object handle you have access to all the buffer fields.

    DO I = 1 TO buffer-object:NUM-FIELDS:

       hBufferField = buffer=object:BUFFER-FIELD(I).

       /*

       By using the BUFFER-FIELD handle you have access to DATA-TYPE, NAME, FORMAT, DEFAULT-VALUE, etc.

       */  

    END.

    You would have to create class to use the System.Data.OleDb.OleDbCommand to create the table/fields.

  • Hi Roger,

    thanks for help.

    I have a TEMP-TABLE to export in MDB. Is it possible too?

    Thx.

    G.

  • TT buffer is no different than DB table buffer.

  • Hi,

    I have problem to load data with this separator " ' ". Some fields description contains this character.

    Is it possibie to change?

    Thx.