This is the closest I could find to any details on MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcje
tmicrosoft_excel_data_types.asp

No mention of MEMO but it can be explicitly created e.g.

Sub ExcelMemo()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
 .ConnectionString = _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=C:\DropMe.mdb"
 .Open

 .Execute _
 "CREATE TABLE" & _
 " [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
 ".TestExcel2" & _
 " (text_col NVARCHAR(255), memo_col MEMO);"

 ' This will succeed, showing the MEMO
 ' col can accommodate 300 characters
 .Execute _
 "INSERT INTO " & _
 " [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
 ".TestExcel2 (text_col, memo_col)" & _
 " VALUES (STRING$(255, 'x')," & _
 " STRING$(300, 'x'));"

 ' This will succeed, showing the NVARCHAR
 ' col cannot accommodate 300 characters
 .Execute _
 "INSERT INTO " & _
 " [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
 ".TestExcel2 (text_col, memo_col)" & _
 " VALUES (STRING$(300, 'x')," & _
 " STRING$(300, 'x'));"
   
 .Close
End With
End Sub

--

Jamie.

--

Hi Jamie,

Having installed Office 2003 SP2 I can't do this sort of thing any more.
I hacked your sub so it executed only the CREATE TABLE statement (adding
a TEXT column for the hell of it), and then opened the resulting .xls
file and typed the 0.00025 and 0.00035 into the first two blank rows.

The values in cur_col were not rounded, and the values in text_col were
recognised as numbers, so it seems that - as I thought - even though the
Excel driver has a concept of column types, Excel itself doesn't.

Going a step further, I re-hacked your code to connect to the worksheet
and display the data (using $ on the sheet name to get the UsedRange
because I hadn't updated the "TextExcel" named range when I typed in the
data):

 Dim rs
 Set rs = .Execute( _
 "SELECT cur_col, float_col, text_col FROM" & _
 " [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
 ".[TestExcel$];")

 MsgBox _
 "cur_col" & vbTab & "float_col" & vbTab & "text_col" & vbCr & _
 rs.GetString
 rs.Close

Again, the values in cur_col were not rounded.

So I surmise that the situation is this:
1) It's possible to use the Excel driver to execute a CREATE TABLE
statement that specifies column types.

2) Doing so does nothing to restrict the kinds of data that can be
inserted into the columns via the Excel UI or VBA.

3) But provided you don't drop the connection and aren't using an
up-to-date installation of Access 2002 or 2003, the Excel driver will
coerce values you inserte or update by executing SQL statements.

Here's a couple of things I'd try if I had an older machine here:

a) modify your test sub to execute this:

 "INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
 ".TestExcel (cur_col, float_col)" & _
 " VALUES (12345679012345.1234, 12345679012345.1234);"

Then dive into VBA and check the Formula and Value properties of the
cells. If the CREATE TABLE created a currency column, the cur_col value
will have all 19 digits, while float_col will lose the last three or so.

b) close the connection, then re-connect and try to execute something
like this

 "INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
 ".TestExcel (cur_col, float_col)" & _
 " VALUES ('This is a numeric value', 'Me too');"

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.