Warehousing the PRODUCTS Table to Microsoft Access
Below is sample code to warehouse the PRODUCTS table, in the EXAMPLES sample application, to a Microsoft Access table named OI_PRODUCTS in the NWIND database. The logic flow (creating the connection handle and the query handle, building the query string, executing the query, and destroying the connection handle and query handle) is similar to previous examples. Specific considerations for warehousing to Microsoft Access are discussed in the next topic.
declare subroutine msg $insert XO_Equates $insert Msg_Equates $insert logical * returns @rm/@fm delimited results Results = "" Equate CRLF$ to char(13):char(10) hXO = XOInstance ("NWIND", "ODBC", '', '', '', XO_NOTRANS$) hQry = 0 if hXO then * create the query handle for the connection handle hQry = QryInstance(hXO) if hQry then * drop OI_PRODUCTS Script = "drop table oi_products" Flag = QryMethod(hQry, QRY_EXECUTE$, Script) if Flag then call msg(@window, 'OI_PRODUCTS table dropped successfully!') end else gosub error end * create the OI_PRODUCTS table Script = "create table oi_products (" Script := " id varchar(22) primary key not null, " Script := " description varchar(50), " Script := " price currency, " Script := " image_filename text(50), " Script := " order_date date);" Flag = QryMethod(hQry, QRY_EXECUTE$, Script) if Flag then call msg(@window, 'OI_PRODUCTS table created successfully!') end else gosub error end * populate the OI_PRODUCTS table from the PRODUCTS table. open 'PRODUCTS' to products_filevar then open 'DICT.PRODUCTS' to @dict then select products_filevar done = FALSE$ loop readnext @id else done = TRUE$ until done = TRUE$ read @record from products_filevar, @id then Script = "insert into oi_products " Script := "(id, description, price, image_filename, order_date) values (" Script := "'" : @ID : "', " Script := "'" : {DESCRIPTION} : "', " Script := "'" : oconv({PRICE},'MD2,$'): "', " Script := "'" : {IMAGE_FILENAME} : "', " order_date = {ORDER_DATE} if order_date = '' then Script := "NULL)" end else Script := "'" : oconv({ORDER_DATE},"D4/") : "')" end Flag = QryMethod(hQry, QRY_EXECUTE$, Script) if Flag else gosub error end end repeat end end * close the query handle Flag = QryMethod(hQry, QRY_DESTROY$) end else gosub error end * close the connection Flag = XOMethod(hXO, XO_DESTROY$) end else gosub error end return 0 * Error handling Error: if hQry then Flag = QryMethod(hQry, QRY_GETERROR$, "", "", "",| "",Text) end else Flag = XOMethod(hXO, QRY_GETERROR$, "", "", "", | "",Text) end convert @vm to @tm in Text Def = "" Def<MTEXT$> = Text Def<MCAPTION$> = "Script Execution Error" Def<MICON$> = "!" Msg(@window, Text) Results = "" return