MS ignores my cell definition 2

[grndpa]

Is there a way I can get Microsoft Access 2003 to respect the cell definitions I provide?

The symptoms -- as bizarre and indictable as it would seem -- suggest that Microsoft has decided I mistakenly defined the cells as text when they are clearly numeric.

Specifically, I'm importing a file that contains a column entitled HomePhoneNumber. In the source spreadsheet and in the target Access 2003 table, I've defined the cells in this column as "Text". During the import (and Link for that matter) Microsoft rejects any cell in that column that isn't numeric. (I know these are the cells causing the symptoms as Microsoft provides the column name and row number in the "import errors" table).

Granted, most of the cells in this column are 10 digits or null. However, and I have no control over this, a half-dozen or so of the cells are formatted with parentheses and dashes such as (111) 111-1111.

Access rejects these formatted cells.

Is there any way, short of sorting the file or inserting dummy rows, that I can get Microsoft to respect my definition of the cells in this row?

 

[JoeAtWork]

By "grown-up" I did not mean how long the product has been around, I was referring to power and robustness, and how well the product can implement a relational database system. SQL Server can support an enterprise level system, Access can't.

I didn't mean to compare the two systems, as they are in completely different classes and meant for different markets. In short, Access is targetted for the layperson who knows little or nothing about relational database design, and needs to have an all-in-one tool so that UI and reports can be created within the product. It fits this role well, in that at least it is a major step up from "databases" via spreadsheets.

SQL Server is geared towards professional developers, to be used as the database engine component of a larger system.

I was mostly commenting on the OP's complaints of the "shortcomings" of the wizard tool he chose to use. If you choose to use a tool designed for a non-professional, you should expect it will predetermine most of your choices, as giving too many options just confuses the layperson. If you want more explicit control, use a more professional tool - I believe that was Michael Red's point about looking at options, which the OP seems not to have uderstood.

 

[KornGeek]

The way I've dealt with this in the past is to use VBA to open the target spreadsheet, and in every column that is supposed to be text that contain only numeric data, I prepend an apostrophe. This forces Excel (and thus the import tool) to view the entry as text. Because the first entry is text, it assumes the rest as text as well.

Now, my spreadsheets seldom had empty fields and I wasn't importing dates, so the problem is a bit different. Unfortunately, I don't have access (no pun intended) to the code anymore, otherwise I would post it.

I agree with lameid that if it's going to assume a format, using the target table is the best option. However, in Microsoft's defense, sometimes imports are done to create tables, making this an inconsistent standard.

 

[lameid]

In short, Access is targetted for the layperson who knows little or nothing about relational database design, and needs to have an all-in-one tool so that UI and reports can be created within the product.

All the office Apps have wizards and things that make things easier for the layperson (so long as they work and they make life easier for programmers too). And to some extent I'm sure some of the built in database templates fit some users needs. However I do not think that Access is limited in scope to the layperson. It and the other Office Applications for the most part expose VBA and provide for add-ins so programmers can leverage the full power of the product and make tools to make life easier. I'd say Office is in a middle tier between layperson and professional programmer. It is accessible to both and usable to both. Professional programmers can get more out of it than the layperson. I'd even go as far to say that Access is shifted more towards the professional programmer than the other Office Applications. It is not so much for the layperson... It is more for the Power users and up. A lay person can use an application made in Access.

WAD (working as designed) and "doing what the user wants it to do" can be 2 completely different things. The key commonality to Office and other programming tools is flexibility. The users of both expect to be able to do things within reason (I don't intend to drive a screw with a hammer). Another way to look at it is none of these Apps are designed to enforce business rules. Therefore I submit that all should offer flexibility like specifying datatypes in an Excel import. I guess I am trying to say that the users of these tools are never intended to be wrong. On the otherhand an Accounting system has rules that you must live by. An Enterpise system has business rules that the user will be prevented from modifying. Since office security is at best weak, Enterprise really means security to me. There is also a matter of scale that is at issue but not the baser one.

The OP wanted to know how to use Access to automate some task. At first, he only received recomendations on how to manually intercede even though in the first post he said that is not what he was looking for...

Is there any way, short of sorting the file or inserting dummy rows, that I can get Microsoft to respect my definition of the cells in this row?

Honestly, if I received the comments about adding rows without at least the suggestion of Automating Excel I'd be more frustrated. This is especially true if I had been around the forum long enough to know that the contributors to the thread are very knowledgable and helpful (which I do).

I think the issue some of the contributors missed is some one was asking about a tool in a toolbox instead it lead to this discussion about toolboxes. And though there are many reasons to abandon Access (Security, Security, Security, limited multi-user support, scalability), importing data should not be one of them.

 

[grndpa]

Thank you, lameid, for re-iterating my question about looking for alternatives. You have great empathy.

If nothing else, I've learned not to editorialize. Still the comments are quite interesting and -- despite the blood on the floor -- I've found them quite worthwhile.

I agree with JoeAtWork that

If you want more explicit control, use a more professional tool -

. Alas, no such option at this site.

 

[PWise]

Skip:
I gave you a star for this link

http://support.microsoft.com/kb/281517

Which helped me find a solution for my problem in post

http://www.tek-tips.com/viewthread.cfm?qid=1380843&page=1

PWise

 

[SkipVought]

Thanks, pwise! Just something I stumbled across.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}