APPX Software Library

Chapter 5: Read APPX Data with Excel, Access and LibreOffice

Load COUNTRY into Excel and Refresh It

Use the Legacy Microsoft Query route in 32-bit Excel. If the command is hidden, open File → Options → Data and enable From Microsoft Query (Legacy) under Show legacy data import wizards.

  1. Create a blank workbook and select Data → Get Data → Legacy Wizards → From Microsoft Query (Legacy).
  2. On Databases, select AppxODBC, keep Use the Query Wizard to create/edit queries checked, and select OK.
  3. Enter the APPX user and password in the Dharma login dialog.
  4. In the Query Wizard, expand dmo_dmo_country and add its nine columns to Columns in your query.
  5. Select Next, keep the first sample unfiltered, and sort by name.
  6. Select Next, keep Return Data to Microsoft Excel selected, and select Finish.
  7. In Import Data, choose Table, choose cell $A$1 on the existing worksheet, and select OK.
  1. Wait until Excel returns to Ready. The worksheet contains 7 data rows and 9 columns, plus the heading row.
  2. Select Table Design → Refresh, wait for Ready, and check the same seven complete records.
  3. Save the workbook locally. On reopening, refresh the external query and supply the APPX login when prompted.

Compare the saved worksheet with the native APPX sample after converting Excel date serials to dates. The workbook retains an external query that can refresh. Leave password saving disabled.

The fresh workbook was saved, reopened and refreshed through AppxODBC. All seven records and nine fields match the independently read native APPX sample, including dates and blank fields. No password is saved in the workbook connection.

The saved 5.5.1 workbook after a successful refresh.
Figure 9. The saved 5.5.1 workbook after a successful refresh.

A second login during the first import

The query wizard and the worksheet use separate connections. On the first return to Excel, the tested driver displayed [Dharma][ODBC Dharma Driver 09.01.0000][Dharma/SDK] -1016 Can't Connect to the Appx Data Server. Select OK, choose Machine Data Source → AppxODBC, and enter the APPX password again in the Dharma login dialog. The worksheet then loads the seven records. Reopening the saved workbook and selecting Refresh prompts directly for the password and completes normally.

Excel asks for the APPX login when refreshing the saved workbook.
Figure 10. Excel asks for the APPX login when refreshing the saved workbook.

Leave Service empty, as pictured. This native refresh passed with the supplied 5.5.1 driver; all seven records and nine fields match the sample. Blank selects the default sqlnw lookup, reaching this gateway on TCP 1990. Database appx selects the SQL dictionary. A saved workbook can retain Service=sqlnw even after the DSN is cleared: clear that field in its login dialog, finish the refresh, and save the workbook. Service and saved-connection overrides.

Create the connection with this edition's installed driver. A workbook copied from a newer-driver configuration can first report Invalid connection string attribute; create a fresh query through the wizard instead of assuming those saved attributes are compatible.