用ADO調(diào)用ACCESS時應(yīng)注意的SQL語句書寫方法
這是我的再貼(初學(xué)者有益) 對EXCEL編程用ADO調(diào)用ACCESS時值得注意! 雖然是一個老的話題，但有必要在這里再次得到關(guān)注 ------------------------------------------------------------------------------- 一段代碼可以說明一切： 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 -------------測試當(dāng)前路徑用 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中應(yīng)用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會認(rèn)為是文本型+
+++++++++++
#號的作用是：操作日期類型字段的字符時所必須的，例如：#1996-07-01# 否則直接書寫為 1996-07-01 將中斷程序提示出錯！
