Error importing from Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2003 and trying to import a table from Excel and keep
getting an error message but am unsure what the error relates to. I have
tried to change various aspect (data types etc) but still get the error.
In my Access database I have some fields which are checkbox options - the
table in Access translates these as -1 for yes and nothing for No. I assume
that the data in Excel should also consist of -1 or nothing, or should it be
TRUE and FALSE? Also should the data type for these fields be Yes/No or Text?
The other question I have is - should the excel table include the primary
key or will access add this automatically? I tried both ways but have so far
achieved nothing!

Thanks for any help
 
The big question is: What's the error message?

0 means No in Access. Nothing means Null.

You should store this data in Yes/No fields.

You might have better luck first linking to the Excel worksheet then
appending the records from it into a table. That way you could use an IIf or
NZ statement to convert the Nulls to 0.
 
Try this:

With Access open go to File, Get External Data, Link Tables. At the first
dialog box select Microsoft Excel XLS at the Files of Type dropdown. Then
find your Excel file and click the Link button. Follow the instructions in
the Link Spreadsheet Wizard. When done you can use the data in the
spreadsheet like a table (except that you can't change it). It will show the
data like Access sees it.
 
Great - Thanks very much! I linked the sheet first and then copied the
results from the linked table into my original table. This seems to have
worked.
Anna
 
Jerry
Thanks for your response.

The error message that i get is "An error occurred trying to import file.
The file was not imported".

If I've understood you correctly I should be using Yes/No Data type and 0s
and-1s in the excel table. I've given this another go but am still getting
the same error.

As a relative newcomer to Access I'm afraid I have no idea about appending
records or what IIf and NZ means. Could you give further info about that if
its something I need to do?
Thanks
 
Back
Top