ADO inserts text strings not numbers when table is empty

[Rob Nicholson]

Bit of a stab in the dark this one. We're using standard ADO to access data
stored in an Excel workbook (don't ask why) and have a slight problem.
Consider a simple sheet containing the following heading and data:

Col1,Col2
1,3
2,4

If you execute a command like this:

Dim cmd As New ADODB.Command
cmd.Connection=ActiveConnection
cmd.CommandText="Insert Into [Data$] (Col1,Col2) Values (3,5)"
cmd.Execute

It works fine - the extra row is added contain the two *numbers* 3 & 5.
HOWEVER, if you delete the data rows out of the worksheet/table leaving just
the headings and carry out the same command, two *text* strings are written,
i.e. '3 and '5. I'm assuming that this is because Excel is unable to
determine the data types of the fields when the table is empty and therefore
defaults to text.

This can be confirmed by opening up a recordset on the empty table and
delving around the Fields collection. When the table is empty, Fields(0) is
a text string. However, then when with numbers, Fields(0) is a double.

Is there anyway around this? Some way to tell Excel the data types of the
fields in an empty Excel table?

Cheers, Rob.

 

[Dick Kusleika]

Rob

I'm stumped. I tried CAST and CONVERT but the SQL used by Excel doesn't
seem to support those functions.

I think what you'll need to do after your Execute statement is something
line

Sheets("Data").Range("A65536").End(xlUp).Value = _
CLng(Sheets("Data").Range("A65536").End(xlUp).Value)

But that would require that the destination workbook be open - which kind of
negates the benefit of using ADO in the first place.

Sorry I couldn't be more helpful.

 

[onedaywhen]

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))"

--

Rob

I'm stumped. I tried CAST and CONVERT but the SQL used by Excel doesn't
seem to support those functions.

I think what you'll need to do after your Execute statement is something
line

Sheets("Data").Range("A65536").End(xlUp).Value = _
CLng(Sheets("Data").Range("A65536").End(xlUp).Value)

But that would require that the destination workbook be open - which kind of
negates the benefit of using ADO in the first place.

Sorry I couldn't be more helpful.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Bit of a stab in the dark this one. We're using standard ADO to access data
stored in an Excel workbook (don't ask why) and have a slight problem.
Consider a simple sheet containing the following heading and data:

Col1,Col2
1,3
2,4

If you execute a command like this:

Dim cmd As New ADODB.Command
cmd.Connection=ActiveConnection
cmd.CommandText="Insert Into [Data$] (Col1,Col2) Values (3,5)"
cmd.Execute

It works fine - the extra row is added contain the two *numbers* 3 & 5.
HOWEVER, if you delete the data rows out of the worksheet/table leaving just
the headings and carry out the same command, two *text* strings are written,
i.e. '3 and '5. I'm assuming that this is because Excel is unable to
determine the data types of the fields when the table is empty and therefore
defaults to text.

This can be confirmed by opening up a recordset on the empty table and
delving around the Fields collection. When the table is empty, Fields(0) is
a text string. However, then when with numbers, Fields(0) is a double.

Is there anyway around this? Some way to tell Excel the data types of the
fields in an empty Excel table?

Cheers, Rob.

 

[Dick Kusleika]

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]

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, 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

 

[Dick Kusleika]

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