What is the problem?

Since Office 97, might have been earlier, 97 was when I started to use these features. It has been very easy to get Excel data into MS Access. In Access there is a tool that does everything for you… You have a list of items in Excel with some properties defined:

image

They all have an item number and a name, some have colour and size some are missing these properties. In the price column are the prices where you use special coding where NP = No price, and POR = price on request.

This kind of table is very easy to import into MS Access as a imagetable. First create a new database, use 2003 format and mdb. If you don’t must use the 2007-format, and in all cases you don’t need it – my advice is skip it. MS has put in some annoying restrictions, so avoid “.accdb”.

imageSelect “External Data” and the Excel option and click the “Browse”-button to show where your Excel sheet is located.

You are now running a wizard that guides you thru the process of importing your spread sheet into a database table. You will get one of these silly warnings “A potential security..” , but click “Open”.

imageBrave you. You get up a popup window telling you, and you click “Next”… and then finally “Finish”on:

image

You open the table in MS Access, and you get what you asked for but not what you expected.

image

imageYou have to comply with the rules, it imported everything that was there. After that lesson learned you get rid of the first two lines and the empty A-column:

 

imageYou redo all the “Next”-clicks and it is almost all there in a relational table. There is a table called “Sheet1_ImportErrors” that tells you another story…image

The wizard did not like some of your data so it neglected it. This is how it works in reality, nice wizard gives you 95% of what you want. How to solve it completely, you don’t have a clue. The true story behind it is that a table in a spread sheet is not always easy to convert to a relational table. The relational table have rules where the Excel tables have none. The wizard tries to overcome these complexities which often ends up – you are left with some problems you don’t know how to handle. In this case it was the price column, which the wizard on good reason’s believed contained numbers, but… So when it found NP and POR, it gave up and neglected these values. Somebody from “the relational world” could say, you can’t do like that, numbers are numbers and text is text…
As an Excel user you don’t understand nothing.

Reading more about the import tool in Access.