Using SQL with VBA in Excel
在利用ADO+SQL查询ACCESS数据库
VB中是这样的
?:任意单个字符 如LIKE ‘?南’ ,LIKE ‘?南?’
*:零个或多个字符 如:LIKE ‘*南’ , LIKE ‘*南*’
而在VBA中则不能这样用,它的用法竟然和SQL查询SQL Server数据库的语法一致:
_ :任意单个字符 如LIKE ‘_南’ ,LIKE ‘_南_’
%:零个或多个字符 如:LIKE ‘%南’ , LIKE ‘%南%’
Ex:
Private Sub BtnSearch_Click()
Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim Stpath, strSQL As String
Stpath = ThisWorkbook.Path & Application.PathSeparator & “student_sample.mdb”
CNN.Open “provider=Microsoft.jet.OLEDB.4.0;data source=” & _
Stpath ‘& “;Jet OLEDB:Database Password=” & “123″
‘Right way
strSQL = “Select * from 档案 WHERE 籍贯 LIKE ‘%南%”
‘Wrong way
’strSQL = “Select * from 档案 WHERE 籍贯 LIKE ‘*南*’”
RST.Open strSQL,CNN
Sheet1.Range(“A2:G100″).ClearContents
Sheet1.Cells(2, 1).1yFromRecordset RST
RST.Close
Set RST = Nothing
Set CNN = Nothing
End Sub
?:任意单个字符 如LIKE ‘?南’ ,LIKE ‘?南?’
*:零个或多个字符 如:LIKE ‘*南’ , LIKE ‘*南*’
DOS 还有现在Windows系统搜索都还是这样子的~~ :em03:
可VBA这样就不可以。
第一次用VBA写程序的时候，郁闷了好久～
后经人指点才知道的～ :em14: