使用shape语句; SHAPE {SELECT au_id, au_lname, au_fname FROM authors} APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS WHERE TA.title_id = TS.title_id} AS title_chap RELATE au_id TO au_id)
' 声明ADODB.Connection和ADODB.Recordset对象变量 Dim conn As ADODB.Connection Dim rs As New ADODB.Recordset
Private Sub Command1_Click() Dim sql As String ' 实例化Connection 对象 Set conn = New ADODB.Connection ' 设置连接提供者名称 conn.Provider = "MSDataShape" ' 设置连接字符串 conn.ConnectionString = "Provider=MSDataShape;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb; Data Provider=Microsoft.Jet.OLEDB.4.0" ' 打开到数据源的连接 conn.Open 'shape指令 sql = "SHAPE {SELECT * FROM Orders} AS Orders " + _ "APPEND ({SELECT * FROM [Order Details]} AS detail " + _ "RELATE 'OrderID' TO 'OrderID') AS detail" ' 在分级 Recordset对象中当父行位置更改时,对基本子记录的引用是也更改。 rs.StayInSync = True ' 打开游标 rs.Open sql, conn ' 设置MSHFlexGrid控件数据源 Set MSHFlexGrid1.DataSource = rs End Sub
' 释放相应的资源 Private Sub Command2_Click() rs.Close Set rs = Nothing conn.Close Set conn = Nothing End End Sub
使用数据定形服务.private mcnn as new adodb.connection private mrst as new adodb.recordset private sub form_load() '指定提供者和连接字符串. mcnn.open "provider=MSDataShape;data provider=SQLOLEDB.1;server=(local);user id=sa;initial catalog=Northwind" mrst.open "SHAPE {SELECT CUSTOMERID,COMPANYNAME FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS } RELATE CUSTOMERID TO CUSTOMERID)",mcnn set mshflexgrid.datasource=mrst end sub
Ado分层结构一般这样实现: (cnNorthwind是Connection对象,rsCustomers和rsOrders是Recordset对象)cnNorthwind.Provider="MsDataShape" cnNorthwind.Properties("Data Provider")="SQLOLEDB" cnNorthwind.Properties("Data Source")="ScepHome" cnNorthwind.Properties("Initial Catalog")="Northind"strSQL="SHAPE (SELECT * FROM Customers) AS Customers " & _ "APPEND ({SELECT * FROM Orders} AS Orders " & _ "RELATE CustomerID TO CustomerID) As Orders"rsCustomers.Open strSQL,cnNorthwind,adOpenStatic,adLockBatchOptimistic,adCmdText Set rsOrders=rsCustomers.Fields("Orders").Value(参考代码,请先初始化对象)
SHAPE {SELECT au_id, au_lname, au_fname FROM authors}
APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS
WHERE TA.title_id = TS.title_id}
AS title_chap RELATE au_id TO au_id)
我要构造的SQL语句还是比较复杂的。
多谢了
' 声明ADODB.Connection和ADODB.Recordset对象变量
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub Command1_Click()
Dim sql As String
' 实例化Connection 对象
Set conn = New ADODB.Connection
' 设置连接提供者名称
conn.Provider = "MSDataShape"
' 设置连接字符串
conn.ConnectionString = "Provider=MSDataShape;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb; Data Provider=Microsoft.Jet.OLEDB.4.0"
' 打开到数据源的连接
conn.Open
'shape指令
sql = "SHAPE {SELECT * FROM Orders} AS Orders " + _
"APPEND ({SELECT * FROM [Order Details]} AS detail " + _
"RELATE 'OrderID' TO 'OrderID') AS detail"
' 在分级 Recordset对象中当父行位置更改时,对基本子记录的引用是也更改。
rs.StayInSync = True
' 打开游标
rs.Open sql, conn
' 设置MSHFlexGrid控件数据源
Set MSHFlexGrid1.DataSource = rs
End Sub
' 释放相应的资源
Private Sub Command2_Click()
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End
End Sub
private mrst as new adodb.recordset
private sub form_load()
'指定提供者和连接字符串.
mcnn.open "provider=MSDataShape;data provider=SQLOLEDB.1;server=(local);user id=sa;initial catalog=Northwind"
mrst.open "SHAPE {SELECT CUSTOMERID,COMPANYNAME FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS } RELATE CUSTOMERID TO CUSTOMERID)",mcnn
set mshflexgrid.datasource=mrst
end sub
(cnNorthwind是Connection对象,rsCustomers和rsOrders是Recordset对象)cnNorthwind.Provider="MsDataShape"
cnNorthwind.Properties("Data Provider")="SQLOLEDB"
cnNorthwind.Properties("Data Source")="ScepHome"
cnNorthwind.Properties("Initial Catalog")="Northind"strSQL="SHAPE (SELECT * FROM Customers) AS Customers " & _
"APPEND ({SELECT * FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID) As Orders"rsCustomers.Open strSQL,cnNorthwind,adOpenStatic,adLockBatchOptimistic,adCmdText
Set rsOrders=rsCustomers.Fields("Orders").Value(参考代码,请先初始化对象)