Import from Excel to Access, data types don't match

[giddyup43]

Hi.
I created a beautiful form in Access in order to fix up our inventory database. Our database is currently in excel. I would like to go through each record, one by one in the access form.
My problem is that I cannot import the data from excel into access.
The data types don't match.
Well, they don't match! the excel sheet is messed up. I want to fix it row by row in access but I cant get it in.
Can somebody help me with this?
My access form is based off a table set up with lookup columns, text columns, date columns, numbers columns, yes/no columns. I want to import excel information into this table, then work on it.
If you can help me with this i would SOO much appreciate it.

 

[Joe4]

Unfortunately, Excel and Access don't always play nice together, especially as it relates to importing data from Excel into Access. Access tries to "guess" the data-type of each field (I believe it looks at the first 10 records), and sometimes it guesses wrong, and it usually doesn't give you too much of a clue as to what the problem is. Sometimes you can figure it, like if you add an alphanumeric ID field, and the first 10 records are all numbers, but letters are introduced somewhere further down the page, it tries to use a Number format which will cause as error when it gets to the data with letters.

In the past, I have spent hours trying to figure out exactly what the issue with an import is to no avail. A workaround I usually end up doing is to export my Excel data to a tab-delimited file, and then import that file into Access. When you import a text file into Access, the Import Wizard is invoked allowing you to set the Data Type for each field (instead of letting Access "guess"). An extra step, yes, but only takes a minute longer as opposed to hours of pulling your hair out.

Our database is currently in excel.

By the way, have you considered moving your data to Access permanently? After all, Access is a database program while Excel really is not. If all the data resides in Access, then you don't have these Excel/Access interaction issues to deal with.

 

[alansidman]

In addition to Joe's suggestion, I often find that if I convert my excel file to a .csv file and then create an import specification (which allows you to specify exactly the type of fields you have) will solve this issue.

Here is a tutorial that should guide you through this procedure.

How to Create an Import Specification | BTAB Development