Link to XLS or Import as DBF

[Bob Barnes]

A snippet (see below) of an Excel file that I tried Access linking to
Excel...the Color "3R3" below links in Excel as #Num! (a dealbreaker).

If I save as a DBaseIV, any Color NOT readable as a number arrives as a Blank.

Also, saving as a DBF changes all the "GoPK" below to a Date..IE 9/10/09. I
can deal w/ that in the VBA code I use working w/ the DBF. I had hoped to
either bring in a GoPk (IE...9/10/09 6:49:09 AM) as a Date (w/time), or use
as a PK (as a text)..which would be unique combined w/ Booth as a multi-key
PK. Is there a known "negative" using a longer text (IE...9/10/09 6:49:09
AM) as part of a PK?

I could also deal w/ this data w/o using a PK.

The ISSUE now...getting all Color values in the DBF when they appear as a
number (IE..202), or a text (IE...3R3). TIA - Bob

GoPk Booth Acolor
9/10/09 6:49:09 AM C 202
9/10/09 6:53:33 AM D 202
9/10/09 7:55:44 AM D 3R3
9/10/09 8:12:30 AM C 202

 

[Jerry Whittle]

The trick is to put a bogus first row in the Excel spreadsheet with an
example of the correct data type. Access has a bad habit of just checking a
few rows and guessing the data type. For example it thinks that the Acolor
field is a Number because the first few records are numbers. Then it gets to
3R3 and throws an error. Make the first row 3R3 and it would see the field at
text.

You could do something similar with the time field.

Another option is to import the Excel data into an existing table with the
correct data types.