請問一下,我用以下的程式碼要產生報表的recordset,因為我用
access 2000當資料庫,故要將Jet.OLEDB.3.51改成Jet.OLEDB.4.0
但同樣的程式碼在做cmd.execute的時候就一直發生錯誤,不知哪
為前輩可告知還有哪地方要修改,謝謝
Dim cn As ADODB.Connection '連結資料庫的物件
Dim cmd As ADODB.Command ' Command 物件
Dim param1 As ADODB.Parameter ' Parameter 物件(開始日期)
Dim param2 As ADODB.Parameter ' Parameter 物件(結束日期)
Dim ret1 As String ' InputBox 函數的傳回值(開始日期)
Dim ret2 As String ' InputBox 函數的傳回值(結束日期)
Dim mySQL As String ' SQL 敘述
Dim rsRptMonOut As ADODB.Recordset '出庫月報表
' 取得製作出貨月報表的日期
' 開始日期
ret1 = InputBox("請輸入出貨月報表的開始日期" _
& Chr(13) & "例如 : 890401", "出庫月報表")
If ret1 = "" Then
Exit Sub
End If
'結束日期
ret2 = InputBox("請輸入出貨月報表的結束日期" _
& Chr(13) & "例如 : 890430", "出庫月報表")
If ret2 = "" Then
Exit Sub
End If
Set cn = New ADODB.Connection
cn.Provider = "MS DataShape"
cn.ConnectionString = "data provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=d:\lu\lu.mdb"
cn.CursorLocation = adUseClient
cn.Open
' 製作 Command 物件
mySQL = "Shape {SELECT 交易表頭表.貨單編號,交易表頭表.出貨日期," _
& "交易表頭表.客戶代號,客戶資料表.地址 " _
& " FROM 交易表頭表,客戶資料表 " _
& "WHERE 交易表頭表.客戶代號 = 客戶資料表.客戶代號 " _
& "AND (交易表頭表.出貨日期 BETWEEN ? AND ? ) } AS rptMonOut " _
& "APPEND ({SELECT 產品編號,數量,單價,金額 " _
& " FROM 交易明細表} As rptMonOutSub " _
& "RELATE 貨單編號 TO 貨單編號) AS rptMonOutSub"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = mySQL
'製作參數
Set param1 = New ADODB.Parameter
Set param1 = cmd.CreateParameter("prmStart", adVarChar, adParamInput, 6)
cmd.Parameters.Append param1
Set param2 = New ADODB.Parameter
Set param2 = cmd.CreateParameter("prmEnd", adVarChar, adParamInput, 6)
cmd.Parameters.Append param2
'MsgBox (ret1)
'MsgBox (ret2)
' 製作 Recordset 物件
cmd.Parameters("prmStart").Value = CLng(ret1)
cmd.Parameters("prmEnd").Value = CLng(ret2)
' MsgBox (cmd.Parameters("prmStart").Value)
Set rsRptMonOut = New ADODB.Recordset
Set rsRptMonOut = cmd.Execute