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

"onedaywhen" wrote in message
om...
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.


Interesting. So if you identify the data type in CREATE TABLE, it will
work. I've never created a table using CREATE TABLE, so that's a new one on
me. I wonder if there's any way to define the data type in a manually
created table.

I know that ADO has a tendency to inherit the data type from the previous
row. Instead of Col1 and Col2, I made my headings 1234 and 5678, but I
couldn't get the SQL to read it as a column heading. It appears that column
headings have to be strings.

When I did an INSERT INTO using

stSQL = "Insert Into [Sheet2$] (1234) values (8)"

(after changing my heading to '1234) it put in 8 as '8. When I replaced it
with a numeric 8, future INSERT INTOs showed as numeric, not strings.

Next, I deleted all the rows of the table (sans header) and formatted Row 2
of the sheet as a number data type just using cell formatting. I didn't
enter anything into the cells in Row 2. The I hid Row 2. When I ran the
INSERT INTO again, it came in as numeric. So maybe for setting up tables
manually, you define the data type a cell format in the first row, hide it,
and everything's keen.

Dick