如何使用Excel VBA从Access数据库中提取备注字段？
我有一个 Excel 电子表格。我正在通过 ODBC 连接到 Access 数据库。然后
Set dbEng = CreateObject("DAO.DBEngine.40")
Set oWspc = dbEng.CreateWorkspace("ODBCWspc", "", "", dbUseODBC)
Set oConn = oWspc.OpenConnection("Connection", , True, "ODBC;DSN=CLIENTDB;")
我使用查询并获取结果集来获取一些表数据。
Set oQuery = oConn.CreateQueryDef("tmpQuery")
oQuery.Sql = "SELECT idField, memoField FROM myTable"
Set oRs = oQuery.OpenRecordset
现在问题就出现了。我的字段是 dbMemo，因为最大内容长度可达几百个字符。它并没有那么长，事实上我正在读取的值只有十几个字符。但 Excel 似乎根本无法处理备注字段内容。我的代码...
ActiveCell = oRs.Fields("memoField")
...给出错误运行时错误'3146'：ODBC调用失败。
有什么建议吗？ Excel VBA实际上可以获取备注字段数据吗？或者说这完全不可能。我也从 GetChunk 得到完全相同的错误。
ActiveCell = oRs.Fields("memoField").GetChunk(0, 2)
...还给出错误运行时错误'3146'：ODBC-调用失败。
转换为文本字段使一切正常。然而，有些数据当然会被截断为 255 个字符，这意味着这不是一个可行的解决方案。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
CStr(oRs.Fields("memoField"))并分配给Range/ActiveCell的Value2。我现在能想到的就这些了。
Range.CopyFromRecordsetto see if it works.CStr(oRs.Fields("memoField"))and assign toValue2of theRange/ActiveCell.All I can think of right now.