Dim rstTree As ADODB.Recordset Dim strSql As String strSql = "SELECT lngNodeId, strNodeName FROM " & m_strTreeTable & " WHERE (strNodeName Like 'NewNode*') " Set rstTree = New ADODB.Recordset With rstTree .ActiveConnection = CurrentProject.Connection .Source = strSql .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Open options:=adCmdText End With debug.print rstTree.RecordCount rstTree.Close Set rstTree = Nothing
像上面这样打开recordset,得到的recordcount是0。但实际上,符合条件的记录有20条。无奈之下,改成下面这样写(把strSql里的where子句去掉,打开记录集之后,设置recordset的filter为那个where子句的内容),就能得到正确的记录数。
Dim rstTree As ADODB.Recordset Dim strSql As String strSql = "SELECT lngNodeId, strNodeName FROM " & m_strTreeTable Set rstTree = New ADODB.Recordset With rstTree .ActiveConnection = CurrentProject.Connection .Source = strSql .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Open options:=adCmdText End With rstTree.Filter = "strNodeName Like 'NewNode*'" debug.print rstTree.RecordCount rstTree.Close Set rstTree = Nothing
这样虽然解决了问题,可是很不爽。后来vbman2003在这个帖子的9楼指点我:jet数据引擎在like中支持的通配符是%不是*。后来我改成下面这样就行了。
strSql = "SELECT lngNodeId, strNodeName FROM " & m_strTreeTable & " WHERE (strNodeName Like 'NewNode%') "
ACCESS 内部,为了方便普通用户。微软使用了 * 和 ? 做为通配符。但SQL国际标准中则是 % 和 _ ,所以在通过ODBC,ADO,DAO等来访问数据的时候只能用 % 和 _ 。(来自这个帖子ACMAIN_CHM的回复)