Insert table2 Select 型号,Sum(数量) From table1 Group By 型号
如果存在Table2的话用 Insert into tabl2(型号,数量)Select 型号,Sum(数量) From table1 Group By 型号
楼上代码可以实现 Accses要修改一点 Insert into tabl2(型号,数量)Select 型号,Sum(数量)as abc From table1 Group By 型号
cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f" 在数据库中可以实现,但在VB中报错:对象变量或WITH块不可用 请指教
Dim cnn1 As ADODB.Connection Dim rst As ADODB.Recordset Dim sqlstr As String Dim cnnstr As String Private Sub Form_Load() cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\feita.mdb;Persist Security Info=False" Set rst = New ADODB.Recordset cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f" If cnn1.State = adStateOpen Then cnn1.Close If rst.State = 1 Then rst.Close cnn1.Open cnnstr With rst .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open sqlstr, cnn1, , , adCmdText End With Set DataGrid1.DataSource = rst DataGrid1.RefreshEnd Sub 谢谢了
Dim cnn1 As ADODB.Connection Dim rst As ADODB.Recordset Dim sqlstr As String Dim cnnstr As String Private Sub Form_Load() cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\feita.mdb;Persist Security Info=False" Set rst = New ADODB.Recordset 'cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f" If cnn1.State = adStateOpen Then cnn1.Close If rst.State = 1 Then rst.Close cnn1.Open cnnstr cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f" With rst .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open sqlstr, cnn1, , , adCmdText End With Set DataGrid1.DataSource = rst DataGrid1.RefreshEnd Sub
Insert into tabl2(型号,数量)Select 型号,Sum(数量) From table1 Group By 型号
Accses要修改一点
Insert into tabl2(型号,数量)Select 型号,Sum(数量)as abc From table1 Group By 型号
在数据库中可以实现,但在VB中报错:对象变量或WITH块不可用
请指教
Dim rst As ADODB.Recordset
Dim sqlstr As String
Dim cnnstr As String
Private Sub Form_Load()
cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\feita.mdb;Persist Security Info=False"
Set rst = New ADODB.Recordset
cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f"
If cnn1.State = adStateOpen Then cnn1.Close
If rst.State = 1 Then rst.Close
cnn1.Open cnnstr
With rst
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open sqlstr, cnn1, , , adCmdText
End With
Set DataGrid1.DataSource = rst
DataGrid1.RefreshEnd Sub
谢谢了
Dim rst As ADODB.Recordset
Dim sqlstr As String
Dim cnnstr As String
Private Sub Form_Load()
cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\feita.mdb;Persist Security Info=False"
Set rst = New ADODB.Recordset
'cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f"
If cnn1.State = adStateOpen Then cnn1.Close
If rst.State = 1 Then rst.Close
cnn1.Open cnnstr
cnn1.Execute "Insert into dc2(r,d,c,t,t1,f,s)Select r,d,c,t,t1,f,Sum(s)as sums From dc1 Group By r,d,c,t,t1,f"
With rst
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open sqlstr, cnn1, , , adCmdText
End With
Set DataGrid1.DataSource = rst
DataGrid1.RefreshEnd Sub
使用的话自然提示with错误了