字“大小”从MS访问中检索数据时，不用作为列名称
尝试从MS Access数据库中检索VBA中的数据时，我遇到了一个奇怪的错误。我得到错误：
运行时错误'-2147467259（80004005）'：方法'execute'对象'_connection'失败。
最初，当我遇到错误时，我的SQL语句中有一长串列列表，但是通过反复试验，我发现问题与名为“ size”的列有关。此后，我尝试将列命名为重命名，除了任何版本的“大小”（大小，大小，大小等）的任何版本外，似乎都可以使用。
有人知道为什么吗？
代码：
Sub test()
Dim connStr, objConn
Dim sPath As String
sPath = "[path]"
fullPath = sPath & "\[dbname].accdb"
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & fullPath
'Define object type
Set objConn = CreateObject("ADODB.Connection")
'Open Connection
objConn.Open connStr
Dim queryStr As String
queryStr = "SELECT ID, Size FROM primary_table"
Set rs = objConn.Execute(queryStr) 'This is were the error occurs
objConn.Close
Set rs = Nothing
Set objConn = Nothing
End Sub
I have encountered a strange error when trying to retrieve data in VBA from an MS Access database. I get the error:
Run-time error '-2147467259 (80004005)': Method 'Execute' of object '_Connection' failed.
I initially had a long list of columns in my SQL statement when I got the error but with trial and error I discovered that the issue was with the column named "Size". I since tried to rename the column and it seems like anything works except any versions of the word "size" (Size, size, sizE etc.).
Anyone have an idea of why?
Code:
Sub test()
Dim connStr, objConn
Dim sPath As String
sPath = "[path]"
fullPath = sPath & "\[dbname].accdb"
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & fullPath
'Define object type
Set objConn = CreateObject("ADODB.Connection")
'Open Connection
objConn.Open connStr
Dim queryStr As String
queryStr = "SELECT ID, Size FROM primary_table"
Set rs = objConn.Execute(queryStr) 'This is were the error occurs
objConn.Close
Set rs = Nothing
Set objConn = Nothing
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
正如问题评论中指出的那样：“
size>”是访问中的保留单词，因此必须是：[size];或primary_table.size最佳实践是避免首先使用保留的单词作为列或表名称。有关保留单词的全面列表，请参阅艾伦·布朗（Allen Browne）的出色站点：
问题名称和访问中的单词
As was pointed out in the question comments, "
Size" is a reserved word in Access, so it must be EITHER:[Size]; ORprimary_table.SizeBest practice is to avoid using reserved words as column or table names in the first place. For a comprehensive list of reserved words, refer to Allen Browne's superb site:
Problem names and reserved words in Access