用ADO调用ACCESS时应注意的SQL语句书写方法这是我的再贴(初学者有益) 对EXCEL编程用ADO调用ACCESS时值得注意! 虽然是一个老的话题，但有必要在这里再次得到关注 ------------------------------------------------------------------------------- 一段代码可以说明一切： Private Sub CommandButton1_Click() Dim oConn As New ADODB.Connection Dim oRs As New ADODB.Recordset Dim RS1 As New ADODB.Recordset Dim a As String Dim b As String Dim path1 As String Dim sql_2 As String path1 = CurDir() & Application.PathSeparator & "Northwind.mdb" Rem--------- MsgBox path1 -------------测试当前路径用 oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & path1 & ";" & _ "User Id=admin;" & _ "Password=;" a = Me.TextBox1.Text b = Me.TextBox2.Text Rem --------EXCEL中应用ADO查询语句注意的问题 Rem ---------请注意在Between ......And语句中连接日期字符变量用法：即不采用 '" & 变量 & "'方式，而是直接用 & 符号连接变量。 Rem----------而在其他SQL的Where条件字符后则采用 '" & 变量 & "'方式。 sql_2 = "select * from 订单 where 订单.订购日期 between #" & a & "# and #" & b & "#" & "and 订单.客户ID = '" & Me.ComboBox1 & "'" RS1.CursorLocation = adUseClient RS1.Open sql_2, oConn, adOpenKeyset, adLockOptimistic Sheet2.Range("c1").CopyFromRecordset RS1 End Sub
+++++++++++++++++++++++++++++++++++++++++
ACCESS的VBA要用#号代替单引号，，否则ACCESS会认为是文本型+
+++++++++++
#号的作用是：操作日期类型字段的字符时所必须的，例如：#1996-07-01# 否则直接书写为 1996-07-01 将中断程序提示出错！
|
|
|
