How to use Excel VBA to extract Memo field from Access Database?
I have an Excel spreadsheet. I am connecting to an Access database via ODBC. Something along then lines of:
Set dbEng = CreateObject("DAO.DBEngine.40")
Set oWspc = dbEng.CreateWorkspace("ODBCWspc", "", "", dbUseODBC)
Set oConn = oWspc.OpenConnection("Connection", , True, "ODBC;DSN=CLIENTDB;")
Then I use a query and fetch a result set to get some table data.
Set oQuery = oConn.CreateQueryDef("tmpQuery")
oQuery.Sql = "SELECT idField, memoField FROM myTable"
Set oRs = oQuery.OpenRecordset
The problem now arises. My field is a dbMemo because the maximum content length is up to a few hundred chars. It's not that long, and in fact the value I'm reading is only a dozen characters. But Excel just doesn't seem able to handle the Memo field content at all. My code...
ActiveCell = oRs.Fields("memoField")
...gives error Run-time error '3146': ODBC--call failed.
Any suggestions? Can Excel VBA actually get at memo field data? Or is it just completely impossible. I get exactly the same 开发者_如何学运维error from GetChunk as well.
ActiveCell = oRs.Fields("memoField").GetChunk(0, 2)
...also gives error Run-time error '3146': ODBC--call failed.
Converting to a text field makes everything work fine. However some data is truncated to 255 characters of course, which means that isn't a workable solution.
- Try
Range.CopyFromRecordset
to see if it works. - Try using
CStr(oRs.Fields("memoField"))
and assign toValue2
of theRange
/ActiveCell
. - Try making the memo field the last physical column in table. A memo field is read only when retrieved. There was/may-still-be an issue with memo fields that are not at physically at the end of a table.
All I can think of right now.
精彩评论