• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Utilize the MsDataShape Provider for multi-dimension recordsets. Examples of two or more linked rec

Utilize the MsDataShape Provider for multi-dimension recordsets. Examples of two or more linked rec

Utilize the MsDataShape Provider for multi-dimension recordsets. Examples of two or more linked recordsets.

API Declarations






Rate Utilize the MsDataShape Provider for multi-dimension recordsets. Examples of two or more linked rec

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

    Set DCON = New ADODB.Connection
    Set DRS = New ADODB.Recordset
    
    DCON.CursorLocation = adUseClient
    DCON.CommandTimeout = 15
    DCON.ConnectionTimeout = 15
    DCON.Mode = adModeReadWrite
    'More than likely you'll have to create a new connection string
    'But the key factor in this string is:
    '"Provider=MSDataShape;Data Provider=MSDASQL;"

    DCON.Open "Provider=MSDataShape;Data Provider=MSDASQL;" & _
    "Persist Security Info=False;Extended Properties=" & _
    "'Description=SQL;DRIVER=SQL Server;SERVER=MyServer;" & _
    "WSID=MyID;DATABASE=Northwind;Trusted_Connection=Yes';" & _
    "Initial Catalog=Northwind"
    
    DRS.MaxRecords = 500 'limit # or records
    DRS.CursorLocation = adUseClient
    DRS.CursorType = adOpenStatic
    DRS.LockType = adLockOptimistic
    
    Dim SQLstr1, SQLstr2, SQLstr3 As String
    
    SQLstr1 = "Your first SQL string"
    SQLstr2 = "Your second SQL string"

    'two recordsets
    DRS.Open "SHAPE " & SQLstr1 & " " & _
        "APPEND (" & SQLstr2 & " " & _
        "AS DRSChild1 " & _
        "RELATE KeyField1 TO KeyField2)", DCON
    'NOTE: the 2 recordsets must be related by a common field,
    'these fields would follow the relate statement

    'OR

    'three or more recordsets

    SQLstr3 = "Your third SQL string"

    DRS.Open "SHAPE " & SQLstr1 & " " & _
            "APPEND (" & SQLstr2 & " " & _
            "RELATE KeyField1 TO KeyField2) AS DRSChild1, " & _
            "(" & SQLstr3 & " " & _
            "RELATE KeyField1 TO KeyField3) AS DRSChild2", DCON