ConnectToMicrosoftAccess

From Pickwiki
Jump to: navigation, search

To use this you will need to setup a datasource on the client under Control Panel. The name of datasource should be what you use in the SQLConnect statement for the access database.

$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
      *-----------------------------------------------------------------------
      *    Preparing the SQL processes
      *-----------------------------------------------------------------------

      SQL.OK = TRUE                      ; * error flag for sql processes
      SQL.STATUS = [[ClearDiagnostics]]()

      * setup the ODBC connection to the TEST.mdb file
      SQL.STATUS = [[SQLAllocConnect]](@HENV,TEST.MDB.CONNECT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not establish a connection to the Access workspace: "
         MESSAGE := "Failed to allocate a CONNECTION environment"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END
      IF SQL.OK THEN
         GOSUB MDB.CONNECT
      END                                ; * endif sql.ok (TEST.MDB.CONNECT)

      * Setup the ODBC connection to the LOCAL UV account
      SQL.STATUS = [[SQLAllocConnect]](@HENV,TEST.UV.CONNECT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not establish a connection to the Universe Database environment: "
         MESSAGE := "Failed to allocate a CONNECTION environment"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END
      IF SQL.OK THEN
         SQL.STATUS = SQLConnect(TEST.UV.CONNECT,"localuv",'','')
         IF SQL.STATUS <> SQL.SUCCESS THEN
            MESSAGE = "Could not establish a connection to the Universe Database environment: "
            MESSAGE := "Failed to connect to data source"
            GOSUB ERROR.PROCESS
            SQL.OK = FALSE
         END
      END                                ; * endif sql.ok (TEST.UV.CONNECT)

      * setup customer table transfer sql statement environments
      SQL.STATUS = [[SQLAllocStmt]](TEST.UV.CONNECT,CUSTOMER.SEL.STMT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the CUSTOMER file in Universe: "
         MESSAGE := "Failed to allocate a STATEMENT environment (select)"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END

      SQL.STATUS = [[SQLAllocStmt]](TEST.MDB.CONNECT,CUSTOMER.INS.STMT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the CUSTOMER file in Universe: "
         MESSAGE := "Failed to allocate a STATEMENT environment (insert)"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END

      * SQL statement objects to access TEST.MDB parts Allocations table
      SQL.STATUS = [[SQLAllocStmt]](TEST.MDB.CONNECT,TEST.PARTS)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the TEST parts allocations table in TEST.mdb: "
         MESSAGE := "Failed to allocate a STATEMENT environment"
         GOSUB ERROR.PROCESS
      END

      * SQL statement objects to access TEST.MDB Order Allocations table
      SQL.STATUS = [[SQLAllocStmt]](TEST.MDB.CONNECT,TEST.ORDERS)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the TEST Orders Allocations table in TEST.mdb: "
         MESSAGE := "Failed to allocate a STATEMENT environment"
         GOSUB ERROR.PROCESS
      END

      IF NOT(SQL.OK) THEN
         CLOSE TESTF
         STOP
      END                                ; * endif not(sql.ok) ...
      *-------------------------------------------------------------------
      *  End of SQL Preparation
      *-------------------------------------------------------------------

      LOOP
         SQL.STATUS = [[ClearDiagnostics]]()
         SQL.OK = TRUE

         GOSUB RESET.SQL.STATEMENTS
         GOSUB PREPARE.SQL.INSERT.COMMANDS
         IF NOT(SQL.OK) THEN EXIT

         SQL.STATUS = [[ClearDiagnostics]]()

         ' code removed
      UNTIL RET.VALUE<1> = "CANCEL" DO
         TEST.ID = PART.NO:"*":LOCATION

         * get the inventory balance and part description
         INV = RAISE(TRANS('INV',PART.NO,-1,'X'))
         LOCATE(LOCATION,INV,2;LOC.VMC) THEN
            INV.BAL = OCONV(INV<3,LOC.VMC>,'MR4')
         END ELSE
            INV.BAL = 0
         END                             ; * endlocat(location,inv ....
         PART.DESC = TRANS('PARTS',PART.NO,1,'X')

         * calculate header totals
         ALLOC = OCONV(SUM(TEST<14>),'MR4')
         ON.ORDER = OCONV(SUM(TEST<4>), 'MR4')
         BACKORDER = ON.ORDER - ALLOC
         NUM.ORDER = 0
         NUM.CUST = 0

         * select the customer data & add to TEST.mdb database
         SQL.STATUS = SQLExecute(CUSTOMER.SEL.STMT)

         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS = SQLError(@HENV,TEST.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not execute the SQL Selection of TEST Customer data!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
            SQL.OK = FALSE
            GOSUB ERROR.PROCESS
            EXIT
         END                             ; * endif sql.status # sql.success ....
         LOOP
            SQL.STATUS = SQLFetch(CUSTOMER.SEL.STMT)
         UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO
            SQL.STATUS = SQLExecute(CUSTOMER.INS.STMT)
            IF SQL.STATUS # SQL.SUCCESS THEN
               SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
               MESSAGE = "Could not insert record into the TEST.mdb DATABASE, Customer table!  "
               MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
               SQL.OK = FALSE
               GOSUB ERROR.PROCESS
               EXIT
            END                          ; * endif sql.status # sql.success ....
            NUM.CUST += 1
         REPEAT
         IF NOT(SQL.OK) THEN EXIT

         * update the TEST order allocation table (line item detail of allocations)
         LINE.CNT = DCOUNT(TEST<1>,@VM)
         SO.LIST = ''
         FOR IDX = 1 TO LINE.CNT
            ALLOC.LINE = TEST<1,IDX>
            SO.NO = TEST<2,IDX>["*",1,1]
            SO.LINO = TEST<2,IDX>["*",2,1]
            CUST.NO = TEST<6,IDX>
            SCHED.DATE = OCONV(TEST<3,IDX>,'D4\')
            SCHED.QTY = OCONV(TEST<4,IDX>,'MR4')
            ALLOC.QTY = OCONV(TEST<14,IDX>,'MR4')
            PRICE = OCONV(TEST<5,IDX>,'MR4')

            LOCATE(SO.NO,SO.LIST;DUMMY) ELSE SO.LIST<-1> = SO.NO

            SQL.STATUS = SQLExecute(TEST.ORDERS)

            IF SQL.STATUS # SQL.SUCCESS THEN
               SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
               MESSAGE = "Could not insert orders allocation data into the TEST.MDB file!  "
               MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
               GOSUB ERROR.PROCESS
               SQL.OK = FALSE
               EXIT
            END                          ; * endif sql.status # sql.success ....
         NEXT IDX
         IF NOT(SQL.OK) THEN EXIT

         * update TEST part allocation table data
         NUM.ORDER = DCOUNT(SO.LIST,@AM)
         * load the Parts allocation table in TEST.mdb
         SQL.STATUS = SQLExecute(TEST.PARTS)

         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not insert parts allocation data into the TEST.MDB file!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
            SQL.OK = FALSE
            GOSUB ERROR.PROCESS
            EXIT
         END                             ; * endif sql.status # sql.success ....

         * reset just the TEST.ORDERS statement environment
         SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
         SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.UNBIND)
         SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.RESET.PARAMS)

         NAP 500
* ====> CALL EXTERNAL CLIENT PROGRAM

         IF STATUS = 1 THEN
            SLEEP 3
         END ELSE
            MESSAGE = "Could not start [[TestClient]] program on workstation"
            MESSAGE<2> = MB.ERROR
            EXIT
         END                             ; * endif status = 1 (pix.win.run call)

         * reopen the test data source connection
         * retrieve updated test allocations from the mdb file
         GOSUB PREPARE.SQL.RESULTS.COMMANDS
         IF SQL.OK THEN
            SQL.STATUS = SQLExecute(TEST.ORDERS)

            IF SQL.STATUS # SQL.SUCCESS THEN
               SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
               MESSAGE = "Could not execute the SQL Selection against TEST.mdb "
               MESSAGE := "[[OrderAllocations]] table data!  "
               MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
               SQL.OK = FALSE
               GOSUB ERROR.PROCESS
            END                          ; * endif sql.status # sql.success ....
            IF SQL.OK THEN
               LOOP
                  SQL.STATUS = SQLFetch(TEST.ORDERS)
               UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO
                  LOCATE(ALLOC.LINE,TEST,1;TEST.IDX) THEN
                     IF (SO.NO:"*":SO.LINO) = TEST<2,TEST.IDX> THEN
                        TEST<14,TEST.IDX> = ICONV(ALLOC.QTY,'MR4')
                     END ELSE
                     END                 ; * endif (so.no:"*":so.lino) = test<2,test.idx> ....
                  END                    ; * endlocate(alloc.line,....
               REPEAT
               TEST<17> = SUM(TEST<4>) - SUM(TEST<14>)
            END                          ; * endif sql.ok (select of [[OrderAllocations]])
         END                             ; * endif sql.ok (prepare sql results)
         GOSUB CLEANUP.MDB

         IF SINGLE.UPDATE THEN EXIT      ; * break out of loop if single update only
      REPEAT

      GOSUB CLEANUP.MDB                  ; * insure that we have cleaned up our workspace
*
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.DROP)
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.DROP)
      SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.DROP)
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.DROP)
      SQL.STATUS = SQLDisconnect(TEST.UV.CONNECT)
      SQL.STATUS = [[SQLFreeConnect]](TEST.UV.CONNECT)
      SQL.STATUS = SQLDisconnect(TEST.MDB.CONNECT)
      SQL.STATUS = [[SQLFreeConnect]](TEST.MDB.CONNECT)
*
      STOP
*
* ===================================================================
* <Routines>:
* ===================================================================
PREPARE.SQL.INSERT.COMMANDS:* Load sql statement environments appropriate
      * to loading TEST.MDB

      * Prepare UV customer file selection command for retreiving customer data to
      * be loaded into the Customer table in TEST.MDB
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.SEL.STMT,1,SQL.B.BASIC,SQL.CHAR,25,0,TEST.ID)
      SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,1,SQL.B.DEFAULT, CUSTNO)
      SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,2,SQL.B.DEFAULT, NAME)
      SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,3,SQL.B.DEFAULT, CITY)
      SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,4,SQL.B.DEFAULT, STATE)
      SQL.STATUS = [[SQLBindCol]](CUSTOMER.SEL.STMT,5,SQL.B.DEFAULT, ZIP)
      CUST.SEL.CMD = "SELECT DISTINCT [[CUST_NO]],NAME,CITY,STATE,ZIP FROM CUSTOMER "
      CUST.SEL.CMD := "WHERE [[CUST_NO]] IN (SELECT [[CUST_NO]] FROM [[TEST_TEST_L0]] WHERE @ID = ?)"
      SQL.STATUS = SQLPrepare(CUSTOMER.SEL.STMT,CUST.SEL.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL select command for the TEST customer data!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status # sql.success ....

      * Prepare the SQL insert command to load the customer data into the Customer table
      * of the TEST.MDB file.  Note the binding of parameters to variable names.
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,2,SQL.B.BASIC,SQL.CHAR,10,0,CUSTNO)
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,3,SQL.B.BASIC,SQL.CHAR,30,0,NAME)
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,4,SQL.B.BASIC,SQL.CHAR,30,0,CITY)
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,5,SQL.B.BASIC,SQL.CHAR,2,0,STATE)
      SQL.STATUS = [[SQLBindParameter]](CUSTOMER.INS.STMT,6,SQL.B.BASIC,SQL.CHAR,10,0,ZIP)
      CUST.INS.CMD = "INSERT INTO Customer ([[Port_No]],[[Customer_No]],Name,City,State,[[ZipCode]]) "
      CUST.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?)"
      SQL.STATUS = SQLPrepare(CUSTOMER.INS.STMT,CUST.INS.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Insert command for the customer table!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status # sql.success ....

      * Prepare the SQL insert command to load the [[PartsAllocation]] Table of the
      * TEST.MDB file. Note the variable bindings.
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,4,SQL.B.BASIC,SQL.CHAR,30,0,PART.DESC)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,5,SQL.B.BASIC,SQL.REAL,12,4,INV.BAL)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,6,SQL.B.BASIC,SQL.REAL,12,4,ALLOC)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,7,SQL.B.BASIC,SQL.REAL,12,4,ON.ORDER)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,8,SQL.B.BASIC,SQL.REAL,12,4,BACKORDER)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,9,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.ORDER)
      SQL.STATUS = [[SQLBindParameter]](TEST.PARTS,10,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.CUST)
      PART.INS.CMD = "INSERT INTO [[PartsAllocation]] ([[Port_No]],[[Part_No]],[[Inventory_Location]],"
      PART.INS.CMD := "[[Part_Description]],[[Inventory_Balance]], Allocated, [[On_Order]], "
      PART.INS.CMD := "Backorder,[[Order_Count]],[[Customer_Count]]) "
      PART.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      SQL.STATUS = SQLPrepare(TEST.PARTS,PART.INS.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Insert command for the [[PartsAllocations]] table!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status # sql.success ....

      * Prepare the SQL insert command to load the [[OrderAllocationS]] Table of the
      * TEST.MDB file. Note the variable bindings.
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,2,SQL.B.BASIC,SQL.INTEGER,6,0,ALLOC.LINE)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,3,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,4,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,5,SQL.B.BASIC,SQL.CHAR,6,0,SO.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,6,SQL.B.BASIC,SQL.SMALLINT,5,0,SO.LINO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,7,SQL.B.BASIC,SQL.CHAR,10,0,CUST.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,8,SQL.B.BASIC,SQL.DATE,10,0,SCHED.DATE)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,9,SQL.B.BASIC,SQL.REAL,10,4,SCHED.QTY)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,10,SQL.B.BASIC,SQL.REAL,10,4,ALLOC.QTY)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,11,SQL.B.BASIC,SQL.REAL,10,4,PRICE)
      ORDER.INS.CMD = "INSERT INTO [[OrderAllocations]] ([[Port_No]],[[Alloc_Line]],[[Part_No]],"
      ORDER.INS.CMD := "[[Inventory_Location]],[[Sales_Order]], [[Sales_Order_Line]], [[Customer_No]],"
      ORDER.INS.CMD := "[[Schedule_Date]],[[Schedule_Quantity]],[[Allocated_Quantity]],[[Unit_Price]]) "
      ORDER.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)"
      SQL.STATUS = SQLPrepare(TEST.ORDERS,ORDER.INS.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Insert command for the [[OrdersAllocations]] table!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status # sql.success ....

      RETURN
* ===================================================================
PREPARE.SQL.RESULTS.COMMANDS:* Load sql statement envrionments appropriate
      * to retrieving the altered data from TEST.MDB
      * reset just the TEST.ORDERS statement environment
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.UNBIND)
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.RESET.PARAMS)


      * build SQL select statement to retrieve Order allocation lines to load any
      * allocation adjustments into the UV TEST file
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
      SQL.STATUS = [[SQLBindParameter]](TEST.ORDERS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
      SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,1,SQL.B.DEFAULT, ALLOC.LINE)
      SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,2,SQL.B.DEFAULT, SO.NO)
      SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,3,SQL.B.DEFAULT, SO.LINO)
      SQL.STATUS = [[SQLBindCol]](TEST.ORDERS,4,SQL.B.DEFAULT, ALLOC.QTY)
      READ.ALLOC.CMD = "SELECT [[Alloc_Line]],[[Sales_Order]],[[Sales_Order_Line]],[[Allocated_Quantity]] FROM [[OrderAllocations]] "
      READ.ALLOC.CMD := "WHERE [[Port_No]] = ? and [[Part_No]] = ? and [[Inventory_Location]] = ?"
      SQL.STATUS = SQLPrepare(TEST.ORDERS,READ.ALLOC.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL select statment to retrieve test order allocations!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status # sql.success ....

      RETURN
* ===================================================================
RESET.SQL.STATEMENTS:* reset and clear the statement environments

      * reset the customer selection statement environment
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.CLOSE)
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.UNBIND)
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.SEL.STMT,SQL.RESET.PARAMS)

      * reset the customer table insert statement environment
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.CLOSE)
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.UNBIND)
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.RESET.PARAMS)

      * reset the parts allocation table environment
      SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.CLOSE)
      SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.UNBIND)
      SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.RESET.PARAMS)

      * reset the order allocation table environment
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.UNBIND)
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.RESET.PARAMS)

      RETURN
* ===================================================================
CLEANUP.MDB:* commands to cleanup the TEST.mdb file for this users data

      * clean up order allcation data
      SQL.STATUS = [[SQLFreeStmt]](TEST.ORDERS,SQL.CLOSE)
      SQL.STATUS = SQLPrepare(TEST.ORDERS,"DELETE FROM [[OrderAllocations]] NOWAIT where [[Port_No]] = ?")
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Delete command for the [[OrderAllocations]] table, table not cleared!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END ELSE
         SQL.STATUS = SQLExecute(TEST.ORDERS)
         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not clear the [[OrderAllocations]] table for the current user!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
            GOSUB ERROR.PROCESS
            SQL.OK = FALSE
         END                             ; * endif sql.status # sql.success ....
      END                                ; * endif sql.status # sql.success ....

      * clean up parts allcation data
      SQL.STATUS = [[SQLFreeStmt]](TEST.PARTS,SQL.CLOSE)
      SQL.STATUS = SQLPrepare(TEST.PARTS,"DELETE FROM [[PartsAllocation]] NOWAIT where [[Port_No]] = ?")
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Delete command for the [[PartsAllocation]] table, table not cleared!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END ELSE
         SQL.STATUS = SQLExecute(TEST.PARTS)
         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,TEST.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not clear the [[PartsAllocation]] table for the current user!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
            GOSUB ERROR.PROCESS
            SQL.OK = FALSE
         END                             ; * endif sql.status # sql.success ....
      END                                ; * endif sql.status # sql.success ....

      * clean up customer data
      SQL.STATUS = [[SQLFreeStmt]](CUSTOMER.INS.STMT,SQL.CLOSE)
      SQL.STATUS = SQLPrepare(CUSTOMER.INS.STMT,"DELETE FROM Customer NOWAIT where [[Port_No]] = ?")
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Delete command for the customer table, table not cleared!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
      END ELSE
         SQL.STATUS = SQLExecute(CUSTOMER.INS.STMT)
         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS = SQLError(@HENV,TEST.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not clear the customer table for the current user!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
            GOSUB ERROR.PROCESS
         END                             ; * endif sql.status # sql.success ....
      END                                ; * endif sql.status # sql.success ....

      RETURN
* ===================================================================
MDB.CONNECT:* routine to open a connection to the TEST data source

      SQL.STATUS = SQLConnect(TEST.MDB.CONNECT,"TEST",'','')
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not (re)establish a connection to the Access workspace: "
         MESSAGE := "Failed to connect to data source"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END

      RETURN
* ===================================================================
ERROR.PROCESS:* error reporting loop


      RETURN
*
* ===================================================================
*
*
* <End>:

This was originally posted on u2-users, and the author provided a cleaned-up version for the Wiki.

http://www.mail-archive.com/u2-users%40listserver.u2ug.org/msg04593.html