Welcome to Luca!globe
 The CPA Journal Online Current Issue!    Navigation Tips!
Main Menu
CPA Journal
Professional Libary
Professional Forums
Member Services
Dec 1994

Microsoft gives Access to corporate data. (database application development software) (The CPA & The Computer)

by Rowe, Walker Elliott, III

    Abstract- Microsoft Access is a database application development software that allows users to easily gain access data in complicated corporate databases. Because it features open database connectivity (ODBC), Microsoft Access can communicate with databases offering an ODBC driver, including ORACLE and SyBase. Among the benefits of this software is that it provides users with the ability to create their own repository of informatiion without even bothering the MIS department or programmers. Knowledge about databases is absolutely not necessary when using Microsoft Access. All that is required of its users is to know which database tables have the data being sought and the names of the fields that serve as the storage of the needed information. People need not worry about tightly-controlled data because the software comply with security procedures just as other programs do.

Such an enthusiastic and simple outlook often runs into the stoic countenance of been-there, done-that indifference. Do not, however, be daunted by the skeptics.

Paris has L'Academie Francaise to stamp out Anglicisms (le fast food) and impose order upon the standard French language. America has Microsoft to insist that disparate computer systems speaking thousands of computer dialects use the same language when speaking to one another. With the many databases on the market, one common vernacular is ODBC, open database connectivity.

Programs that are ODBC compliant--Microsoft Access 1.1, Microsoft Excel 5.0, WordPerfect 6.0, and Lotus 1-2-3 v.4.0--can talk with any database that offers an ODBC driver--ORACLE, SyBase, and others. (DEC offers a driver for RDB. Q&E writes drivers for Ingres, Informix and DB2.)

Why should you be excited by this? For one thing, this software lets you build your own repository of information without having to ask any programmer or the MIS department to do it for you. You can run queries and print reports about topics that personally interest you: your general ledger account balances, your inventory, your sales, etc.

If your computer is connected to a computer network, chances are your corporate database is connected to the same or an adjacent system. This means all you need to connect your computer to a far away database is the name of the database computer, a password, and a short command (such as an attach statement). This needs to be set up only once. After that, accessing the data is a matter of clicking the mouse and keying a password.

No Database Knowledge Required

You do not need to know anything about databases to use Microsoft Access. All you need to know are which database tables contain the data you seek and the names of the fields (columns, variables) that store the information you need.

Access presents data in a familiar spreadsheet-like format shown in rows and columns. From this display, you can use something called "Wizards" to create reports, queries, and screens. It takes Access as little as 10 seconds to program a report, run it, and send the results to the printer. If you have an idea but are not sure what to do, instructions called "Cue Cards" will lend a hand.

The forms (screens) and reports that Access produces are breathtakingly elegant. They include varying type sizes, and fonts neatly decorated with shaded patterns and borders. For colors, Access includes a color palette and Microsoft graph.

For example, suppose you want to see all the journal entries that have passed through the cash account on the balance sheet. Use the mouse to select the account number, debit amount, and credit amount fields (columns). Then key the cash account number and click the Run Query button. Access shows the results posthaste (sometimes). You can save the query or print it on a report.

Since it is so precarious, you would not want to make changes to journal entry data. For customer names or other data, you could use "Wizards" to make a data-entry screen (a form) and make changes at will. (Of course, you can prevent this by making the connection to the database read- only.)

As you gain expertise you can experiment with features such as connecting tables through common elements. For example, you might want to associate inventory transactions with a particular salesperson. To do this without Access you would have to know what is meant by a view or, the more complex, SQL language. With Access you simply open both tables and draw a line between the common element salesperson number, click the mouse to select the column "sales person name," and key "Tom" or "Ross" or whatever.

Some people might worry that Access will allow unauthorized access to tightly-controlled data. This is not true, because Access must follow security procedures just like any other user. Others might contend that Access is not robust enough for so-called industrial-strength systems (a word that implies a certain level of haughtiness). When a report or query is run, the host computer, a mainframe or database server, does most of the calculating and sorting. Access simply requests such data and waits patiently to receive it.

Still others might say that Access will clog the computer network with requests for mountains of data in far-away places. Wherever possible, Access uses a clever device called a "dynaset" to retrieve data not in its expansive entirety but by its minimal location (a so-called pointer). Access also uses something called scrollable cursors to retrieve data one screen-load at a time rather than the whole set at once.

If your forte is system integration, you can use Access to test systems as you build them. Access is an ideal tool for keying test data. Without it, or a similar tool, the only way to make test data is to use a text editor to key something strange like "GL00002320000324XX032493" to represent, say, a debit of $23.20 to account number 324XX. This is prone to errors and unwieldy, not to mention that doing this all day long would put your staff in a sour mood. With Access you can make an easy- to-use data-entry screen and then make an export filter to format a test file.

Easy Data Conversion

Access can convert dates to text, numbers to text, text to numbers, and so forth. For more exacting requirements--such as right-justified and zero-filled amounts--you might need to write a program using the Access Basic programming language. This is easier to do than you might think. Access lets you fumble your way through writing code, pointing out spelling errors and correcting capitalization as you go. Debugging is easy, because you can stop a running program, add a new line of instructions, then let the program carry on. Try that with COBOL.

Beyond accessing large network databases, you can use Access to connect to PC databases. Is there a computer hacker in your department who is handy with DBase, Excel, Paradox or FoxPro? No problem. Microsoft supplies ODBC drivers so you can connect to this data as well. You can work with, for example, dBase III data using something with which you are familiar, Access, and without owning your own copy of dBase III.

If you work with flat files--ordinary text files or files received by tape or modem--you can use the Microsoft ODBC drivers to do queries and reports. This is done by segmenting long strings of text into separate bits of information (fields). Access makes tills easy. Simply run the cursor across the text and press the space bar where you want a new field. Then you can work with the text file like it was a database.

Of course there is a down side to this analysis, but it is slight. Since Access is tightly integrated with your computer network, any changes in those networks can wreak havoc with data sources (meaning ODBC definitions) you have made. Also, Access forces you to optimally tune your Windows software for the proper buffer size et al. This is not for the faint of heart. Additionally, Access like any other software that relies on the unreliable MS-DOS operating system, can lock up your PC when you try to do too many things at once.

It used to be that getting information from a company's database required a knowledge of SQL language or programming expertise. This is no longer true, as software like Microsoft Access is increasingly able to connect to database products like ORACLE, SyBase, RD, and others. If your organization keeps its data in such a place--and it probably does-- you might use Access to look into it.

The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.

©2009 The New York State Society of CPAs. Legal Notices

Visit the new cpajournal.com.