Solved: Incomplete ADO load

[Remalay]

HELP!

I'm using ADO to retrieve a spreeadsheet from a closed workbook. The operation works fine but not all the data is transferred (range A2:AG98). Reveiwing the transferred data there are multiple segements throughout the table where there are blank cells which should contain data.

I vaguely recall something about ADO testing the first few rows in each column to detect the data type, then using that for the remainder???? Not sure if this even correct, nor if it would apply, but the blank cells always seem to be within columns of mixed number/text data, and looks as if either one or the other set is not transferring (eg col 'H' only cells containing numbers have copied, col 'M' only cells containing text have copied)

Can someone please advise me of a possible work-araound?

thanks
remalay

[Simon Lloyd]

Remalay would you like to post your code?, how are you collecting the recordset? Do you use any of the Recordset Events e.g FetchProgress or FetchComplete? Do you use BOF & EOF (begining of file, end of file)? are you filtering the recordset before collection?

Maybe just posting your workbook with a short explanation will get you the answers you require!

Regards,
SImon

[Remalay]

Hope it helps.......

[vba]
Option Explicit
Public Sub getFdb()
ADOgetFdb "SELECT * FROM [Data$]"
End Sub
'***********************************
Public Function ADOgetFdb(szSQL As String)
Dim rsData As ADODB.Recordset
Dim szConn As String

'..create the connection string
szConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Fsrc & ";" & _
"Extended Properties=Excel 8.0;"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConn, adOpenForwardOnly, adLockReadOnly, adCmdText

'...check for data received into Sheet
If Not rsData.EOF Then
Sheet1.Range("A2").CopyFromRecordset rsData
Else
MsgBox " Your copy of sheet(" & szSQL & ")" & vbNewLine & _
" was not updated from the Master database.", vbCritical
End If

'..clean up the recordet object
rsData.Close
Set rsData = Nothing
End Function
[/vba]

[Simon Lloyd]

Also forgot to mention ADO does not supportthe copying of formats

[Remalay]

Simon,

There's no formatting involved. Transferred text is then displayed via userforms.

The majority of the columns contain similar cell data, numbers or text strings.
In several columns of the table cells contain either text or numbers; eg an address field where the entry may be a house name or street number. The source table contains all the info, but only the house numbers are transferred through the ADO. Yet the reverse is true on another column containing mixed data types.

rgds
remalay

[Remalay]

Further investigation reveals that it is dependant upon the entries in the first 10 rows of the specific column; If most are text, then numeric entries are not transferred, alternatively, if most are numeric, then all text values in that column are not transferred.

[Remalay]

I've found myself a work-around........
by testing each value before it is stored to the source wkbk, and, ifnumeric, prefix it with a single inverted comma;

[vba]
If IsNumeric(rng.Value)=True Then
rng.Value= "'" & rng.Value
End If
[/vba]

Not and ideal solution, but a 'fix' nonetheless.........


anyone got a better fix?

rgds
remalay
.

[RichardSchollar]

Hi Remalay

The microsoft article here explains problems with mixed data types and Excel: "//support.microsoft.com/kb/257819"


the relevant bit is about half way down. It does suggest holding the data in Excel as text and converting to numerics once retrieved by the query if required. There is also the possibility of modifying the connection string if you want Read-Only data, altho it does mention that this can cause subsequent problems. Check it out!

Richard

[Remalay]

Thanks Richard, at least the article explains the issue and provides workarounds, and, more importantly, ensures I wasn't doing something stupid.

Thanks again.
remalay