Office中国论坛/Access中国论坛

标题: Ado如何操作多个表！ [打印本页]

作者: chenyingfengsx 时间: 2011-3-10 10:24
标题: Ado如何操作多个表！
Dim rst As ADODB.Recordset
Dim strSQL As String
Set rst = New ADODB.Recordset

Dim temp As String
Dim pwzhi As String
Dim gwzhi As String
Dim myarry() As String
Dim myarry1() As String
Dim i As Integer
Dim j As Integer
Dim s As String
Dim s1 As String

If Me![blei] = "制辊" Then
      Me.pwzhi = "制辊"
      Me.gwzhi = "制辊"
Else
      Me.pwzhi = "片子线"
      Me.gwzhi = "机加工"
End If
If Me.bchang <= 1150 And Me.zhchang / 3.1416 <= 220 Then

   Me.shkong = 1
End If
'插入数据到main表里面
   temp = "insert into main (mac,fen,dkong,xdu,dklxing,shkong,dzeng,jcao,chchang,kykban,lbie,zhlei,bhao,blei,shchlbie,shchzbie,zhshu,gxhao,zhhzhshu,fgzhshu,zdlzhshu,bshchu,khming,pming,ywyuan,jgrqi,djshjian,wtshjian,yfrqi,zxshjian,bchang,zhchang,pwzhi,gwzhi,djren,dzhfou,cliao,bzhu)"
temp = temp & "values('" & Me![mac] & "','" & Me![fen] & "','" & Me![dkong] & "','" & Me![xdu] & "','" & Me![dklxing] & "','" & Me.shkong & "','" & Me![dzeng] & "','" & Me![dzeng] & "','" & Me![chchang] & "','" & Me![kybhao] & "','" & Me![lbie] & "','特种版','" & Me![Findbhao] & " ','" & Me![blei] & " ','新制','" & Me![shchzbie] & "','" & Me![zhshu] & " ','" & Me![gxhao] & "','" & Me![zhhzhshu] & " ','" & Me![zhshu] & " ','" & Me![zdlzhshu] & " ','" & Me![bshchu] & "','" & Me![khming] & "','" & Me![pming] & "','" & Me![ywyuan] & "','" & Date & "','" & Now() & "','" & Me![wtshjian] & "','" & Me![yfrqi] & " " & Time & "','" & Now() & "','" & Me![bchang] & "','" & Me![zhchang] & "','" & Me![pwzhi] & "','" & Me![gwzhi] & "','" & Me![djren] & "','" & Me![dzhfou] & "','" & Me![cliao] & "','" & Me![bzhu] & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL temp
   '插入数据到jihua表里面
If Me.shchzbie = "包装版" Then
   Me.jjjhua = 16
Else
   Me.jjjhua = 24
End If

temp = "insert into jihua (bhao,gzzhjhua,jjjhua)"
temp = temp & "values('" & Me![Findbhao] & " ', ' " & Now() + 24 / 24 & " ', ' " & Now() + Val(Me.jjjhua) / 24 & " ')"
DoCmd.SetWarnings False
DoCmd.RunSQL temp


   'piaoju表的记录
strSQL = "select * from piaoju where bhao = '" & Me![Findbhao] & " '"
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
      If rst.RecordCount > 0 Then
         temp = "update piaoju set jprqi=now() where bhao='" & Me![Findbhao] & " '"
         DoCmd.SetWarnings False
         DoCmd.RunSQL temp
      Else
         temp = "insert into piaoju (bhao,jprqi) values('" & Me![Findbhao] & " ','" & Now() & " ')"
         DoCmd.SetWarnings False
         DoCmd.RunSQL temp
      End If
   rst.close

'pianzi表记录
strSQL = "select * from pianzi where bhao = '" & Me![Findbhao] & " '"
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
      If rst.RecordCount > 0 Then
         temp = "update pianzi set chpwzhi='电拼' where bhao='" & Me![Findbhao] & " '"
         DoCmd.SetWarnings False
         DoCmd.RunSQL temp
      Else
         temp = "insert into pianzi (bhao,chpwzhi) values('" & Me![Findbhao] & " ','电拼')"
         DoCmd.SetWarnings False
         DoCmd.RunSQL temp
      End If
      rst.close
  '更新dengjibanhao表数据

temp = "UPDATE dengjibanhao  SET bhao = bhao+1 where  dengjibanhao.blei='" & Me![blqfen] & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL temp

      '增加新纪录GeGongXuWanChengJiLu表
         temp = "insert into GeGongXuWanChengJiLu (bhao,zhshu,gxhao,bzu,czzhe,gxmcheng,jhgxu,wchshjian) values('" & Me![Findbhao] & " ','" & Me![zhshu] & " ','" & Me![gxhao] & " ','" & Me![djren] & "','" & Me![djren] & "','登记','片子线','" & Now() & "')"
         DoCmd.SetWarnings False
         DoCmd.RunSQL temp

         temp = "insert into GeGongXuWanChengJiLu (bhao,zhshu,gxhao,bzu,czzhe,gxmcheng,jhgxu,wchshjian) values('" & Me![Findbhao] & " ','" & Me![zhshu] & " ','" & Me![gxhao] & " ','" & Me![djren] & "','" & Me![djren] & "','登记','机加工','" & Now() & "')"
         DoCmd.SetWarnings False
         DoCmd.RunSQL temp

'插入数据到Gunjindu表里

s = Me![gxhao]
s1 = Me![ghao]
myarry = Split(s, ".")
myarry1 = Split(s1, ".")
For i = 0 To UBound(myarry)
temp = "insert into GunJinDu (bhao,ghao,gxhao,gwzhi,ggchcyuan)"
temp = temp & "values('" & Me![Findbhao] & " ','" & myarry1(i) & " ','" & myarry(i) & "','" & Me![gwzhi] & "','" & Round(Me![zhchang] / 3.1415926, 2) + 0.1 + Me![dzeng] * (myarry1(i) - 1) & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL temp
Next i

上面的代码，我想通过ODBC连接后台是sql的数据库，出现不能更新或者操作第二个表，第一个表能够更新或者插入。这个问题好困扰啊！

作者: chenyingfengsx 时间: 2011-3-11 13:46
问题解决了！是链接数据库表的时候没有指定唯一性！

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)