Private Sub cmdGetData_Click()
' Get inventory data
Dim rsInventory As New ADODB.Recordset
Dim stmInventory As New ADODB.Stream
' set db connection
conDB.ConnectionString = ("Provider=SQLOLEDB.1;Server=Pinatubo;User ID=training; PWD=password;Initial Catalog=XMLTraining;")
conDB.Open
' set recordset
rsInventory.Open "tblInventory", conDB, adOpenDynamic, adLockOptimistic
rsInventory.Save stmInventory, adPersistXML
' Save ADO XML to file
stmInventory.SaveToFile App.Path & "\inventory.xml", adSaveCreateOverWrite
' dispaly xml file to UI
wbXML.Navigate App.Path & "\inventory.xml"
' close database connection
conDB.Close
End Sub
Private Sub cmdGetDetail_Click()
' Get product detail
Dim rsProductDetail As New ADODB.Recordset
Dim stmProduct As New ADODB.Stream
' set recordset
rsProductDetail.Open "SELECT *, tblProductDetail.* FROM tblInventory INNER JOIN " & _
"tblProductDetail ON tblInventory.fldProductID = tblProductDetail." & _
"fldProductID", conDB, adOpenDynamic, adPersistXML
rsProductDetail.Save stmProduct, adPersistXML
' Save ADO XML file to disk
stmProduct.SaveToFile App.Path & "\ProductDetail.xml", adSaveCreateOverWrite
' Load xml document in UI
wbXML.Navigate App.Path & "\ProductDetail.xml"
' Close database connection string
conDB.Close
End Sub
你可以使用VB的ADO对对象来搞定。
你首先要在VB菜单中:
“工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library”
注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的
示例:
dim cn as new adodb.Connection
dim rs as new adodb.recordset
dim sqlstr as string
cn.open "Provider=SQLOLEDB;Driver={SQL Server};Server=Server;Uid=sa" & _
";Pwd=1;Database=data"
rs.cursorlocation=aduseclient
rs.open "select * from XXX",cn,3,3
rs.save "d:\mydata\data.rst",adPersistXML'保存
rs.close
rs.open "d:\mydata\data.rst",cn,AdOpenDynamic,AdLockBatchOptimistic,adCmdFile'打开
msgbox "共有:" & rs.recordcount & "条记录!!!"
rs.close
set rs=nothing
cn.close
set cn=nothing
Private Sub Command1_Click()
Dim strmResults As New ADODB.Stream
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cnstr As String
cnstr = "Provider = SQLOLEDB.1;Password=121231;Persist Security Info=True;User ID=sa;Initial Catalog=zydb;Data Source=wzsswz"
cn.ConnectionString = cnstr
cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = "select * from company for XML auto"
strmResults.Open
cmd.Properties("Output Stream").Value = strmResults
cmd.Properties("xml root") = "root"
cmd.Execute , , adExecuteStream
strmResults.Position = 0
strmResults.SaveToFile App.Path & "\outfile.xml"
strmResults.Close
Set strmResults = Nothing
End Sub