不同數據庫資料的導入

[kate_sun]

Private Sub ActiveReport_ReportStart()
Dim strabcd As String '兵ン
Dim rsTemp As ADODB.Recordset
Dim strRstJ As String

Dim rs As ADODB.Recordset
Dim Cnn2 As ADODB.Connection
Set Cnn2 = New ADODB.Connection
Cnn2.CursorLocation = adUseClient
Cnn2.ConnectionTimeout = 30
Cnn2.CommandTimeout = 60
Cnn2.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=tool;Password=tool123;Data Source=BIAP\BISQL;"
'本地數據庫
Set rsTemp = cnn.Execute("select name from tempdb.dbo.sysobjects where name ='##LLPBabc'")
If rsTemp.RecordCount > 0 Then
cnn.Execute ("Delete from ##LLPBabc")
Else
cnn.Execute ("create table ##LLPBabc(WADOCO nvarchar(8), DRDL01 nvarchar(30) ,WADCTO nvarchar(2) ,WAWR03 char(3),DCG datetime, WASRST char(2), UORG01 float, SOQS01 float , SOCN01 float)")
End If

'JDE資料暫存數據庫
Set rs = Cnn2.Execute("select name from tempdb.dbo.sysobjects where name ='##LLPBabcd'")
If rs.RecordCount > 0 Then
Cnn2.Execute ("Delete from ##LLPBabcd")
Else
Cnn2.Execute ("create table ##LLPBabcd(WADOCO nvarchar(8), DRDL01 nvarchar(30) ,WADCTO nvarchar(2) ,WAWR03 char(3),DCG datetime, WASRST char(2), UORG01 float, SOQS01 float , SOCN01 float)")
End If
Strdate = Date - 1
Label18.Caption = Label18.Caption & Strdate

If strA <> "" Then
strabcd = strabcd & " and " & "WADCTO = '" & strA & "'"
End If
If strB <> "" Then
strabcd = strabcd & " and " & "WADOCO = '" & strB & "'"
End If
If strC <> "" Then
strabcd = strabcd & " and " & "WASRST = '" & strC & "'"
End If
If strD <> "" Then
strabcd = strabcd & " and " & "WAWR03 = '" & strD & "'"
End If


'獲取暫存數據庫資料
strRST_SQL = " select WADOCO,DRDL01,WADCTO,WAWR03,DCG,WASRST, sum(UORG) as UORG,sum(SOQS) as SOQS ,sum(SOCN) as SOCN" & _
" from WorkOrderF4801 inner join UserDefinedCodesF0005 on WorkOrderF4801.WAWR03=UserDefinedCodesF0005.DRKY and DRSY='00' and DRRT='W3' " & strabcd & " group by WADOCO,WADCTO,WAWR03,DCG,WASRST,DRDL01"
Set rs = Cnn2.Execute(strRST_SQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
strRST_SQL = " insert into ##LLPBabcd (WADOCO,DRDL01,WADCTO,WAWR03,DCG,WASRST,UORG01,SOQS01,SOCN01) values('" & Trim(rs.Fields("WADOCO").Value) & "','" & Trim(rs.Fields("DRDL01").Value) & "','" & Trim(rs.Fields("WADCTO").Value) & "','" & Trim(rs.Fields("WAWR03").Value) & "','" & Trim(rs.Fields("DCG").Value) & "'," & Trim(rs.Fields("WASRST").Value) & ",'" & Trim(rs.Fields("UORG").Value) & "','" & Trim(rs.Fields("SOQS").Value) & "','" & Trim(rs.Fields("SOCN").Value) & "') "
Cnn2.Execute strRST_SQL
rs.MoveNext
Loop
End If

---問題所在
GetRptdata Ado, "select WADOCO,DRDL01,WADCTO,WAWR03,DCG,WASRST,UORG01,SOQS01,SOCN01,sum(pb009)as pb009,sum(pb010)as pb010,sum(pb011)as pb011 from vwmJAPB " & _
"inner join ##LLPBabc on vwmJAPB.pb001=##LLPBabc.WADCTO Collate Chinese_Taiwan_Stroke_CI_AS and vwmJAPB.pb003=##LLPBabc.WADOCO Collate Chinese_Taiwan_Stroke_CI_AS group by WADOCO,DRDL01,WADCTO,WAWR03,DCG,WASRST,UORG01,SOQS01,SOCN01 " & _
" order by WADOCO,WADCTO,WAWR03 ", cnn

End Sub

因為是2個數據庫，抓取的JDE資料已經放入到暫存數據庫臨時表##LLPBabcd中，後面DAO需要與本地數據庫連接vwmJAPB，不可能實現。
所以我希望將暫存在##LLPBabcd到進本地數據中，
不知道有什麽辦法可以自動把暫存數據庫中的臨時表##LLPBabcd的信息導入到本地數據庫臨時表##LLPBabc中來。

[kate_sun]

我要在代碼里實現，有辦法沒？

[kate_sun]

剛才思考下，我既然已經獲取了數據是否可以直接將數據存放到本地數據庫中去。我就在存儲過程的地方實現。剛才測試結果：
strRST_SQL = " select WADOCO,DRDL01,WADCTO,WAWR03,DCG,WASRST, sum(UORG) as UORG,sum(SOQS) as SOQS ,sum(SOCN) as SOCN" & _
" from WorkOrderF4801 inner join UserDefinedCodesF0005 on WorkOrderF4801.WAWR03=UserDefinedCodesF0005.DRKY and DRSY='00' and DRRT='W3' " & strabcd & " group by WADOCO,WADCTO,WAWR03,DCG,WASRST,DRDL01"
Set rs = Cnn2.Execute(strRST_SQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
strRST_SQL = " insert into ##LLPBabcd (WADOCO,DRDL01,WADCTO,WAWR03,DCG,WASRST,UORG01,SOQS01,SOCN01) values('" & Trim(rs.Fields("WADOCO").Value) & "','" & Trim(rs.Fields("DRDL01").Value) & "','" & Trim(rs.Fields("WADCTO").Value) & "','" & Trim(rs.Fields("WAWR03").Value) & "','" & Trim(rs.Fields("DCG").Value) & "'," & Trim(rs.Fields("WASRST").Value) & ",'" & Trim(rs.Fields("UORG").Value) & "','" & Trim(rs.Fields("SOQS").Value) & "','" & Trim(rs.Fields("SOCN").Value) & "') "
Cnn2.Execute strRST_SQL（更改為Cnn.Execute strRST_SQL）
rs.MoveNext
Loop
End If
運行OK.
想法成立！

[kate_sun]

本地數據庫
cnnStr = "Provider=SQLOLEDB.1;Password=sh123;Persist Security Info=True;User ID=mgfsh;Initial Catalog=SHON;Data Source=10.160.48.16"
JDE資料存放數據庫
Cnn2.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=tool;Password=tool123;Data Source=BIAP\BISQL;"

[jhone99]

两个数据库建两个连接就可以了，不用这么费劲

[kate_sun]

我也不想這費勁，你說的2個兩個數據庫連接如何實現？我看網上有說用
select ##LLPBabcd.WADOCO,##LLPBabcd.DRDL01,##LLPBabcd.WADCTO,##LLPBabcd.WAWR03,
##LLPBabcd.DCG,##LLPBabcd.WASRST,##LLPBabcd.UORG,##LLPBabcd.SOQS,
##LLPBabcd.SOCN into from ##LLPBabc
但是不同數據庫之間這怎么能實現

[贝隆]

我也觉得，建两个数据库，建两个数据库连接比较简便。