MSHFlexGrid - using 2 different providers - union query

[LadyLin]

Is is possible to create an ADO recordset from tables in 2 different databases (1 a SQL DB accessed through sqlole.db.1 and 1 a non-SQL db accessed through MSDASQL.1)?
I want to do a union query on the tables which have the same structure. I want to load the data into a flexgrid.

 

[Andrzejek]

No takers for your problem, ha?

If I would have this problem, I would set the temporary table 'on the fly' in one of the databases, let's say in SQL, and move my data from non-SQL DB.

Now I have my data in one DB. I would do my query the easy way, get my results, and drop my temp table in SQL.

Done.

But that's me. Others may have more 'elegant' way

--- Andy

 

[LadyLin]

Thanks for the suggestion but I'm not allowed to create in the SQL database. For now I'm using 2 flexgrids and letting the user toggle between them. Not what I wanted but since the older data is in a "legacy" type data base, the dual grids will go away eventually. I'll keep your suggestion in mind for the future, Thanks again.

 

[strongm]

What is the legacy DB?

 

[LadyLin]

It was a custom designed DB accessed through Liant's Relativity Driver ODBC connection.

 

[ettienne]

Create a TEMP DB in Access, same principal.

 

[fredericofonseca]

can you create the link to the Liant driver (COBOL fiels by the way) as a linked server on the SQL Server? I havent tried this yet but if you tell me which driver version you are using I maay be able to try it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JerryKlmns]

Could you load the resultset form SQL db to the flexgrid and then load the other resultset from the non-SQL db?
Both resultsets have the same structure, so they are treated the same way for the flexgrid. But this is a 2 steps work to do if you need an update on data!
This means that you use the flexgrid as an unbound object!

 

[LadyLin]

Thanks for the suggestions...

Frederico, the driver version is 2.52.53.29, very old as this DB has been around for some time.

Jerry, this is the code I use to link the recordset to the flexgrid

oRS.Open strSQL, _
       ConnectString(gstrConnType), _
       adOpenForwardOnly, _
       adLockReadOnly, _
       adCmdText
Set fgOpen.DataSource = oRS
oRS.Close
Set oRS = Nothing

I've never tried one as an unbound object but the suggestion seems like a good one, I'm going to research that. Any hints on how to start?

 

[JerryKlmns]

A fast copy paste 'cause I have to run. I'll see you tomorrow!

Dim rstGetParalaves As ADODB.Recordset

Set rstGetParalaves = New ADODB.Recordset
With rstGetParalaves
    .ActiveConnection = Cnn '3
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = "SELECT Par.Diaxiristis, CID, CL93.Name, Bank, Center, Main, CL93.NrAxiografouSys, NrAxiografouPar, Banks.Name, Amount, PayDateAxiografou, ParOn, P123, IssuerAFM, IssuerName " & _
                "FROM (Paralaves As Par INNER JOIN CL93 " & _
                "ON Par.NrAxiografouSys = CL93.NrAxiografouSys) " & _
                "INNER JOIN Banks " & _
                "ON CL93.PayeeBank = Banks.Bank_ID " & _
                "WHERE ((Par.User Is Not Null) " & _
                "AND (Par.PorX=1) AND (UserSendTo Is Null)) " & _
                "ORDER BY Bank, Center, CID, Main, PayeeBank;"
    .Open
End With

With FlexP_Eur
    .Clear
    .Width = 11500
    .WordWrap = True
    .AllowUserResizing = flexResizeColumns
    .AllowBigSelection = False
    .SelectionMode = flexSelectionFree
    .Cols = 16
    .Rows = rstGetParalaves.RecordCount + 1
    .RowHeight(0) = 720
    .ColWidth(0) = 0
    .ColWidth(1) = 1000
    .ColWidth(2) = 825
    .ColWidth(3) = 3270
    .ColWidth(4) = 810
    .ColWidth(5) = 750
    .ColWidth(6) = 1305
    .ColWidth(7) = 1140
    .ColWidth(8) = 975
    .ColWidth(9) = 1035
    .ColWidth(10) = 1065
    .ColWidth(11) = 1020
    .ColWidth(12) = 1020
    .ColWidth(13) = 570
    .ColWidth(14) = 960
    .ColWidth(15) = 3270
    .Row = 0
    .Col = 1
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Äéá÷åéñ."
    .Col = 2
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "CID"
    .Col = 3
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Ðéóôïý÷ïò"
    .Col = 4
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "ÔñÜðåæá"
    .Col = 5
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Êáô/ìá"
    .Col = 6
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Ëïãáñéáóìüò"
    .Col = 7
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Áñ. ÁîéïãñÜöïõ ÓõóôÞìáôïò"
    .Col = 8
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Íïýìåñï ÅðéôáãÞò"
    .Col = 9
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "ÔñÜðåæá Åêäüóåùò"
    .Col = 10
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Ðïóïý"
    .Col = 11
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "ËÞîåùò"
    .Col = 12
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "ÅðéóôñïöÞ"
    .Col = 13
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "P123"
    .Col = 14
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "ÁÖÌ Åêäüôç"
    .Col = 15
    .CellAlignment = flexAlignCenterCenter
    .CellFontBold = True
    .Text = "Åêäüôçò"
    While Not rstGetParalaves.EOF
        .Row = rstGetParalaves.AbsolutePosition
        .Col = 1
        .Text = rstGetParalaves.Fields("Diaxiristis")
        .Col = 2
        .Text = rstGetParalaves.Fields("CID")
        .Col = 3
        .Text = rstGetParalaves.Fields("CL93.Name")
        .Col = 4
        .Text = Right("000" & rstGetParalaves.Fields("Bank"), 4)
        .Col = 5
        .Text = Right("000" & rstGetParalaves.Fields("Center"), 4)
        .Col = 6
        .Text = Right("00" & rstGetParalaves.Fields("Main"), 10)
        .Col = 7
        .Text = rstGetParalaves.Fields("NrAxiografouSys")
        .Col = 8
        .Text = rstGetParalaves.Fields("NrAxiografouPar")
        .Col = 9
        .Text = rstGetParalaves.Fields("Banks.Name")
        .Col = 10
        .Text = Format(rstGetParalaves.Fields("Amount"), "#,##0.00")
        .Col = 11
        .Text = Format(rstGetParalaves.Fields("PayDateAxiografou"), "dd/mm/yyyy")
        .Col = 12
        .Text = Format(rstGetParalaves.Fields("ParOn"), "dd/mm/yyyy")
        .Col = 13
        .Text = rstGetParalaves.Fields("P123") & ""
        .Col = 14
        .Text = rstGetParalaves.Fields("IssuerAFM")
        .Col = 15
        .Text = rstGetParalaves.Fields("IssuerName")
        rstGetParalaves.MoveNext
    Wend
    If .Rows > 1 Then
        .Row = 1
    Else
        cmdFind.Enabled = False
        cmdDiaxirisi.Enabled = False
'        cmdClose.SetFocus
    End If
    .Col = 7
End With
If Dir("c:\Temp\rstDiaxirisiP.adtg") <> "" Then Kill ("c:\Temp\rstDiaxirisiP.adtg")
With rstGetParalaves
    If FlexP_Eur.Rows > 1 Then
        .MoveFirst
        .Save "c:\Temp\rstDiaxirisiP.adtg", adPersistADTG
    End If
    .Close
End With
Set rstGetParalaves = Nothing