The Search key was not found in any record excel import

[Jen0dorf]

HI

As I have done 100s of times before I went to import an excell spreadsheet into Access 2013

I sue the wizard and tell it the 1st raoew contains field names. No spaces in the first row. I've set all the fields to text.



When I import I get the error:

The Search key was not found in any record

I've compacted and repaired the database but that has not fixed the error. Is it in the spreadsheet of in Access?

thanks

Ian

[ranman256]

are you importing via Transferspreadsheet, or a query?
does it create an IMPORT ERRORS table?

setting the column to text does not guarantee the field will be text.
I run a macro to scan the column and put a single quote in the front of every cell before import. This solves that problem.

[Jen0dorf]

Hi

I just select
external data..... excel

then browse to the file.

I select import into a new table, tick the box to say the first row has the column headers,

I let access pick the primary key.

It's then that the error appears and nothing is imported and not error report is created.

THanks

Ian

[Jen0dorf]

Hi

just re read your text and realised I hadn't quite understood. Went back to excell added a " to the start of every record and it them imported with no errors.

I then used search and find to remove the " in access.

Thanks

Ian

Hi

I just select
external data..... excel

then browse to the file.

I select import into a new table, tick the box to say the first row has the column headers,

I let access pick the primary key.

It's then that the error appears and nothing is imported and not error report is created.

THanks

Ian

[ranman256]

in case you dont have it, here's a macro to convert the column to text.
put cursor in top row, then run:

Public Sub Cvt2Txt()
While ActiveCell.Value <> ""
   If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   ActiveCell.Offset(1, 0).Select
Wend
End Sub

[Jen0dorf]

Hi

don't have that,

is this for using in excel or access?

thanks

Ian