问：怎么把Sqlserver 的数据转换并输出成Xml格式文件？
解决方法:
SQL Server7的用法
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
2000的用select * from company for XML auto就可以了
Option Explicit
Dim conDB As New ADODB.Connection
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 connection string
conDB.ConnectionString = ("Provider=SQLOLEDB.1;Server=Pinatubo;User ID=training; PWD=password;Initial Catalog=XMLTraining;")
conDB.Open
' 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
在.NET中就方面多了 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
2000的用select * from company for XML auto就可以了
Option Explicit
Dim conDB As New ADODB.Connection
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 connection string
conDB.ConnectionString = ("Provider=SQLOLEDB.1;Server=Pinatubo;User ID=training; PWD=password;Initial Catalog=XMLTraining;")
conDB.Open
' 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
select * from table for xml auto
问：如何终止一个被其它进程打开的Access文件？
解决方法： Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Const SW_SHOWNORMAL = 1
Const WM_CLOSE = &H10
Const gcClassnameMSWord = "OpusApp"
Const gcClassnameMSExcel = "XLMAIN"
Const gcClassnameMSIExplorer = "IEFrame"
Const gcClassnameMSVBasic = "wndclass_desked_gsk"
Const gcClassnameNotePad = "Notepad"
Const gcClassnameMyVBApp = "ThunderForm"
Private Sub Form_Load()
Dim WinWnd As Long, Ret As String, RetVal As Long, lpClassName As String
'Search the window
WinWnd = FindWindow(vbNullString, "Microsoft Access")
If WinWnd = 0 Then MsgBox "Couldn't find the window ...": Exit Sub
'Show the window
ShowWindow WinWnd, SW_SHOWNORMAL
'Post a message to the window to close itself
PostMessage WinWnd, WM_CLOSE, 0&, 0&
End Sub
