Retraction, Clarification, Whatever

Obviously you are correct in your admonishment and any
even the casual student of SQL know that all sorts of selection
and action queries can be sent to a DB.

My statement should have read: With the code Im posting I was
unable to return both numbers and text from the same column
so I simply sent a second query to the workbook.

That being qualified, the code is fast, stable, free and answers the
specific question.

" have a closed work book I wish to extract results from into my open
workbook"


TK

"Jamie Collins" wrote:

"TK" wrote ...

Jet will not return both strings and numbers
in the same recordset so use rs for one, rs1 for
the another.

Where did you get this idea? This is clearly incorrect.

A quick demo:

Sub Test()
Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
With Con

' Create Jet data source
.Open "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;" & _
"Extended Properties='Excel 8.0';"

' Create table with numeric and text columns
.Execute "" & _
"CREATE TABLE MyTable (" & _
" MyNumberCol FLOAT NULL," & _
" MyTextCol VARCHAR(255) NULL);"

' Create data
.Execute "" & _
"INSERT INTO MyTable" & _
" (MyNumberCol, MyTextCol)" & _
" VALUES (55.55, 'test');"

' Open recordset
Dim rs As Object
Set rs = .Execute("" & _
"SELECT MyNumberCol, MyTextCol" & _
" FROM MyTable;")

MsgBox "" & _
"MyNumberCol is " & _
TypeName(rs.fields("MyNumberCol").Value) & vbCrLf & _
"MyTextCol is " & _
TypeName(rs.fields("MyTextCol").Value)

rs.Close
.Close

End With

End Sub


Jamie.

--