by Big Al (4 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 5th January 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Utilize the MsDataShape Provider for multi-dimension recordsets
API Declarations
VBcoders
Set DRS = CreateObject("ADODB.Recordset")
DCON.CursorLocation = adUseClient
DCON.CommandTimeout = 15
DCON.ConnectionTimeout = 15
DCON.Mode = adModeReadWrite
DCON.Open "Provider=MSDataShape;Data Provider=MSDASQL;Persist Security Info=False;Extended Properties='Description=SQL;DRIVER=SQL Server;SERVER=MyServer;WSID=MyComputer;DATABASE=Northwind;Trusted_Connection=Yes';Initial Catalog=NorthWind"
DRS.MaxRecords = 500
DRS.CursorLocation = adUseClient
DRS.CursorType = adOpenStatic
DRS.LockType = adLockOptimistic
'SHAPE {SELECT * FROM "dbo"."Client"} AS Command1 APPEND ({SELECT * FROM "dbo"."LossHistory"} AS Command2 RELATE 'ClientID' TO 'LClientID') AS Command2,({SELECT * FROM "dbo"."LossHistory"} AS Command3 RELATE 'ClientID' TO 'LossID') AS Command3
Dim SQLstr1, SQLstr2, SQLstr3 As String
Dim SingleShape As Boolean
MultiShape = False
SQLstr1 = "{SELECT TOP 100 PERCENT COUNT(ClientID) AS ClientCount, LEFT(CAST(EntryDate AS varchar), 11) AS DateEntered From dbo.Client WHERE (EntryDate BETWEEN CONVERT(DATETIME, '2002-04-01 00:00:01', 102) AND CONVERT(DATETIME, '2002-04-28 23:59:01', 102)) GROUP BY LEFT(CAST(EntryDate AS varchar), 11) ORDER BY LEFT(CAST(EntryDate AS varchar), 11)}"
SQLstr2 = "{SELECT TOP 100 PERCENT COUNT(ClientID) AS ClientCount, LEFT(CAST(EntryDate AS varchar), 11) AS DateEntered, QuoteDecline AS Quote From dbo.Client WHERE (EntryDate BETWEEN CONVERT(DATETIME, '2002-04-01 00:00:01', 102) AND CONVERT(DATETIME, '2002-04-28 23:59:01', 102)) GROUP BY LEFT(CAST(EntryDate AS varchar), 11), QuoteDecline ORDER BY LEFT(CAST(EntryDate AS varchar), 11)}"
SQLstr3 = "{SELECT TOP 100 PERCENT COUNT(ClientID) AS ClientCount, LEFT(CAST(EntryDate AS varchar), 11) AS DateEntered, QuoteDecline AS Decline From dbo.Client WHERE (EntryDate BETWEEN CONVERT(DATETIME, '2002-04-01 00:00:01', 102) AND CONVERT(DATETIME, '2002-04-28 23:59:01', 102)) GROUP BY LEFT(CAST(EntryDate AS varchar), 11), QuoteDecline ORDER BY LEFT(CAST(EntryDate AS varchar), 11)}"
If MultiShape Then
DRS.Open "SHAPE " & SQLstr1 & " " & _
"APPEND (" & SQLstr2 & " " & _
"AS DRSChild1 " & _
"RELATE DateEntered TO DateEntered)", DCON
Else
DRS.Open "SHAPE " & SQLstr1 & " " & _
"APPEND (" & SQLstr2 & " " & _
"RELATE DateEntered TO DateEntered) AS DRSChild1, " & _
"(" & SQLstr3 & " " & _
"RELATE DateEntered TO DateEntered) AS DRSChild2", DCON
End If
'DRS.Open "SHAPE {SELECT TOP 100 PERCENT COUNT(ClientID) AS ClientCount, LEFT(CAST(EntryDate AS varchar), 11) AS DateEntered From dbo.Client WHERE (EntryDate BETWEEN CONVERT(DATETIME, '2002-04-01 00:00:01', 102) AND CONVERT(DATETIME, '2002-04-28 23:59:01', 102)) GROUP BY LEFT(CAST(EntryDate AS varchar), 11) ORDER BY LEFT(CAST(EntryDate AS varchar), 11)} " & _
"APPEND ({SELECT TOP 100 PERCENT COUNT(ClientID) AS ClientCount, LEFT(CAST(EntryDate AS varchar), 11) AS DateEntered, QuoteDecline From dbo.Client WHERE (EntryDate BETWEEN CONVERT(DATETIME, '2002-04-01 00:00:01', 102) AND CONVERT(DATETIME, '2002-04-28 23:59:01', 102)) GROUP BY LEFT(CAST(EntryDate AS varchar), 11), QuoteDecline ORDER BY LEFT(CAST(EntryDate AS varchar), 11)} " & _
"AS DRSChild " & _
"RELATE DateEntered TO DateEntered)", DCON
No comments have been posted about Utilize the MsDataShape Provider for multi-dimension recordsets. Why not be the first to post a comment about Utilize the MsDataShape Provider for multi-dimension recordsets.