Access to Excel Sql Query Memo Field

[slicerdh]

I have a vb macro which does a sql query to an access database retrieving an index field (autonumber), Description (memo), AsOfDate (date). when displayed on the spread sheet everything except the description displays as it should however the memo field only displays the first character of the memo and nothing else. When I do an "Add Watch" on the dbrecord being retrieved I see the full memo description but the spread sheet only shows the one character. What setting/format/option am I overlooking to get the full description?

 

[DonkeyOte]

can you post up the code you're using that actually posts the recordset to Excel ?

You may find you need to iterate the recordset in order to retrieve the descriptions in full.

 

[DonkeyOte]

below is some code I use on a regular basis -- you can ignore the parsed variables -- essentially all this routine does is execute a SQL statement (sqlstr) using a defined DSN connection (or full driver connection) and returns the resulting recordset to a predefined location (st = sheet, rw = row, co = column) -- with a couple of flags to determine as to whether or not headers are required and/or iteration of recordset is required -- when text type fields are incl in the recordset.

I hope this helps you.

Sub get_recordset(st As String, dsnstr As String, sqlstr As String, hdr As String, iterate As String, rw As Long, co As Integer)
'determine original sheet
Dim original_st As String
original_st = ActiveSheet.Name
Sheets(st).Select
Set c = New ADODB.Connection
Select Case InStr(UCase(dsnstr), "DRIVER")
    Case Is > 0
        c.Open dsnstr
    Case Else
        c.Open "DSN=" & dsnstr
End Select
sq = sqlstr
Set r = c.EXECUTE(sq)
If r.EOF = False Then
    'populate row with field names?
    If hdr = "T" Then
        For i = 0 To r.Fields.Count - 1
            Cells(rw, co + i) = r.Fields(i).Name
        Next i
        rw = rw + 1
    End If
    'need to iterate through the recordset (long text fields)?
    If iterate = "F" Then
        Cells(rw, co).CopyFromRecordset r
    Else
        r.MoveFirst
        Do Until r.EOF = True
            For i = 0 To r.Fields.Count - 1
                Cells(rw, co + i) = r.Fields(i).Value
            Next i
            r.MoveNext
            rw = rw + 1
        Loop
    End If
End If
c.Close
'return to original sheet
Sheets(original_st).Select
End Sub

 

[slicerdh]

Here is the code used to retrieve the record set

The primary routine is -

WSql = "Select * From DevelopmentNotes Where Not Complete"
QueryDatabase 2, 1


Calls QueryDatabase subroutine


Sub QueryDatabase(iRow, iCol)
'
' QueryDatabase Subroutine
' Subroutine written 8/30/2004 by David Slicer
'
' Purpose is to query the database tables for processing
'
' Variables are
' iRow - the sheet cell possition to start the load
' iCol - the sheet cell offset to load the info
'
Dim dbActive As Database
Dim dbRecord As Recordset
RetryQuery:
' Query the database
On Error GoTo ErrorHandler
Set dbActive = OpenDatabase(WPathName + "\DVCTracking.mdb")
Set dbRecord = dbActive.openrecordset(WSql)
With ThisWorkbook.Sheets("DataSelection")
With .Cells(iRow, iCol)
.CurrentRegion.Clear
numberofRows = .CopyFromRecordset(dbRecord)
End With
End With
dbActive.Close
GoTo Finish
ErrorHandler:
' Errors have occurred
If Err.Number = 3024 Or Err.Number = 3078 Then
CreateTables "DVCTracking.mdb"
GoTo RetryQuery
Else
ErrorOk "Error Quering Database: DVCTracking.mdb" + Chr(13) + _
"Table Name is " + TName + Chr(13) + Chr(13) + "Error Number is " + _
Str(Err.Number) + Chr(13) + Err.Description
Exit Sub
End If
Finish:
Set dbActive = Nothing
Set dbRecord = Nothing
On Error GoTo 0
End Sub