How to Set Up ODBC Access for the Lexmark Markvision Enterprise Database

This document provides step-by-step instructions on how to use Open Database Connectivity (ODBC) to extract data from the built-in Firebird database that is included with Lexmark Markvision Enterprise (MVE).

Procedure:

Please note that the nature of ODBC connections require a local connection. Therefore, you must either perform all the following procedures on the Markvision server (requiring you to install the application software, such as Microsoft Excel or Access, on the server) or you must install MVE on a workstation and copy the database file from the server in order for it to work.

NOTE:

Prior to running any queries, you should perform an audit in Markvision to ensure that the database contains up-to-date information.

• Obtain and Install software and documentation.

• Create ODBC data source.

• Open ODBC data source using Microsoft Excel.

• Open ODBC data source using Microsoft Access.

Obtain and Install Software and Documentation:

1. Obtain the free Firebird ODBC driver, available from the

   Firebird web site

   . Make sure you obtain the proper version for your operating system (32-bit or 64-bit).



2. Install the Firebird ODBC driver on the computer where you intend to access the Markvision Enterprise data.



3. Download the Markvision Enterprise Database Access white paper which is available from

   Lexmark’s Markvision web site

   . Scroll down to the bottom of the page then select Database White Paper.

Review the white paper to determine:

• The table which contain the data you want

• The field which contain the data you want

<Back to top>

Create ODBC Data Source:

1. Open the ODBC Data Sources tool. In most versions of Windows OS, go to: Control Panel >Administrative Tools >Data Sources (ODBC).



2. Under ODBC Data Source Administrator, click User DSN tab.

3. Click on Add.



4. Click the Create New Data Source window and select the Firebird/InterBase(r) driver.

5. Click on Finish.



6. Fill the FireBird ODBC Setup dialog box with the following information:

   

   1. Data Source Name (DSN): Enter a descriptive name that you will recognize when it appears as a selection in applications, such as

      MVE Data

      .

   

   2. Description: Enter a longer description of the database if you wish, such as Lexmark Markvision Enterprise database.

   

   3. Database: Browse to

      C:\Program Files\Lexmark\Markvision Enterprise\firebird\data

      and select the FRAMEWORK.FDB file.

   

   4. Client: Browse to

      C:\Program Files\Lexmark\Markvision Enterprise\firebird\bin

      and select the FBCLIENT.DLL file.

   

   5. Database Account:

      FRAMEWORK

   

   6. Password:

      rivet

7. Leave all other settings unchanged and click OK twice.

<Back to top>

---

Open ODBC data source using Microsoft Excel:

NOTE:

The following instructions are based upon Microsoft Excel 2007.

1. Open Microsoft Excel then click on the Data ribbon.



2. Click From Other Sources.



3. Select From Microsoft Query.



4. In the Choose Data Source dialog box, select the data source you created from Step 3.

5. Click OK.



6. Under the Query Wizard, locate the tables and select the fields you want to include in your query output. Choose a field and click on the > button to add it to the query results.

For example:

• NETWORK_PRINTER table; Select MODEL_NAME, SERIAL_NUMBER, and LIFETIME_PAGE_COUNT.

• NETWORK_ADAPTER table; Select the IP_ADDRESS field.

7. Click Next.



8. Enter filters to obtain more specific results, and then click Next.



9. Specify how the data should be sorted, then click Next again.



10. Click Finish, then OK.

11. Save the file so that data can modified, formulas can be added, and so on.

<Back to top>

---

Open ODBC data source using Microsoft Access:

NOTE:

The following instructions are based upon Microsoft Access 2007.

1. Open Microsoft Access and create a new blank database.



2. Click the External Data ribbon.



3. Click the More button and select ODBC Database.



4. The best option is to Link to the data source by creating a linked table as this means the data will continue to be dynamic; when you perform a discovery or an audit in Markvision, the changes to the database will be reflected in Access.



5. Click OK.



6. In the Select Data Source dialog box, click on the Machine Data Source tab.



7. Select the ODBC connection you created earlier and click on OK.

8. Select the tables you wish to include in the Access database (refer to the database white paper to determine which tables contain the data you need). Use Ctrl+click to select more than one table. Click on OK.



9. Click on the Create ribbon.



10. Click on the Query Design button.



11. In the Show Table dialog box, select the tables you wish to query and click on Add.

12. Click on Close.



13. Drag the fields you wish to include from each table into successive columns below.

14. Or optionally, a criteria can be added in the appropriate column to filter data.



15. Click the Run button to run the query and see the resulting data.



16. Click on View and select Design View to return to the design window and modify the query. Click Run again to see the results.



17. Once you are satisfied with the results, click the Save button in the office toolbar. When prompted, give your query a descriptive name.

18. The access has report tools available on the Create ribbon if you wish to create reports from your data.

<Back to top>

---

Still Need Help?

Have the following available when calling Lexmark Technical Support;

• Printer model(s)

• Printer serial number

• Name and version of the solution

LEGACY ID: HO4079