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

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question