Hello,
I am having trouble importing data from an Excel Worksheet. When I try
to import the data, everything works except certain columns that
contain dates, where it replaces the date with a null in the access
table. To import the data I do the following.
1. Create a table based on the excel spreadsheet setup with the
following query
sSQL = "INSERT INTO " & TblName & " SELECT * " _
& "FROM [Excel 8.0;HDR=Yes;Database="&ExcelFile&"].["&ExcelRange&"] "
& "WHERE False;"
2. I then correct any field data types.
3. Finally I use the same query above except without "WHERE False;"
I believe the problem arises because if a record has no date, then the
string " - - " is entered instead of just leaving the cell blank.
After doing some searching it appears that when access imports data, it
internally determines the data type from the first couple of records.
For the problem fields, the first records are " - - " instead of a
date in the form XX/XX/XXXX. This seems confirmed by the fact that the
first query makes those fields of type 'text' instead of 'date/time'
(which is why I have to correct those fields in part 2). Leaving those
fields as text in the Access table still replaces the dates with nulls,
but now leaves the " - - " records.
I found a solution by adding a " ' " in front of all of the records in
offending columns in excel, but the process is unbelievably slow since
I have ~70 columns with ~50,000 rows.
Is there a way to force Access to internally declare the problem
columns as 'date/time' type so that it will reject the " - - "
records instead of the date's?
