SQL语句在线求助一点通大师，请出手一助！

zyz218

用SQL语句把PreviousPlan中所有记录换成ProducePlan中的记录，不能用删除表再将ProducePlan另存为PreviousPlan的方式，因为PreviousPlan和其它表建立了关系，因些需要用SQL来刷新记录，在下认为要先删除PreviousPlan中所有记录，再将ProducePlan所有记录写入PreviousPlan，不知SQL怎样写，在下试着写了几次都不成功，请一点通大师帮帮忙，多谢！！！！
<IMG>

zyz218

<IMG>

[此贴子已经被作者于2005-12-22 18:53:09编辑过]

fan0217

把你例子传上来，一点通大师才好出手啊！

zyz218

、代码写在ProducePlan窗体中,谢谢关注

阿笨

我这里存不来文件,但想想有一个很低效率的办法



'删除原表

docmd.runSql("delect * from PreviousPlan")

'取出新值

set rs=currentproject.connection.execute("select * from ProductPlan")

'填充

do while not rs.eof

   docmd.runSql("insert into PreviousPlan (字段列表) values (" & rs(字段列表) & ")") '这句根据实际写,更改文本字段中的"'"为"''",日期字段要前后加"#",文本字段要加"'"....

   rs.movenext

loop

rs.close

set rs=nothing

wwwwa

用UPDATE即可

UPDATE PreviousPlan A，ProducePlan B SET A。NIAN=B。NIAN。。。。。 WHERE A。LINENAME=B。LINENAME

zyz218

If MsgBox("Are you sure to update the previous record ? You can't comeback it after updating！", vbOKCancel, "Notice") = vbOK Then
    DoCmd.RunSQL ("delete * from PreviousPlan")
    Set rs = CurrentProject.Connection.Execute("select * from ProducePlan")
    Do While Not rs.EOF
    DoCmd.RunSQL ("insert into PreviousPlan (LineName) values (" & rs(LineName) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (Nian) values (" & rs(nian) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (Yue) values (" & rs(yue) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (ProductionName) values (" & rs(ProductionName) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (Qty) values (" & rs(Qty) & ")")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
Else
    Exit Sub
End If

以上中：

DoCmd.RunSQL ("insert into PreviousPlan (LineName) values (" & rs(LineName) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (Nian) values (" & rs(nian) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (Yue) values (" & rs(yue) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (ProductionName) values (" & rs(ProductionName) & ")")
    DoCmd.RunSQL ("insert into PreviousPlan (Qty) values (" & rs(Qty) & ")")

请阿笨看看错在那里？

zyz218

一定要一行一行地写进去？

zyz218

6楼看不懂


WHERE A。LINENAME=B。LINENAME

以上有问题应该

[此贴子已经被作者于2005-12-23 8:37:43编辑过]

一点通

如果代码不是很熟的话,建议用:

docmd.runSql("delect * from PreviousPlan")   '先清空PreviousPlan表中的数据

然后创建一个追加查询,将ProducePlan中的所用记录追加到PreviousPlan去