Access Script

Hi Rocks

I achieved this by using the FTD wizard silently from a program.

This is the code:-

      ************************ ***********************
      *** Copy data to the Access Database table "Header" ***
      ************************ ***********************
      DATABASE.NAME='\Delivery -Notes.mdb'
      TABLE.NAMES='!Header'
      COLUMN.NAMES='OrderNo Version AccountCode OrderDate DeliveryDate YourRef Operator'
      ITEMS.TO.INCLUDE=DELIVER Y.NOTES
      
      ************************ ****************
      **** Copy the Delivery Note Details data to the ****
      **** Access Database table "Details" ****        **********************************
      TABLE.NAMES<2>='!D etails'
      COLUMN.NAMES<2>='O rderNo Version ItemCount Product Description PackSize Bin PackOrd SglOrd '
      COLUMN.NAMES<2>=CO LUMN.NAMES<2>:'PackSent SglSent Supplier'
      ITEMS.TO.INCLUDE<2> ;=DELIVERY.NOTES
      
      ************************ ************************
      NOA=DCOUNT(TABLE.NAMES,@ AM)
      
      FOR I=1 TO NOA
        IF ITEMS.TO.INCLUDE<I> NE "" THEN
             EXPORT.ITEMS=SWAP(ITEMS.TO.INCLUDE<I>,@VM, @AM)
             
             WRITE EXPORT.ITEMS ON POINTER.FILE,ITEM.NAME
             
             EXECUTE 'GET-LIST ':ITEM.NAME
             
             HEADER.FILE=DATABASE.NAME:TABLE.NAMES<I>
             DATA=COLUMN.NAMES<I>
             
             DATA 'S','K',EXPORT.FILENAME,'',HEADER.FILE,DATA,'Y','Y'
             EXECUTE 'FTD' CAPTURING OUT
        END
      NEXT I

DELIVERY.NOTES is a multi-valued list of keys of the items that you want to export. You need to get the keys into a selected list to be used by the FTD command which is done by replacing the @VM with @AM and then writing it out as one item to the POINTER-FILE. The GET-LIST then includes all the items that you want to export to ACCESS.

This is one way of getting data into ACCESS but there may be others that are quicker, I hope this helps.

Calidore

First of all, I would like to thanks Calidore for posting valuable script.

   ************************ ****************
*** Copy data to the Access Database tableS ***
   ************************ ****************
    *MS Access Database and TableS
    DATABASE.NAME='C:\Test.mdb'
    TABLE.NAMES='!Customers'
    TABLE.NAMES<2>='!Staffs'
    TABLE.NAMES<3>='!Reps'
     *Pick Database File Name
     FILE.NAME='Customers'
     FILE.NAME<2>='Staffs'
     FILE.NAME<3>='Reps'
    * Specified the Attributes that you want to transfer to the Access table       
    ATT.NAME='CUSNO NAME STREET SUBURB STATE PCODE EMAIL'
    ATT.NAME<2>='CUSNO CONTACT ADDRESS1 PHONE EMAIL
    ATT.NAME<3>='REPID NAME PHONE MOBILE.PHONE EMAIL'
    * Specified the Select statement here
    SELECT.STAT='SELECT Customers WITH A1 = "A]" '
    SELECT.STAT<2>='SELECT Staffs WITH A3 = "A]"'
    SELECT.STAT<3>='SELECT Reps WITH A0 = "M]"'
   *Set the loop value
   EndLoop = 3
   *Loop FTD process untill I equal to EndLoop
   FOR I = 1 TO EndLoop
      IF I <= EndLoop THEN   
        *Execute Select statment, save the select list and Get all the data
        EXECUTE SELECT.STAT<I>
        EXECUTE 'SAVE-LIST GMTEST'
        EXECUTE 'Get-LIST GMTEST'
        *Merge Database to the table
        HEADER.FILE=DATABASE.NAME:TABLE.NAMES<I>
        ATTRIBUTES=ATT.NAME<I>
*Execute FTD command with the following paremeters
*This command will overwirte the specified table if table doesn't exist its creates new table
*Send, Ascii,Source file,Source item list, Target file, Attribute, Header Record,MultiValue Fields
*If you leave item list empty it will transfer all the records from Save-list
        DATA 'S', 'A',FILE.NAME<I>,' ',HEADER.FILE,ATTRIBUTES,'Y','Y'
        EXECUTE 'FTD'
      END
     NEXT I
*End of program   

Above script works perfectly if you have same attributes name in Access and Pick database. But in my case I’ve to setup different attributes name in Access. Does anyone know how to link Pick attributes to Access. For example in Access Customer table has CusId, CusName etc. and in Pick Customer File has CusNo, Name etc. I want to export data to Access table so the data from the CusNo goes to CusId and Name to CusName. I know it is possible with Accuterm Download Wizard but that is not a option for my project.

Cheers
Rocks