[archive] Writing/Reading Visual Foxpro files

[Migrated content. Thread originally posted on 30 September 2008]

Hello everyone,

I am working on a system in which I have to update Visual Foxpro files from an Acucobol accounting system. I have the source to the accounting system and can modify the programs.

When invoices, receipts or disbursements are posted in the accounting system, the data must also be written to their corresponding DBF equivalents.

I am replacing an old system that--seemed to--write small ASCII files and do a SYSTEM call to load a foxpro .exe that would read these files and then update the VFP dbf files.

Instead, I would like to either:

a) Open DBF files directly from AcuCOBOL (some kind of external file handler)
b) Use an ActiveX control I can call from AcuCOBOL and pass it data that it will, in turn, write to VFP.
c) Replicate from Vision files to VFP files
d) Suggestion from this forum.

Anyone done this?

Any ideas?

Thanks in Advance,
Dan

MS ADO should do the trick. There are examples of using MS ADO posted on the forum, look around.

[Migrated content. Thread originally posted on 30 September 2008]

Hello everyone,

I am working on a system in which I have to update Visual Foxpro files from an Acucobol accounting system. I have the source to the accounting system and can modify the programs.

When invoices, receipts or disbursements are posted in the accounting system, the data must also be written to their corresponding DBF equivalents.

I am replacing an old system that--seemed to--write small ASCII files and do a SYSTEM call to load a foxpro .exe that would read these files and then update the VFP dbf files.

Instead, I would like to either:

a) Open DBF files directly from AcuCOBOL (some kind of external file handler)
b) Use an ActiveX control I can call from AcuCOBOL and pass it data that it will, in turn, write to VFP.
c) Replicate from Vision files to VFP files
d) Suggestion from this forum.

Anyone done this?

Any ideas?

Thanks in Advance,
Dan

Hi Dan,

Since MS ADO(Activex Data Objects) allows data connections to ODBC data sources, you should be able to access the foxpro data provided you have the correct ODBC drivers intalled. You could also connect to foxpro using a direct driver connection string, but it's not as easy to test as an ODBC datasource.

First, to confirm the ODBC connection is working, try creating an ODBC system datasource for your foxpro database, and use an MS Office product and see if you can extract data from it. You can create an ODBC datasource in the Control Panel/Administrative Tools/Data Sources(ODBC) applet.

Second, use the Acucobol-gt axdefgen.exe tool to generate an ActiveX/COM definition copy file for the Acucobol-GT compiler.

Third, create an Acucobol-GT program that:
- references the definition(.DEF) copy file
- defines the ADO objects
- creates an ADO Connection and Recordset object instance
- connects to the ODBC datasource via the Connection object
- queries/updates foxpro data using SQL statement via the Recordset object
- traverses Recordset query results

Here's a code snippet:

121007 PROCESS-ADODB SECTION.
       ADODB-CONNECTION.
      D    STOP "DEBUG".
           CREATE  @Connection      OF @ADODB
                   HANDLE           IN hConnection.
           Create  @Recordset       OF @ADODB
                   HANDLE           IN hRecordset.
      *    STRING  "Driver={Microsoft Access Driver (*.mdb)};"
      *            "Dbq=apiv2db.mdb;"
      *            "DefaultDir=F:\\APIV2\\DocExamples\\MSADO;"
      *            "ReadOnly=False"
      *            DELIMITED        BY SIZE INTO ConnStr.
           MOVE "DSN=REV12;" TO ConnStr.
           |INSPECT ConnStr REPLACING TRAILING SPACE BY NULL.

           MODIFY  hConnection      @Open(ConnStr).
           INQUIRE hConnection      State IN WS-ConnStat.

      * This variant uses the connection as a basis for the selection
      *    MODIFY  hConnection      Execute("Select * from Contacts",
      *            WS-RecCount,     adCmdText)
      *            GIVING           hRecordset.

           IF      WS-ConnStat      = adStateOpen
                   PERFORM ADODB-QUERY
                   PERFORM ADODB-CLOSE
                   PERFORM ADODB-DESTROY
           ELSE
                   PERFORM ADODB-DESTROY.


       ADODB-QUERY.
           MODIFY  hRecordset       @MaxRecords = 100.
           MODIFY  hRecordset       @Open(
              BY NAME @Source
              "Select employee_number, applicant_name FROM TAPPL ORDER B
      -        "Y applicant_name"
              BY NAME @ActiveConnection hConnection,
              BY NAME @Options adCmdText).
           MODIFY  hRecordset       @MoveFirst().
           INQUIRE hRecordset       EOF IN WS-ConnStat.
           PERFORM UNTIL            WS-ConnStat = -1

                   INITIALIZE WS-STRING, WS-FIELD1, WS-FIELD2
                   INQUIRE       hRecordSet
                                 fields::item("applicant_name")::value
                                 IN WS-FIELD1
                                 fields::item("employee_number")::value
                                 IN WS-FIELD2

                   INITIALIZE       WS-STRING

                   STRING           WS-FIELD1 DELIMITED BY SPACE
                                    ", " DELIMITED BY SIZE
                                    WS-FIELD2 DELIMITED BY SPACE
                                    INTO WS-STRING

                   |DISPLAY          WS-String(1:100)

                   IF               WS-ConnStat NOT = -1
                                    MODIFY  hRecordset MoveNext()
                                    END-IF

                   INQUIRE          hRecordset EOF IN WS-ConnStat

           END-PERFORM.
           |ACCEPT  OMITTED.

       ADODB-CLOSE.
           MODIFY  hRecordset       @Close().
           MODIFY  hConnection      @Close().
       ADODB-DESTROY.
           DESTROY hRecordset.
           DESTROY hConnection.

       MAIN-900.
      *Finish up.
           GOBACK.