SQL returns blank instead of text

[pmguerra]

Hi!

I'm using SQL to retrieve a bunch of data from another Excel file. However, SQL only returns numbers, leaving text records with blanks ("").

This only occurs in one column, which has numbers and alphanumeric values.

Any ideas on what is happening and how can I fix this?

Thanks in advance!

[DonkeyOte]

It might help to post your code (remember to use CODE tags)

I've found when using ADO that it's sometimes better to iterate through the recordset when handling datasets contain text fields than it is to return the entire recordset in one go ... in my experience mediumtext/longtext fields tend to get dropped... ie it's slower to process (not horrendously) but you get everything returned.... my experience is talking to MySQL/SQLSvr etc but the premise may be the same dealing with other files (I honestly don't know).

There may be other solutions... the above is just my 2 cents.

[pmguerra]

Thank you for your reply.

My code is very simple, I used the Macro Recorder to do the code for me and then customized the SQL statement to my needs.

origemSQL = "My Path Here"
comSQL = "SELECT DISTINCT TDadosRecolha.Circuito, TDadosRecolha.Mês,  TDadosRecolha.`Tipo Deposição`, TDadosRecolha.Fileira, TDadosRecolha.`Frequência Recolha`" & Chr(13) & _
            "FROM `" & origemSQL & "`.TDadosRecolha TDadosRecolha" 

origemSQL = "ODBC;DSN=Ficheiros Excel;DBQ=" & origemSQL & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

With ActiveSheet.QueryTables.Add(Connection:=origemSQL, _
        Destination:=Range("A1"))
        .CommandText = comSQL
        .Name = "Consulta 2 de Ficheiros Excel"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
    End With

[DonkeyOte]

See: http://support.microsoft.com/kb/257819 ... in particular I guess I'm referring to the Considerations section... this is outside my expertise so I can't help further -- hopefully others can assist as necessary...

[Ron Coderre]

One of the major differences between databases and spreadsheets is that
databases have tables with narrowly defined data types: Text, Integers, etc.
If a field is defined as text...only text is allowed in that field.

Your ADO connection will attempt to figure out the kind of data
your columns contain and will ignore other types. If the first few rows of
your data list contain mostly numbers, it will assume numeric data is in the column
and ignore text. If the first few rows contain mostly text, numeric data will be ignored.

The "fix" is to convert all of the column data to one type. Since you have a
mix of numbers and text...convert the numbers to text.
Here's one way:
Select the column of values.
Data.Text-to-columns...Click: Next....Click: Next
Select the data column...Check: Text...Click: Finish

Does that help?

[pmguerra]

Ok, that works fine! I converted all the values to TEXT and now all is well

Thank you for your help, Ron and DonkeyOte!