Microsoft KB Archive/249012: Difference between revisions
m (Text replacement - "&" to "&") |
m (Text replacement - """ to """) |
||
Line 104: | Line 104: | ||
Dim cn As ADODB.Connection, rs As ADODB.Recordset, rs2 As ADODB.Recordset | Dim cn As ADODB.Connection, rs As ADODB.Recordset, rs2 As ADODB.Recordset | ||
Set cn = New ADODB.Connection | Set cn = New ADODB.Connection | ||
cn.Open | cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB" | ||
Set rs = New ADODB.Recordset | Set rs = New ADODB.Recordset | ||
rs.CursorLocation = adUseClient | rs.CursorLocation = adUseClient | ||
rs.Open | rs.Open "SHAPE {SELECT * FROM Employees WHERE EmployeeID < 5} AS Employees " & _ | ||
"APPEND ({SELECT * FROM Orders} RELATE EmployeeID TO EmployeeID)", _ | |||
cn, adOpenStatic, adLockReadOnly, adCmdText | cn, adOpenStatic, adLockReadOnly, adCmdText | ||
Print_Records rs | Print_Records rs | ||
Set rs2 = New ADODB.Recordset | Set rs2 = New ADODB.Recordset | ||
rs2.Open | rs2.Open "SHAPE Employees", cn, adOpenStatic, adLockReadOnly, adCmdText | ||
Print_Records rs2 | Print_Records rs2 | ||
rs.Close | rs.Close | ||
Line 119: | Line 119: | ||
Private Sub Print_Records(rs As ADODB.Recordset) | Private Sub Print_Records(rs As ADODB.Recordset) | ||
Debug.Print | Debug.Print "Recordset has "; rs.RecordCount; " records." | ||
rs.MoveFirst | rs.MoveFirst | ||
Do While Not rs.EOF | Do While Not rs.EOF | ||
Line 141: | Line 141: | ||
</pre></li> | </pre></li> | ||
<li><p>In order to return all of the employees to the second recordset, change the second Shape statement to:</p> | <li><p>In order to return all of the employees to the second recordset, change the second Shape statement to:</p> | ||
<pre class="codesample"> rs2.Open | <pre class="codesample"> rs2.Open "SHAPE {SELECT * FROM Employees}", cn, adOpenStatic, adLockReadOnly, adCmdText | ||
</pre></li> | </pre></li> | ||
<li><p>Re-run the application. The following output is produced:</p> | <li><p>Re-run the application. The following output is produced:</p> |
Latest revision as of 13:51, 21 July 2020
Article ID: 249012
Article Last Modified on 5/8/2003
APPLIES TO
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 1
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.1 Service Pack 1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
This article was previously published under Q249012
SYMPTOMS
When you open two recordsets with the Msdatashape provider, the second recordset has the incorrect number of records.
CAUSE
You may be inadvertently re-shaping the data in the first recordset.
RESOLUTION
Change the second SHAPE statement to be within curly brackets { }.
STATUS
This behavior is by design.
MORE INFORMATION
The Msdatashape provider, that ships with Microsoft Data Access Components 2.1 and later, allows the results of a previous Shape statement to be re-shaped. This is often useful when you want to see all the child records at once without having to access them through the parent records.
To re-shape a recordset, specify the Alias name in the new statement without using curly brackets.
Steps to Reproduce Behavior
- Using Visual Basic 5.0 or 6.0, create a new Standard EXE project.
- Use the Project and References menu to add a reference to the following type library:
Microsoft ActiveX Data Objects 2.1 Library
Add a Command button (Command1) and the following code to the default form:
Option Explicit Private Sub Command1_Click() Dim cn As ADODB.Connection, rs As ADODB.Recordset, rs2 As ADODB.Recordset Set cn = New ADODB.Connection cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB" Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open "SHAPE {SELECT * FROM Employees WHERE EmployeeID < 5} AS Employees " & _ "APPEND ({SELECT * FROM Orders} RELATE EmployeeID TO EmployeeID)", _ cn, adOpenStatic, adLockReadOnly, adCmdText Print_Records rs Set rs2 = New ADODB.Recordset rs2.Open "SHAPE Employees", cn, adOpenStatic, adLockReadOnly, adCmdText Print_Records rs2 rs.Close rs2.Close End Sub Private Sub Print_Records(rs As ADODB.Recordset) Debug.Print "Recordset has "; rs.RecordCount; " records." rs.MoveFirst Do While Not rs.EOF Debug.Print rs(0), rs(1), rs(2) rs.MoveNext Loop End Sub
NOTE: You may have to change the Connect string to correctly point to the Nwind.mdb file.
Run the application and click Command. You see the following results:
Recordset has 4 records. 1 Davolio Nancy 2 Fuller Andrew 3 Leverling Janet 4 Peacock Margaret Recordset has 4 records. 1 Davolio Nancy 2 Fuller Andrew 3 Leverling Janet 4 Peacock Margaret
In order to return all of the employees to the second recordset, change the second Shape statement to:
rs2.Open "SHAPE {SELECT * FROM Employees}", cn, adOpenStatic, adLockReadOnly, adCmdText
Re-run the application. The following output is produced:
Recordset has 4 records. 1 Davolio Nancy 2 Fuller Andrew 3 Leverling Janet 4 Peacock Margaret Recordset has 9 records. 1 Davolio Nancy 2 Fuller Andrew 3 Leverling Janet 4 Peacock Margaret 5 Buchanan Steven 6 Suyama Michael 7 King Robert 8 Callahan Laura 9 Dodsworth Anne
Keywords: kbdatabase kbprb KB249012