Spludlow Web Header

Microsoft Access - Basics

Obtaining Access’s “Northwind 2007” Sample Database

Access’s sample database is “Northwind” you can obtain it by doing the following:

·         Start up access from the shortcut (No current database)

·         Within Access search Online Templates for “Northwind”

·         You should get a hit of “Northwind 2007 sample”, single click on it.

·         Choose the filename for this database.

·         Click the “Create” button.

·         Do what it says and click the “Enable Content” button

·         Login as whoever you think has the silliest name.

·         Open the navigation pane on the left with the “>>” button.

·         By default, the navigation pane is now showing the “Northwind Traders” custom category view. Click the downward arrow round button in the pane’s header and select “Object Type”.

·         Expand “Tables”

·         Bingo you are looking at the tables. You can double click to see the data or right click and select “Design View” to see the table definition.

You can view the relationship diagram by clicking “Database Tools” ->” Relationships”. There is no auto tidy function I’m afraid, but the layout will persist if you save it.

Northwind 2007 Portability

The Access Northwind database has a few troubles when it comes to exporting the schema and data. It uses space in table and column names, no big deal.

There are “Attachments” columns in many tables that use the Access type “Attachment” that maps to OLE Type “LongVarWChar” that becomes a DataTypeId of “NText”. They have Unique indexes that are not legal in most databases. So these “U” keys need bodging out of the Schema.

The “Supplier IDs” on the “Products” table seems to be defined in Access as a Long Integer but from OLDDB it seems to be a “LongVarWChar”. Looking at the data it has semi colon delimited integers as text so we will treat it as text. It also has a unique key so it needs removing.

There is also a “Orders_VersionHistory” unique index on a column that doesn’t seem to exist (it must be hidden) this need removing.

All these fixes can be made with the following code:



Spludlow Web Footer