In Excel, any cell can contain any data, and the data may or may not be
formatted in a particular way. In Access, by contrast, every field has a
fixed data type and can only contain a value of that type ( double,
text, etc.). When Access imports from Excel to a new table, it guesses
the field types to use by examining the data in the first few rows of
the Excel table - and often gets it wrong. This can cause Access to bomb
out during the import.

The "Fix" for this - according to another post, is to massage the data in
the import. That is riciculous.

Does anyone have a way to fix this problem the right way?

Alan

Alan B. Densky wrote:
Whenever I am importing from a source that does not have explicit DataTypes I
always first import into a table having all text fields. Then I can run an
append query from that table into the final one using the appropriate formulas
to convert the data. At least then you are in total control and can examine the
rows that have problems to see what is going on.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Rick,

All of the fields in my Access DB are already text fields.

My problem is that Access, in it's infinate wisdom, is assigning data types
to the columns in the linked spreadsheet based on the data in the first few
records. So, for instance, if I have a zip code column in the Excel spread
sheet, and the first several records don't have a hypnen and zip + 4, and
several rows later some of the zips have a hyphen 33063-1234, the import
bombs because Access has assigned the data type of Number to the link on
that column. And what you see in the link is #num.

In addition, if the zip code has a leading zero (01234), because it's a
number column, it strips out the leading zero. I have similar problems on
several other fields.

So I need some way to tell Access to force all of the columns in the link to
be Text data type.

Alan


"Rick Brandt" <rickbrandt2 (AT) hotmail (DOT) com> wrote

Alan B. Densky wrote:
I never link to Excel. Only import. For exactly this reason.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Alan,

See
http://www.dicks-blog.com/archives/2...ed-data-types/
and http://support.microsoft.com/?id=257819
and
http://msdn.microsoft.com/library/de...ess_driver.asp
and http://office.microsoft.com/en-us/ac...950951033.aspx


Also, consider upgrading to Access 2007, which offers more direct
control.


On Thu, 8 Mar 2007 20:27:31 -0500, "Alan B. Densky"
<AlanD (AT) neuro-vision (DOT) us> wrote:

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Hi John,

Thanks for the effort. I had the link to www.disks-blog and went through
that information a while back. It didn't help because it just didn't make
any difference when I edited the registry.

As far a Acess 2007 - I have no interest in spending the money, upgrading,
or going through the learning curve.

My database is based on DAO, so the ADO isn't going to help me, and I don't
want to go through the learning curve. I quit programming for money a couple
of years ago, and now only program for my own business and a few old
customers. I don't have the time, energy, or desire to play the Microsoft
"learn to do it our way, and we'll make make your learning investment
obsolete whenever we feel like it" game any longer. Especially since so many
people are sending their work to Rent-A-Coder people for $5.00 a day.

Is there any other way around this issue? Maybe I'm just going to have to
break down and do an import of a CSV file in a temp table?? It sounds like
my only work-around that I don't have to go through a learning curve to
accomplish. Unless you know of some other way to trick Access to look at all
of my linked columns for this particular table as Text columns.

Thanks again,

Alan

"John Nurick" <j.mapSoN.nurick (AT) dial (DOT) pipex.com> wrote

Have you tried using IMEX=1 (which seems to work equally in DAO and
ADO)? E.g.

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

On Wed, 14 Mar 2007 21:07:24 -0400, "Alan B. Densky"
<AlanD (AT) neuro-vision (DOT) us> wrote:

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Hi John,

How would I go about using that? Currently I'm using TransferSpreadsheet to
create my link to my spreadsheet.

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblLeads",
YesDB, False

Alan

"John Nurick" <j.mapSoN.nurick (AT) dial (DOT) pipex.com> wrote

Just create a new query, switch to SQL View, and paste the SELECT...
statement into the window. Then modify it to suit your filename and
sheet name so it delivers the data you need, and save it. After that you
can use it pretty much as if it were a linked table.

On Thu, 15 Mar 2007 19:40:22 -0400, "Alan B. Densky"
<AlanD (AT) neuro-vision (DOT) us> wrote:

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Thanks John.
"John Nurick" <j.mapSoN.nurick (AT) dial (DOT) pipex.com> wrote