Excel To Access - Problem with last column

S

Stuart

I have a preformatted Access table that suits my worksheet data.
Using the TransferSpreadsheet method I copy all the worksheet's
data into a copy of that table (named as the worksheet.name).

The last column refuses to import, and I don't know why. The
last col has a text value in row 1 (the Access Field Name), whilst
the rest of the column has either empty cells or numeric values.
Other columns with similar data and same Access Field setups
import fine. There are no formulae in the sheet.

I've tried different sheets and workbooks (all with the same basic
structure/formats/data types) and get the same result every time.

Any ideas, please?

Regards.
 
O

onedaywhen

Stuart, You should consider using ADO and SQL to do this.

I'm not familiar with the MS Access's TransferSpreadsheet method but
if it works anything like the Excel ADO or MS Access manual
equivalents, the following may be relevant. Please post back if it is
so I'll know for next time:

The relevant registry keys (for Jet 4.0) are in:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

The ImportMixedTypes registry key is always read (whether it is
honored is discussed later). You can test this by changing the key to
ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the
error, 'Invalid setting in Excel key of the Engines section of the
Windows Registry.' The only valid values are:

ImportMixedTypes=Text
ImportMixedTypes=Majority Type

Data type is determined column by column. 'Majority Type' means a
certain number of rows (more on this later) in each column are scanned
and the data types are counted. Both a cell's value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
Rows from any minority data types found that can't be cast as the
majority data type will be returned with a null value.

For ImportMixedTypes=Text, the data type for the whole column will be
adWChar ('a null-terminated Unicode character string' i.e. Windows'
REG_SZ).

But how many rows are scanned for each column before is decided that
mixed types exist? There is a second registry Key, TypeGuessRows. This
can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is
the number of rows to scan. A value of zero means all rows will be
scanned.

OK this is where Excel and MS Access part company, because I'm using
ADO in Excel and I have an explicit connection string e.g.

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\ db.xls;
Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

A setting of IMEX=1 in the connection string's extended property
determines whether the ImportMixedTypes value is honored. IMEX refers
to IMport EXport mode. There are three possible values. IMEX=0 and
IMEX=2 result in ImportMixedTypes being ignored and the default value
of 'Majority Types' is used. IMEX=1 is the only way to ensure
ImportMixedTypes=Text is honored.

I was wondering how MS Access deals with the IMEX=1 issue in the
absence of an explicit connection string. I guess that because in the
MS Access GUI you will always be in import mode so the registry key is
always honored.

--
 
S

Stuart

I finally got it to work using MS Access's TransferSpreadsheet
method. If you are interested, I will post the rough code.

Regards and thanks.
 
O

onedaywhen

Stuart said:
I finally got it to work using MS Access's TransferSpreadsheet
method. If you are interested, I will post the rough code.

Regards and thanks.
Click to expand...

I not interested in the code thanks but I would be interested to hear
whether the registry settings I mentioned affected your
'TransferSpreadsheet' approach.

--
 
Top