Tutorial: Using Microsoft Access to link to API views

Microsoft Access is a favorite tool for developers, IT administrators and other system users to run Ad Hoc queries against databases. Normally this type of activity is potentially dangerous, but with the xTuple embedded database API it is safe and easy. All that is needed is an ODBC connection and Microsoft Access itself. Though any version of Access will work, this example uses Access 2007. It also uses the 8.02.04 version of the PostgreSQL ODBC driver. The ODBC driver usually installs automatically with PostgreSQL for Windows, but MSI packages for only the ODBC drivers are also available here.

Note: If your database user requires enhanced authentication, you will not be able to access data using ODBC connections.

Launch Microsoft Access and select the option to create a "New Blank Database." Give the database the name "xTupleLink." Click on the "External Data" menu and select "More" on the Import tool bar section.

APImsa1

Select the "More" option form External Data Import circled in the screen shot

Select ODBC database

APImsa2

ODBC Source Selection dialog

When prompted by the ODBC Source Selection dialog, select the "Link to the data source by creating linked table."

APImsa3

Select Data Source Dialog

Select the Machine Data Source tab on the Select Data Source Dialog and click the "New" button

Note: If you are in Vista, you may be prompted that you do not have administrative privileges to create a system DSN. This message may be ignored.

Select User Defined data source. Click Next.

APImsa4

Create New Data Source Dialog

Select the "PostgreSQL ANSI" ODBC driver. Click Next. Click Finish.

APImsa5

Driver Settings Dialog

Enter your database settings in the Setup dialog. The screen shot is only an example, your setting will be specific to your environment. If you don't know what they are, contact your administrator. Click the "Test" button to make sure your settings work correct. Click "Save."

APImsa6

Select Data Source Dialog

You should be taken back to the Select Data Source dialog. Click "OK."

APImsa7

Link Tables Dialog

Here we are prompted to select tables to link to. Note all the API views show up at the top as tables. Select api.customer and api.custshipto.

APImsa8

You will be prompted to select a unique record identifier. This is necessary for Access to know how to send the update query if you change data. Select customer_number for api.customer, and customer_number and shipto_number for api.custshipto.

APImsa9

Access database linked to Customer records

Congratulations, you're done! You may now select the api_customer table and edit records directly. Access will commit any changes you make as soon as you move off the record on to another or a new one. Experiment by making changes and new records in Access, then see how the result appears in your xTuple client. Also experiment with Access tools such as queries, forms and reports.

Access is a great way to handle complex ad-hoc migration queries and reports where either the time or expertise is limited. Note you may also add your access application to a Custom menu in the xTuple Application client. See your xTuple Application client help files for more information.