Help Centre

   
 
 

Using Attaché Server ODBC data sources in applications

Most applications which can access data via ODBC have some form of query builder installed. For example, Microsoft Excel allows you to invoke MS Query to attach to your data while Microsoft Access and Attaché ReportDesigner each have their own custom query builders. Generally, the query is saved automatically when you save the spreadsheet, report or database you have been working on.

The query builder will allow you to designate the appropriate DSN and select the required data (tables and fields). In most query builders you can also link related tables and perform data selection and sorting.

To access data in MS Excel, choose Data from Other Sources | From Microsoft Query and select your datasource (DSN) from the list of databases.

In Microsoft Access you can either link tables or create a pass-through query.

Attaché data structures

The Attaché database server stores all of your data in a series of tables contained in separate databases representing each of your Attaché company datasets. For example, if your Attaché data is organised so that you have four companies: Accounts, Payroll, Last Year Accounts and Last Year Payroll then the database server will contain four separate databases: ACCOUNTS; PAYROLL; LAST_YEAR_ACCOUNTS; LAST_YEAR_PAYROLL.

The tables inside the database exactly reflect the data files that previously resided in your company data folders. For each table there is one or more related indexes which make it easier for Attaché to retrieve data for enquiry and reports.

Within the database, the tables and fields (columns) are given names which are designed to assist programmers in quickly identifying what data the table contains and what the fields are used for. These table and field names do not necessarily reflect the labels on the tasks and screens seen by the user.

To make it easier for users to access data through ODBC, we have created VIEWS which more exactly reflect the user's view of the data. This means that someone viewing customer masterfile data, for example, could select a view called customer_master and see fields such as code, name, contact, street, suburb and postcode. These VIEWS also allow the data in the table to be formatted before it is presented to the user – for example, a decimal field might only display two decimals or a text field containing Y or N might be presented as a Boolean True/False.

As well as VIEWS, you can directly access the native TABLES. These are presented exactly as they appear in the database and have not been masked or formatted in any way. When looking at your data using ODBC, tables will be prefixed with tbl_.

One final set of data you can now access is the OPTIONS data previously stored in the SYS file and unavailable via ODBC. For example you can now select a view called systemcustoptions1 and systemcustoptions2 which contain the Customer Options including ageing headings, terms headings, default GL set, default bank account and so on.


REF: A001296.00 271118 18.1  ODBC1042