Dick,
I re-tested myself and kept getting mixed results. Sometimes I got the
apostrophe ('3), other times not (just 3). So I dug deeper...

I conclude the INT function indeed makes no difference. However, how
the worksheet was created seems to make a difference.

Here's my test function which demonstrates. I'm not entirely sure how
to recreate this 'manually' i.e. without using CREATE TABLE DDL.
Rather than use a recordset to test the data type, I open the workbook
and test the cell's Value property (and set a breakpoint and eyeball
it just to make sure!). Change the path to the file to suit and note
the code uses the Kill function on this file:

<Code-------------------
Option Explicit

Public Function IsInsertTypeString( _
Optional ByVal UseIntFunction As Boolean = False, _
Optional ByVal UseCreateTableInteger As Boolean = False _
) As Boolean

Const FILE_PATH As String = "C:\Tempo\TestDB.xls"
Const CON_PROVIDER As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Const CON_SOURCE As String = "Data Source="
Const CON_EXTENDED_HDR_YES As String = _
";Extended Properties='Excel 8.0;HDR=Yes'"
Const CON_EXTENDED_HDR_NO As String = _
";Extended Properties='Excel 8.0;HDR=No'"

Dim oWb As Excel.Workbook
Dim oCon As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strConHdrYes As String
Dim strConHdrNo As String
Dim strType As String

' Create new blank workbook
Kill FILE_PATH
Set oWb = Application.Workbooks.Add()
With oWb
.SaveAs FILE_PATH
.Close
End With

' Construct connection strings
strConHdrYes = CON_PROVIDER & CON_SOURCE & _
FILE_PATH & CON_EXTENDED_HDR_YES
strConHdrNo = CON_PROVIDER & CON_SOURCE & _
FILE_PATH & CON_EXTENDED_HDR_NO

Set oCon = New ADODB.Connection
With oCon

' Open connection (no headers)
.ConnectionString = strConHdrNo
.Open

' Create column headers
If UseCreateTableInteger Then
.Execute "CREATE TABLE Data" & _
" (Col1 INTEGER, Col2 INTEGER)"
Else
.Execute "CREATE TABLE Data" & _
" (Col1 CHAR(10), Col2 CHAR(10))"
End If
.Close
End With

Set oCon = New ADODB.Connection
With oCon

' Open connection (with headers)
.ConnectionString = strConHdrYes
.Open

' Insert data
If UseIntFunction Then
.Execute "INSERT INTO [Data$] (Col1, Col2) VALUES (INT(3),INT(5))"
Else
.Execute "INSERT INTO [Data$] (Col1, Col2) VALUES (3,5)"
End If

.Close

End With

Set oWb = Application.Workbooks.Open(FILE_PATH)

strType = TypeName(oWb.Worksheets("Data").Range("A2").Value)

oWb.Close

IsInsertTypeString = CBool(strType = "String")

End Function
</Code-------------------

In the Immediate Window:

? IsInsertTypeString(True,True)
False
? IsInsertTypeString(False,True)
False
? IsInsertTypeString(True,False)
True
? IsInsertTypeString(False,False)
True

--
"Dick Kusleika" wrote in message ...
"onedaywhen" wrote in message
om...
Dick, With Excel as the data source, you must use MS Jet syntax. The
INT function works for me e.g.

cmd.CommandText="INSERT INTO [Data$] (Col1,Col2) Values
(INT(3),INT(5))"

--

It does not for me, using

Sub InsInto()

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stConn As String
Dim stSQL As String
Dim LAIdx As Long, i As Long


'Set up connection string
stConn = "DSN=Excel Files;DBQ=c:\Dick\ADOTest.xls"
stConn = stConn & ";DefaultDir=c:\Dick;DriverId=22;"
stConn = stConn & "MaxBufferSize=2048;PageTimeout=5;"

'Set up sql to insert
stSQL = "Insert Into [Sheet2$] (US) values (INT(8))"

'Create new connection
Set Conn = New ADODB.Connection

'Open the connection
Conn.Open stConn

'Execute the sql
Conn.Execute (stSQL)

Conn.Close

End Sub