Tech Support Guy banner

Solved: Problem with Importing Excel Records into Access

5049 Views 8 Replies 3 Participants Last post by  ddockstader
I know:
A) I am probably doing this wrong. I am certainly doing SOMETHING wrong.
B) There is undoubtedly a faster/easier/better/more reliable (choose one or more) way

So I decided to ask the experts. I have an Access database of woodworking magazines that I am slowly populating. I thought it would be easier to just fill in the information in Excel (I'm pretty experienced with that) and then just import it into the Access table. That way I could kind of quality check the data before I put it in the database. Usually I just do one magazine at a time, save the Excel file and then import it into the Article table in the database (all are attached). Two things go wrong. First, when I import it, I always get an error message saying Access couldn't import the whole table and 0 records were not added. I don't understand that at all, because ALL the records always are added to the table. But, the other problem is that the first column in the Excel file, the magazine title (or at least an abbreviation thereof) is always blank in the Access table. I have to go back and populate it every time.

I have attached a zip file with a pared-down database and a populated Excel file. Can someone look at it and tell me how I can improve this process so I don't get error messages and do get all of the data I put in? Thanks. View attachment DB
Not open for further replies.
1 - 2 of 9 Posts
First of all it is usually much more efficient to enter data in to Access via a correctly formatted form than to use Excel and then Import it, even when you are more familiar with Excel. How do you do the import?
Do you use The File>Get External data
or use Visual Basic Code?
Last but not least, Your attachment does not appear to be an attachment, so we can't look at your data.
ddockstader, first of all the "0 records not added" actually means exactly what it says, no records were not added, when the count goes above zero it means that some records were not added.
The problem with your import is the Combo lookup characteristic of your Magazine Title field in the Access Table, if you remove it and make the box a straight forward "text" box you won't have this problem.
The Lookup field is not the best method to use a combo, I know it is easy and convenient to set up but it prevents a lot of other things from happening.
The best way to have a combo is in a form, the best way to input data directly in to Access is also in a form.
If you want me to create a suitable query/form combination for inputting your data straight in to the table rather than putting it in to Excel first and then Importing it just let me know.
See less See more
1 - 2 of 9 Posts
Not open for further replies.