ADO retrieval from close workbook-data type issue
 

I use the following procedures to retrieve data from a closed workbook and it works really well with one exception. If there is a column of data that is of mixed type, only the numeric data is retrieved, the alpha-numerics are omitted. Is there a way to modify the SELECT statement so that I get all the data, regardless of data type? I know database stuff is a lot more particular than Excel as far as data types, but, I really would like to not have to restrict some columns to a particular type, and I would like to access it through the below code, or something similar. In the sample below the data goes to a listbox; I have the same problem when it goes directly to the worksheet as it just doesn't get to be part of the recordset.
Thanks
Ken


Sub ADO_item_stones()
Dim src As String
src = "SELECT * FROM [Item_Milestones]"
Call ADO_items(src)
End Sub

Sub ADO_(fltr)

Dim Cnct As String, src As String
Dim Connection As ADODB.Connection
Dim recordset As ADODB.recordset
Dim dbfullname As String
Dim sh As String

'Construct Database information string and open the connection

Set Connection = New ADODB.Connection

dbfullname = Application.Substitute(ThisWorkbook.Path, "\Subcontracts", "\Estimates")
dbfullname = dbfullname & "\" & "Consolidated " & Sheet2.Range("job")..Value & ".xlsm"

Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & dbfullname & ";"
Cnct = Cnct & "Extended Properties=Excel 12.0"

On Error GoTo 300

Connection.Open ConnectionString:=Cnct

'Create RecordSet

Set recordset = New ADODB.recordset

recordset.Open Source:=fltr, ActiveConnection:=Connection

'Write the data to the listbox

Do Until recordset.EOF

With Stones.ListBox1
.AddItem recordset!Milestone
.list(.ListCount - 1, 1) = recordset!Name
.list(.ListCount - 1, 2) = recordset!Start
End With

recordset.MoveNext

Loop

Stones.Show

Set recordset = Nothing
Connection.Close
Set Connection = Nothing

Exit Sub

300

MsgBox "Could not connect to " & dbfullname

End Sub

ADO retrieval from close workbook-data type issue
 

If the field contains (or can contain) alpha-numeric data then its
'type' in the mdb table would/should be 'Text'. In this case there
would/should be no problem assigning text values to a listbox.list!

IOW, if your mdb data table field stores mixed types then your mdb
table most likely wasn't created in Access (which wouldn't permit such
poorly structured field typing)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

ADO retrieval from close workbook-data type issue
 

Garry
Thanks for the quick response. I should have noted that I am retrieving data from another spreadsheet; which of course allows totally unstructured data typing. Usually the method works great; it is just an occasional column that has mixed data. I was hoping there was some key word I could add to the SELECT statement that means please cut me some slack on the data typing, just throw what ever is there in to the recordset. I am not good on databases, but I was hoping there was a way.
Thanks
Ken


On Monday, December 15, 2014 3:25:24 PM UTC-5, GS wrote:
If the field contains (or can contain) alpha-numeric data then its
'type' in the mdb table would/should be 'Text'. In this case there
would/should be no problem assigning text values to a listbox.list!

IOW, if your mdb data table field stores mixed types then your mdb
table most likely wasn't created in Access (which wouldn't permit such
poorly structured field typing)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion