Rene Castro
This tutorial will show you how to document an SQL Server database with Microsoft Access.
Microsoft Access is included in the professional and enterprise editions of Microsoft Office. This varies slightly depending on the Microsoft Office suite version.
You can store data in your local file system and you can also link tables and views from other sources using an Open Data Base Connector (ODBC) with Microsoft Access.
In this tutorial we will create an Access file (database) and link it to tables in a remote SQL Server database to show how to document them using the Microsoft Access Database Documenter. Finally, we will create a PDF file that contains the documentation.
Create an empty Access file (database)
To start using Microsoft Access, you need to create an empty file (database) where you will save all your work.
To create an empty file, from the File tab click on the New option and select the Blank Database template.
In the File Name box, provide a name for the empty file (database) and then click the Create button.
You can also change the location for the new file (database) by using the Browse file.
Microsoft Access creates the database with an empty table named Table1, and then opens it in the Datasheet view with the cursor located in the Click to Add column.
Link tables
As indicated in the introduction, the purpose of this article is to document a MS SQL Server database, so we need to create a connection that allows us to access the data.
This connection can be achieved using an ODBC driver that will serve as a bridge between Microsoft Access and a Microsoft SQL Server database.
ODBC is an interface standard for accessing data and communicating with database systems, regardless of operating systems, database systems or programming languages.
You can create new ODBC data source or use an existing one.
If you don't have ODBC connection in your system already follow this guide.
If you have ODBC connection defined in your system you can now connect to SQL Server database from MS Access - go to External Data ribbon tab, then click on New Data Source and choose From Database > From SQL Server.
There are two options in this feature and for our case we will choose the Link to the data source by creating a linked table option that links SQL Server to Access in a way that you can browse SQL Server objects, query and modify data. Select option and confirm with OK button.
A list of available ODBC data sources is displayed and you need to select connection to your database.
Depending on the authentication method defined in the connection information of the existing ODBC driver, it may be necessary to provide credentials, like this:
After this, Microsoft Access displays a list of tables from which you can select the ones you want to work with.
For each table that doesn't have primary key in the source database, Access asks you to define it in its file, showing one table at time. You can skip this step with Cancel.
Now you can see the linked objects (from SQL Server) in the left side of the main screen.
The Database Documenter
Microsoft Access has a tool called Database Documenter that allows you to create a report with information for each selected database object.
To generate report go to the Database Tools tab and choose the Database Documenter option.
After selecting this option, the Documenter window is displayed and you can select the objects you want to include in the report.
You are good to generate documentation but you can change default display options first. You can define what information is included in the report:
Once done, click the OK to generate the report.
Documenter generates report and displays it in a container called Object Definition where you can preview it, print it, or save it to PDF.
Alternative: Dataedo
You can also export documentation with Dataedo - it offers easier process and nicer output:
Some other features:
- Convenient Data Dictionary editor (table and column descriptions)
- ER diagram editor
- Add rich text narratives
to name a few.