Importing Data from Excel into Acess

[Vandana Rola]

I am trying to import data from excel into already existing table in
access (I don't want to import data in to new table). But everytime I
get error Microsoft was enable to append data to the table. I have the
same number of fields, same name of the fields, same data type of the
fields in both Access and Excel. All the columns and rows are filled
in excel. The table is empty in access. The only difference is field
size. The field size in excel is larger than in access. Although the
value assigned to these fields is smaller than the assigned value.

The field size is default in excel and I don't know how to change
that. I can change the field size in Access, but for that I have to
delete the relationships between the tables and recreate them. Which
seems to be long procedure. If that's the only reason then I will do
that. I really need help.

Also I change the data type of the columns in Excel to text, number,
date etc.from general.

 

[John Nurick]

Hi Vandana,

The first thing to remember is that Excel columns don't have data types.
You can format the cells, but it's almost impossible to prevent data of
the wrong type (or other incorrect data) from creeping in if users are
allowed to edit the worksheet.

As a result (and not helped by some aspects of the design of the import
and linking routines) importing from Excel can be very frustrating.
Often the best thing to do is to import or link it to a temporary table,
allowing Access to choose the field types and sizes. Then use a query to
append the data to the "main" table. Handle any necessary type
conversions by using calculated fields in the query (e.g.
CLng([FieldName]) to convert a Double field from Excel into an ordinary
Long field in Access.

Another thing that often causes problems is Excel columns that contain a
mixture of text and numeric data. One way to handle these is to ensure
that all the numbers are preceded by apostrophes, which are not
displayed or imported but which force Excel and Access to treat them as
text. These little Excel routines can add and remove these as required:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub