dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 14 of 14

Thread: Combining multiple Recordsets programmatically (can't use union join)

  1. #1
    David Guest

    Combining multiple Recordsets programmatically (can't use union join)


    Is there a way to combine the results of multiple recordsets into one?
    Such as taking the results of RS1 and the results of rs2 (both resultsets
    have the same data structure) and combine all the records into a rs3

    I don't want to use any physical tables and append data from each rs. I want
    to do this all programmatically in memory.. that way i can then take the
    rs3 recordset and have it be my master recordsource....
    (Can't use union join because of it's 10 union limitation)

    Thanks

  2. #2
    Michael Culley Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)

    > (Can't use union join because of it's 10 union limitation)

    Can you create sub queries?

    --
    Michael Culley
    www.vbdotcom.com



    "David" <david@cdsconsulting.mailbox.as> wrote in message
    news:3cb1d4a7$1@10.1.10.29...
    >
    > Is there a way to combine the results of multiple recordsets into one?
    > Such as taking the results of RS1 and the results of rs2 (both resultsets
    > have the same data structure) and combine all the records into a rs3
    >
    > I don't want to use any physical tables and append data from each rs. I

    want
    > to do this all programmatically in memory.. that way i can then take the
    > rs3 recordset and have it be my master recordsource....
    > (Can't use union join because of it's 10 union limitation)
    >
    > Thanks




  3. #3
    David Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    Sub-queries? No, this is in VB6, not Access. No physical tables or databases
    allowed to exist in the program, all recordsets have to be created in memory.
    Data is being extracted from between 4 and 16 different Visual Fox Pro Tables

    "Michael Culley" <mike@vbdotcom.com> wrote:
    >> (Can't use union join because of it's 10 union limitation)

    >
    >Can you create sub queries?
    >
    >--
    >Michael Culley
    >www.vbdotcom.com
    >
    >
    >
    >"David" <david@cdsconsulting.mailbox.as> wrote in message
    >news:3cb1d4a7$1@10.1.10.29...
    >>
    >> Is there a way to combine the results of multiple recordsets into one?
    >> Such as taking the results of RS1 and the results of rs2 (both resultsets
    >> have the same data structure) and combine all the records into a rs3
    >>
    >> I don't want to use any physical tables and append data from each rs.

    I
    >want
    >> to do this all programmatically in memory.. that way i can then take the
    >> rs3 recordset and have it be my master recordsource....
    >> (Can't use union join because of it's 10 union limitation)
    >>
    >> Thanks

    >
    >



  4. #4
    Chris Hylton Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    You can just create an empty recordset (no fields and no records) and use
    one of the populated recordsets to generate the fields along w/ their datatypes
    (in a loop) using the .fields collection...then in another loop (through
    the records), perform a record by record add from one rs to the other.

    Only problem w/ this is that you are now using a memory generated recordset
    that is not connected to a data source and doesn't have all the properties
    set that associate it to a data source...consequently, AFAIK you won't be
    able to connect it up to a datasource easily to use the .Update method to
    get data back into the database...if you are only using this as a 'read-only'
    recordset, it should work fine.

    I would think there would be a way w/ the SQL to condense the number of unions,
    but if you don't think that's possible...this solution might work for you...but
    may be a little slow if you are dealing w/ thousands of records...

    Chris

    "David" <david@cdsconsulting.mailbox.as> wrote:
    >
    >Sub-queries? No, this is in VB6, not Access. No physical tables or databases
    >allowed to exist in the program, all recordsets have to be created in memory.
    >Data is being extracted from between 4 and 16 different Visual Fox Pro Tables
    >
    >"Michael Culley" <mike@vbdotcom.com> wrote:
    >>> (Can't use union join because of it's 10 union limitation)

    >>
    >>Can you create sub queries?
    >>
    >>--
    >>Michael Culley
    >>www.vbdotcom.com
    >>
    >>
    >>
    >>"David" <david@cdsconsulting.mailbox.as> wrote in message
    >>news:3cb1d4a7$1@10.1.10.29...
    >>>
    >>> Is there a way to combine the results of multiple recordsets into one?
    >>> Such as taking the results of RS1 and the results of rs2 (both resultsets
    >>> have the same data structure) and combine all the records into a rs3
    >>>
    >>> I don't want to use any physical tables and append data from each rs.

    >I
    >>want
    >>> to do this all programmatically in memory.. that way i can then take

    the
    >>> rs3 recordset and have it be my master recordsource....
    >>> (Can't use union join because of it's 10 union limitation)
    >>>
    >>> Thanks

    >>
    >>

    >



  5. #5
    Michael Culley Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)

    > AFAIK you won't be
    > able to connect it up to a datasource easily to use the .Update


    Updates are not going to be possible with a union anyway.

    --
    Michael Culley
    www.vbdotcom.com



    "Chris Hylton" <cchylton@hotmail.com> wrote in message
    news:3cb32c4b$1@10.1.10.29...
    >
    > You can just create an empty recordset (no fields and no records) and use
    > one of the populated recordsets to generate the fields along w/ their

    datatypes
    > (in a loop) using the .fields collection...then in another loop (through
    > the records), perform a record by record add from one rs to the other.
    >
    > Only problem w/ this is that you are now using a memory generated

    recordset
    > that is not connected to a data source and doesn't have all the properties
    > set that associate it to a data source...consequently, AFAIK you won't be
    > able to connect it up to a datasource easily to use the .Update method to
    > get data back into the database...if you are only using this as a

    'read-only'
    > recordset, it should work fine.
    >
    > I would think there would be a way w/ the SQL to condense the number of

    unions,
    > but if you don't think that's possible...this solution might work for

    you...but
    > may be a little slow if you are dealing w/ thousands of records...
    >
    > Chris
    >
    > "David" <david@cdsconsulting.mailbox.as> wrote:
    > >
    > >Sub-queries? No, this is in VB6, not Access. No physical tables or

    databases
    > >allowed to exist in the program, all recordsets have to be created in

    memory.
    > >Data is being extracted from between 4 and 16 different Visual Fox Pro

    Tables
    > >
    > >"Michael Culley" <mike@vbdotcom.com> wrote:
    > >>> (Can't use union join because of it's 10 union limitation)
    > >>
    > >>Can you create sub queries?
    > >>
    > >>--
    > >>Michael Culley
    > >>www.vbdotcom.com
    > >>
    > >>
    > >>
    > >>"David" <david@cdsconsulting.mailbox.as> wrote in message
    > >>news:3cb1d4a7$1@10.1.10.29...
    > >>>
    > >>> Is there a way to combine the results of multiple recordsets into one?
    > >>> Such as taking the results of RS1 and the results of rs2 (both

    resultsets
    > >>> have the same data structure) and combine all the records into a rs3
    > >>>
    > >>> I don't want to use any physical tables and append data from each rs.

    > >I
    > >>want
    > >>> to do this all programmatically in memory.. that way i can then take

    > the
    > >>> rs3 recordset and have it be my master recordsource....
    > >>> (Can't use union join because of it's 10 union limitation)
    > >>>
    > >>> Thanks
    > >>
    > >>

    > >

    >




  6. #6
    Michael Culley Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)

    > Sub-queries? No, this is in VB6, not Access. No physical tables or
    databases
    > allowed to exist in the program, all recordsets have to be created in

    memory.
    > Data is being extracted from between 4 and 16 different Visual Fox Pro

    Tables

    I'm a little confused here. You say you cannot use databases or connected
    recordsets but you are using VFP database. I've never used a VFP database
    but I'm sure it would be possible to created queries in it and use these
    queries from VB.

    --
    Michael Culley
    www.vbdotcom.com






  7. #7
    David Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    Updates aren't needed...

    "Michael Culley" <mike@vbdotcom.com> wrote:
    >> AFAIK you won't be
    >> able to connect it up to a datasource easily to use the .Update

    >
    >Updates are not going to be possible with a union anyway.
    >
    >--
    >Michael Culley
    >www.vbdotcom.com
    >
    >
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote in message
    >news:3cb32c4b$1@10.1.10.29...
    >>
    >> You can just create an empty recordset (no fields and no records) and

    use
    >> one of the populated recordsets to generate the fields along w/ their

    >datatypes
    >> (in a loop) using the .fields collection...then in another loop (through
    >> the records), perform a record by record add from one rs to the other.
    >>
    >> Only problem w/ this is that you are now using a memory generated

    >recordset
    >> that is not connected to a data source and doesn't have all the properties
    >> set that associate it to a data source...consequently, AFAIK you won't

    be
    >> able to connect it up to a datasource easily to use the .Update method

    to
    >> get data back into the database...if you are only using this as a

    >'read-only'
    >> recordset, it should work fine.
    >>
    >> I would think there would be a way w/ the SQL to condense the number of

    >unions,
    >> but if you don't think that's possible...this solution might work for

    >you...but
    >> may be a little slow if you are dealing w/ thousands of records...
    >>
    >> Chris
    >>
    >> "David" <david@cdsconsulting.mailbox.as> wrote:
    >> >
    >> >Sub-queries? No, this is in VB6, not Access. No physical tables or

    >databases
    >> >allowed to exist in the program, all recordsets have to be created in

    >memory.
    >> >Data is being extracted from between 4 and 16 different Visual Fox Pro

    >Tables
    >> >
    >> >"Michael Culley" <mike@vbdotcom.com> wrote:
    >> >>> (Can't use union join because of it's 10 union limitation)
    >> >>
    >> >>Can you create sub queries?
    >> >>
    >> >>--
    >> >>Michael Culley
    >> >>www.vbdotcom.com
    >> >>
    >> >>
    >> >>
    >> >>"David" <david@cdsconsulting.mailbox.as> wrote in message
    >> >>news:3cb1d4a7$1@10.1.10.29...
    >> >>>
    >> >>> Is there a way to combine the results of multiple recordsets into

    one?
    >> >>> Such as taking the results of RS1 and the results of rs2 (both

    >resultsets
    >> >>> have the same data structure) and combine all the records into a rs3
    >> >>>
    >> >>> I don't want to use any physical tables and append data from each

    rs.
    >> >I
    >> >>want
    >> >>> to do this all programmatically in memory.. that way i can then take

    >> the
    >> >>> rs3 recordset and have it be my master recordsource....
    >> >>> (Can't use union join because of it's 10 union limitation)
    >> >>>
    >> >>> Thanks
    >> >>
    >> >>
    >> >

    >>

    >
    >



  8. #8
    David Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    Ok I'll try to explain... I have approx 20 foxpro tables.. (if this data were
    on a SQL server my life would be uneventful) they all contain the same type
    of data, the only reason there are so many is because FoxPro has a 2GB per
    table limitation.. so when a table has reached its limit they start filling
    a new one... so basically it's one big mass of data split into several tables...
    this data serves as the data for our reports. My goal is to query the data
    by one user defined criteria variable and a date range... well to get the
    data needed I have to query each of these tables... A union join with a where
    clause is Ideal and works perfect .. except Union joins are limited to 10
    unions so if I have to use more than 10 tables then I have to find another
    way. The data extracted by the query will simply serve as a datasource for
    a report. My restrictions are that I cannot have the program use temporary
    physical tables to store data.. I cannot create any new objects in FoxPro
    to help so FoxPro queries are also out. so basically my program needs to
    query this huge lump of spread out data and serve a datareport based on the
    resultset.... Like I said if all this data were sitting on a SQL Server in
    one table, I'd be done now...
    I think I've come up with a workaround for the time being but I'd still appreciate
    any black-belt programmer suggestions for this kind of goofy thing....
    Thanks for the effort tho Mike, I do appreciate it

    David

    "Michael Culley" <mike@vbdotcom.com> wrote:
    >> Sub-queries? No, this is in VB6, not Access. No physical tables or

    >databases
    >> allowed to exist in the program, all recordsets have to be created in

    >memory.
    >> Data is being extracted from between 4 and 16 different Visual Fox Pro

    >Tables
    >
    >I'm a little confused here. You say you cannot use databases or connected
    >recordsets but you are using VFP database. I've never used a VFP database
    >but I'm sure it would be possible to created queries in it and use these
    >queries from VB.
    >
    >--
    >Michael Culley
    >www.vbdotcom.com
    >
    >
    >
    >
    >



  9. #9
    Michael Culley Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)

    > of data, the only reason there are so many is because FoxPro has a 2GB per
    > table limitation.. so when a table has reached its limit they start

    filling
    > a new one...


    All I can say is YIKES!

    > I cannot create any new objects in FoxPro
    > to help so FoxPro queries are also out.


    Why is this?

    > I think I've come up with a workaround for the time being but I'd still

    appreciate

    What report designer are you using. I know I could come up with a simple
    workaround for this in active reports real easy. I can explain the method I
    would use for active reports and you may be able to apply it to whatever you
    are using:

    Active reports can create unbound reports, they work just like bound reports
    but they get the data from an event instead of from a recordset. Code is
    something like this:

    Sub Report_DataInitialize
    Me.Fields.Add "Code"
    Me.Fields.Add "Node"
    End Sub

    sub Report_DataFetch (EOF as Boolean)
    Fields("Code").Value = rs!Code
    Fields("Name").Value = rs!Name
    rs.Movenext
    EOF = rs.EOF
    end sub

    In your case I would just add in some code that when the recordset became
    EOF I would reopen it for the next table and continue on. As far as the
    report was concerned it would be the same recordset

    What report designer are you using?

    --
    Michael Culley
    www.vbdotcom.com



  10. #10
    Michael Culley Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)

    > There are about 250 people that will access this application from a shared
    > network folder. Meaning this app won't be distributed as a setup package,
    > it has to be compiled to run as is from the network. I am not allowed to
    > have anything installed onto the users PC's.


    You have just described a browser app Seriously, maybe this is an option.

    > So I cannot use any 3rd party
    > controls or .dlls, etc. that I would have to register on a users PC.


    I presume the VB runtime files are installed?

    > Like I said I've managed a workaround that seems to work. I had to resort
    > to using 1 temp access database with 1 table. I use it as a template to

    fill
    > my temporary recordset (name of temp file is based on user name and time
    > that report was kicked off), when the report is closed it destroys the

    temp
    > file. My worry is the amount of users using this and the amount of temp

    files
    > created simultaneously, if it will have a performance drag or not... we

    will
    > see...


    If you put the access DB on the client's machine then there will be no extra
    load placed on the server.

    --
    Michael Culley
    www.vbdotcom.com



    "David" <david@cdsconsulting.mailbox.as> wrote in message
    news:3cb3a0cd$1@10.1.10.29...
    >
    > Why no objects you ask.. Just part of the requirements I was given. I am

    not
    > allowed to make any changes to any existing FoxPro objects nor add any of
    > my own to the network.
    > There are about 250 people that will access this application from a shared
    > network folder. Meaning this app won't be distributed as a setup package,
    > it has to be compiled to run as is from the network. I am not allowed to
    > have anything installed onto the users PC's. So I cannot use any 3rd party
    > controls or .dlls, etc. that I would have to register on a users PC.
    > I've managed a simple workaround for now that I think will work...
    > I'm using the datareport designer that comes with VB, I'd prefer to use

    something
    > more robust such as Crystal, which I do use for other projects but cannot
    > with this one because of the requirement restrictions. Using Crystal would
    > mean having to install a .dll on to the users' pc.
    > Like I said I've managed a workaround that seems to work. I had to resort
    > to using 1 temp access database with 1 table. I use it as a template to

    fill
    > my temporary recordset (name of temp file is based on user name and time
    > that report was kicked off), when the report is closed it destroys the

    temp
    > file. My worry is the amount of users using this and the amount of temp

    files
    > created simultaneously, if it will have a performance drag or not... we

    will
    > see...
    >
    > Thanks
    > D-
    >
    > "Michael Culley" <mike@vbdotcom.com> wrote:
    > >> of data, the only reason there are so many is because FoxPro has a 2GB

    > per
    > >> table limitation.. so when a table has reached its limit they start

    > >filling
    > >> a new one...

    > >
    > >All I can say is YIKES!
    > >
    > >> I cannot create any new objects in FoxPro
    > >> to help so FoxPro queries are also out.

    > >
    > >Why is this?
    > >
    > >> I think I've come up with a workaround for the time being but I'd still

    > >appreciate
    > >
    > >What report designer are you using. I know I could come up with a simple
    > >workaround for this in active reports real easy. I can explain the method

    > I
    > >would use for active reports and you may be able to apply it to whatever

    > you
    > >are using:
    > >
    > >Active reports can create unbound reports, they work just like bound

    reports
    > >but they get the data from an event instead of from a recordset. Code is
    > >something like this:
    > >
    > >Sub Report_DataInitialize
    > > Me.Fields.Add "Code"
    > > Me.Fields.Add "Node"
    > >End Sub
    > >
    > >sub Report_DataFetch (EOF as Boolean)
    > > Fields("Code").Value = rs!Code
    > > Fields("Name").Value = rs!Name
    > > rs.Movenext
    > > EOF = rs.EOF
    > >end sub
    > >
    > >In your case I would just add in some code that when the recordset became
    > >EOF I would reopen it for the next table and continue on. As far as the
    > >report was concerned it would be the same recordset
    > >
    > >What report designer are you using?
    > >
    > >--
    > >Michael Culley
    > >www.vbdotcom.com
    > >
    > >

    >




  11. #11
    David Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    Why no objects you ask.. Just part of the requirements I was given. I am not
    allowed to make any changes to any existing FoxPro objects nor add any of
    my own to the network.
    There are about 250 people that will access this application from a shared
    network folder. Meaning this app won't be distributed as a setup package,
    it has to be compiled to run as is from the network. I am not allowed to
    have anything installed onto the users PC's. So I cannot use any 3rd party
    controls or .dlls, etc. that I would have to register on a users PC.
    I've managed a simple workaround for now that I think will work...
    I'm using the datareport designer that comes with VB, I'd prefer to use something
    more robust such as Crystal, which I do use for other projects but cannot
    with this one because of the requirement restrictions. Using Crystal would
    mean having to install a .dll on to the users' pc.
    Like I said I've managed a workaround that seems to work. I had to resort
    to using 1 temp access database with 1 table. I use it as a template to fill
    my temporary recordset (name of temp file is based on user name and time
    that report was kicked off), when the report is closed it destroys the temp
    file. My worry is the amount of users using this and the amount of temp files
    created simultaneously, if it will have a performance drag or not... we will
    see...

    Thanks
    D-

    "Michael Culley" <mike@vbdotcom.com> wrote:
    >> of data, the only reason there are so many is because FoxPro has a 2GB

    per
    >> table limitation.. so when a table has reached its limit they start

    >filling
    >> a new one...

    >
    >All I can say is YIKES!
    >
    >> I cannot create any new objects in FoxPro
    >> to help so FoxPro queries are also out.

    >
    >Why is this?
    >
    >> I think I've come up with a workaround for the time being but I'd still

    >appreciate
    >
    >What report designer are you using. I know I could come up with a simple
    >workaround for this in active reports real easy. I can explain the method

    I
    >would use for active reports and you may be able to apply it to whatever

    you
    >are using:
    >
    >Active reports can create unbound reports, they work just like bound reports
    >but they get the data from an event instead of from a recordset. Code is
    >something like this:
    >
    >Sub Report_DataInitialize
    > Me.Fields.Add "Code"
    > Me.Fields.Add "Node"
    >End Sub
    >
    >sub Report_DataFetch (EOF as Boolean)
    > Fields("Code").Value = rs!Code
    > Fields("Name").Value = rs!Name
    > rs.Movenext
    > EOF = rs.EOF
    >end sub
    >
    >In your case I would just add in some code that when the recordset became
    >EOF I would reopen it for the next table and continue on. As far as the
    >report was concerned it would be the same recordset
    >
    >What report designer are you using?
    >
    >--
    >Michael Culley
    >www.vbdotcom.com
    >
    >



  12. #12
    David Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    IIS and ASP is something I've been screaming about for quite awhile but it's
    not an option. We don't have an internal intranet. all the runtime files
    are installed on users machines as part of the image when the hardware guys
    build their systems. After that, no additions or changes to any software
    is allowed (temp db can't be put on users drive). This is a pretty locked
    down place.. hence no browsing ability.

    "Michael Culley" <mike@vbdotcom.com> wrote:
    >> There are about 250 people that will access this application from a shared
    >> network folder. Meaning this app won't be distributed as a setup package,
    >> it has to be compiled to run as is from the network. I am not allowed

    to
    >> have anything installed onto the users PC's.

    >
    >You have just described a browser app Seriously, maybe this is an option.
    >
    >> So I cannot use any 3rd party
    >> controls or .dlls, etc. that I would have to register on a users PC.

    >
    >I presume the VB runtime files are installed?
    >
    >> Like I said I've managed a workaround that seems to work. I had to resort
    >> to using 1 temp access database with 1 table. I use it as a template to

    >fill
    >> my temporary recordset (name of temp file is based on user name and time
    >> that report was kicked off), when the report is closed it destroys the

    >temp
    >> file. My worry is the amount of users using this and the amount of temp

    >files
    >> created simultaneously, if it will have a performance drag or not... we

    >will
    >> see...

    >
    >If you put the access DB on the client's machine then there will be no extra
    >load placed on the server.
    >
    >--
    >Michael Culley
    >www.vbdotcom.com
    >
    >
    >
    >"David" <david@cdsconsulting.mailbox.as> wrote in message
    >news:3cb3a0cd$1@10.1.10.29...
    >>
    >> Why no objects you ask.. Just part of the requirements I was given. I

    am
    >not
    >> allowed to make any changes to any existing FoxPro objects nor add any

    of
    >> my own to the network.
    >> There are about 250 people that will access this application from a shared
    >> network folder. Meaning this app won't be distributed as a setup package,
    >> it has to be compiled to run as is from the network. I am not allowed

    to
    >> have anything installed onto the users PC's. So I cannot use any 3rd party
    >> controls or .dlls, etc. that I would have to register on a users PC.
    >> I've managed a simple workaround for now that I think will work...
    >> I'm using the datareport designer that comes with VB, I'd prefer to use

    >something
    >> more robust such as Crystal, which I do use for other projects but cannot
    >> with this one because of the requirement restrictions. Using Crystal would
    >> mean having to install a .dll on to the users' pc.
    >> Like I said I've managed a workaround that seems to work. I had to resort
    >> to using 1 temp access database with 1 table. I use it as a template to

    >fill
    >> my temporary recordset (name of temp file is based on user name and time
    >> that report was kicked off), when the report is closed it destroys the

    >temp
    >> file. My worry is the amount of users using this and the amount of temp

    >files
    >> created simultaneously, if it will have a performance drag or not... we

    >will
    >> see...
    >>
    >> Thanks
    >> D-
    >>
    >> "Michael Culley" <mike@vbdotcom.com> wrote:
    >> >> of data, the only reason there are so many is because FoxPro has a

    2GB
    >> per
    >> >> table limitation.. so when a table has reached its limit they start
    >> >filling
    >> >> a new one...
    >> >
    >> >All I can say is YIKES!
    >> >
    >> >> I cannot create any new objects in FoxPro
    >> >> to help so FoxPro queries are also out.
    >> >
    >> >Why is this?
    >> >
    >> >> I think I've come up with a workaround for the time being but I'd still
    >> >appreciate
    >> >
    >> >What report designer are you using. I know I could come up with a simple
    >> >workaround for this in active reports real easy. I can explain the method

    >> I
    >> >would use for active reports and you may be able to apply it to whatever

    >> you
    >> >are using:
    >> >
    >> >Active reports can create unbound reports, they work just like bound

    >reports
    >> >but they get the data from an event instead of from a recordset. Code

    is
    >> >something like this:
    >> >
    >> >Sub Report_DataInitialize
    >> > Me.Fields.Add "Code"
    >> > Me.Fields.Add "Node"
    >> >End Sub
    >> >
    >> >sub Report_DataFetch (EOF as Boolean)
    >> > Fields("Code").Value = rs!Code
    >> > Fields("Name").Value = rs!Name
    >> > rs.Movenext
    >> > EOF = rs.EOF
    >> >end sub
    >> >
    >> >In your case I would just add in some code that when the recordset became
    >> >EOF I would reopen it for the next table and continue on. As far as the
    >> >report was concerned it would be the same recordset
    >> >
    >> >What report designer are you using?
    >> >
    >> >--
    >> >Michael Culley
    >> >www.vbdotcom.com
    >> >
    >> >

    >>

    >
    >



  13. #13
    Chris Hylton Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    I have to agree w/ the 'yikes' statement made above, this is a pretty big
    pickle IMHO...given your requirements...if you are dealing w/ 2gb foxpro
    tables and more than 10 of them...my idea of a recordset is OUT, it would
    take forever to loop through tons of records in multiple recordsets and dump
    them all to one recordset using the add methods...don't even bother w/ that
    solution that I suggested...

    You mentioned that you can't use new FOXPRO objects...but what about 'temporary'
    Access objects...meaning, you could (via code) create an Access database,
    using ADOX or DAO, and add a table w/ the fields you need...then, using ADOX
    or DAO again, programatically create 'linked' tables to the FOXPRO data in
    the MDB...

    You could even link the FOXPRO tables first, and use the first FOXPRO table
    w/ a Make Table query to generate the storage table in Access...then subsequent
    Append Table queries to append the other tables...

    then, just blow the Access database away when it's no longer needed...personally,
    I'd just leave it out there and reuse it when I needed to...

    That's about all I can think of, save moving to a new database platform,
    which doesn't sound like an option given your requirements/limitations...

    Good luck,
    Chris

    "David" <david@cdsconsulting.mailbox.as> wrote:
    >
    >IIS and ASP is something I've been screaming about for quite awhile but

    it's
    >not an option. We don't have an internal intranet. all the runtime files
    >are installed on users machines as part of the image when the hardware guys
    >build their systems. After that, no additions or changes to any software
    >is allowed (temp db can't be put on users drive). This is a pretty locked
    >down place.. hence no browsing ability.
    >
    >"Michael Culley" <mike@vbdotcom.com> wrote:
    >>> There are about 250 people that will access this application from a shared
    >>> network folder. Meaning this app won't be distributed as a setup package,
    >>> it has to be compiled to run as is from the network. I am not allowed

    >to
    >>> have anything installed onto the users PC's.

    >>
    >>You have just described a browser app Seriously, maybe this is an option.
    >>
    >>> So I cannot use any 3rd party
    >>> controls or .dlls, etc. that I would have to register on a users PC.

    >>
    >>I presume the VB runtime files are installed?
    >>
    >>> Like I said I've managed a workaround that seems to work. I had to resort
    >>> to using 1 temp access database with 1 table. I use it as a template

    to
    >>fill
    >>> my temporary recordset (name of temp file is based on user name and time
    >>> that report was kicked off), when the report is closed it destroys the

    >>temp
    >>> file. My worry is the amount of users using this and the amount of temp

    >>files
    >>> created simultaneously, if it will have a performance drag or not...

    we
    >>will
    >>> see...

    >>
    >>If you put the access DB on the client's machine then there will be no

    extra
    >>load placed on the server.
    >>
    >>--
    >>Michael Culley
    >>www.vbdotcom.com
    >>
    >>
    >>
    >>"David" <david@cdsconsulting.mailbox.as> wrote in message
    >>news:3cb3a0cd$1@10.1.10.29...
    >>>
    >>> Why no objects you ask.. Just part of the requirements I was given. I

    >am
    >>not
    >>> allowed to make any changes to any existing FoxPro objects nor add any

    >of
    >>> my own to the network.
    >>> There are about 250 people that will access this application from a shared
    >>> network folder. Meaning this app won't be distributed as a setup package,
    >>> it has to be compiled to run as is from the network. I am not allowed

    >to
    >>> have anything installed onto the users PC's. So I cannot use any 3rd

    party
    >>> controls or .dlls, etc. that I would have to register on a users PC.
    >>> I've managed a simple workaround for now that I think will work...
    >>> I'm using the datareport designer that comes with VB, I'd prefer to use

    >>something
    >>> more robust such as Crystal, which I do use for other projects but cannot
    >>> with this one because of the requirement restrictions. Using Crystal

    would
    >>> mean having to install a .dll on to the users' pc.
    >>> Like I said I've managed a workaround that seems to work. I had to resort
    >>> to using 1 temp access database with 1 table. I use it as a template

    to
    >>fill
    >>> my temporary recordset (name of temp file is based on user name and time
    >>> that report was kicked off), when the report is closed it destroys the

    >>temp
    >>> file. My worry is the amount of users using this and the amount of temp

    >>files
    >>> created simultaneously, if it will have a performance drag or not...

    we
    >>will
    >>> see...
    >>>
    >>> Thanks
    >>> D-
    >>>
    >>> "Michael Culley" <mike@vbdotcom.com> wrote:
    >>> >> of data, the only reason there are so many is because FoxPro has a

    >2GB
    >>> per
    >>> >> table limitation.. so when a table has reached its limit they start
    >>> >filling
    >>> >> a new one...
    >>> >
    >>> >All I can say is YIKES!
    >>> >
    >>> >> I cannot create any new objects in FoxPro
    >>> >> to help so FoxPro queries are also out.
    >>> >
    >>> >Why is this?
    >>> >
    >>> >> I think I've come up with a workaround for the time being but I'd

    still
    >>> >appreciate
    >>> >
    >>> >What report designer are you using. I know I could come up with a simple
    >>> >workaround for this in active reports real easy. I can explain the method
    >>> I
    >>> >would use for active reports and you may be able to apply it to whatever
    >>> you
    >>> >are using:
    >>> >
    >>> >Active reports can create unbound reports, they work just like bound

    >>reports
    >>> >but they get the data from an event instead of from a recordset. Code

    >is
    >>> >something like this:
    >>> >
    >>> >Sub Report_DataInitialize
    >>> > Me.Fields.Add "Code"
    >>> > Me.Fields.Add "Node"
    >>> >End Sub
    >>> >
    >>> >sub Report_DataFetch (EOF as Boolean)
    >>> > Fields("Code").Value = rs!Code
    >>> > Fields("Name").Value = rs!Name
    >>> > rs.Movenext
    >>> > EOF = rs.EOF
    >>> >end sub
    >>> >
    >>> >In your case I would just add in some code that when the recordset became
    >>> >EOF I would reopen it for the next table and continue on. As far as

    the
    >>> >report was concerned it would be the same recordset
    >>> >
    >>> >What report designer are you using?
    >>> >
    >>> >--
    >>> >Michael Culley
    >>> >www.vbdotcom.com
    >>> >
    >>> >
    >>>

    >>
    >>

    >



  14. #14
    David Guest

    Re: Combining multiple Recordsets programmatically (can't use union join)


    Thanks Chris, I just heard today that they are going to revamp our datawarehouse
    before the sale of our company (withing the next few months) so since I'm
    the only SQL Certified employee i'll be the one to play with it most likely.
    What I did end up doing is using a temporary access database to store the
    results of resultsets (I'm running multiple SQL Union Join queries - each
    no more than 10 tables) once it has finished the Union loops and filled my
    recordset I use it to populate the report and then display it on screen.
    when the report is closed it wipes out the temp database. I also thought
    about the linking idea you brought up but Access' engine is much slower than
    FoxPro's and wouldn't be able to handle that huge amount of records at once
    esp when it's going to be kicked off many times a day per user (250 users)
    SO I think this will work until I throw all those FoxPro tables onto SQL
    Server.

    Thanks for the help guys.
    D-

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >I have to agree w/ the 'yikes' statement made above, this is a pretty big
    >pickle IMHO...given your requirements...if you are dealing w/ 2gb foxpro
    >tables and more than 10 of them...my idea of a recordset is OUT, it would
    >take forever to loop through tons of records in multiple recordsets and

    dump
    >them all to one recordset using the add methods...don't even bother w/ that
    >solution that I suggested...
    >
    >You mentioned that you can't use new FOXPRO objects...but what about 'temporary'
    >Access objects...meaning, you could (via code) create an Access database,
    >using ADOX or DAO, and add a table w/ the fields you need...then, using

    ADOX
    >or DAO again, programatically create 'linked' tables to the FOXPRO data

    in
    >the MDB...
    >
    >You could even link the FOXPRO tables first, and use the first FOXPRO table
    >w/ a Make Table query to generate the storage table in Access...then subsequent
    >Append Table queries to append the other tables...
    >
    >then, just blow the Access database away when it's no longer needed...personally,
    >I'd just leave it out there and reuse it when I needed to...
    >
    >That's about all I can think of, save moving to a new database platform,
    >which doesn't sound like an option given your requirements/limitations...
    >
    >Good luck,
    >Chris
    >
    >"David" <david@cdsconsulting.mailbox.as> wrote:
    >>
    >>IIS and ASP is something I've been screaming about for quite awhile but

    >it's
    >>not an option. We don't have an internal intranet. all the runtime files
    >>are installed on users machines as part of the image when the hardware

    guys
    >>build their systems. After that, no additions or changes to any software
    >>is allowed (temp db can't be put on users drive). This is a pretty locked
    >>down place.. hence no browsing ability.
    >>
    >>"Michael Culley" <mike@vbdotcom.com> wrote:
    >>>> There are about 250 people that will access this application from a

    shared
    >>>> network folder. Meaning this app won't be distributed as a setup package,
    >>>> it has to be compiled to run as is from the network. I am not allowed

    >>to
    >>>> have anything installed onto the users PC's.
    >>>
    >>>You have just described a browser app Seriously, maybe this is an option.
    >>>
    >>>> So I cannot use any 3rd party
    >>>> controls or .dlls, etc. that I would have to register on a users PC.
    >>>
    >>>I presume the VB runtime files are installed?
    >>>
    >>>> Like I said I've managed a workaround that seems to work. I had to resort
    >>>> to using 1 temp access database with 1 table. I use it as a template

    >to
    >>>fill
    >>>> my temporary recordset (name of temp file is based on user name and

    time
    >>>> that report was kicked off), when the report is closed it destroys the
    >>>temp
    >>>> file. My worry is the amount of users using this and the amount of temp
    >>>files
    >>>> created simultaneously, if it will have a performance drag or not...

    >we
    >>>will
    >>>> see...
    >>>
    >>>If you put the access DB on the client's machine then there will be no

    >extra
    >>>load placed on the server.
    >>>
    >>>--
    >>>Michael Culley
    >>>www.vbdotcom.com
    >>>
    >>>
    >>>
    >>>"David" <david@cdsconsulting.mailbox.as> wrote in message
    >>>news:3cb3a0cd$1@10.1.10.29...
    >>>>
    >>>> Why no objects you ask.. Just part of the requirements I was given.

    I
    >>am
    >>>not
    >>>> allowed to make any changes to any existing FoxPro objects nor add any

    >>of
    >>>> my own to the network.
    >>>> There are about 250 people that will access this application from a

    shared
    >>>> network folder. Meaning this app won't be distributed as a setup package,
    >>>> it has to be compiled to run as is from the network. I am not allowed

    >>to
    >>>> have anything installed onto the users PC's. So I cannot use any 3rd

    >party
    >>>> controls or .dlls, etc. that I would have to register on a users PC.
    >>>> I've managed a simple workaround for now that I think will work...
    >>>> I'm using the datareport designer that comes with VB, I'd prefer to

    use
    >>>something
    >>>> more robust such as Crystal, which I do use for other projects but cannot
    >>>> with this one because of the requirement restrictions. Using Crystal

    >would
    >>>> mean having to install a .dll on to the users' pc.
    >>>> Like I said I've managed a workaround that seems to work. I had to resort
    >>>> to using 1 temp access database with 1 table. I use it as a template

    >to
    >>>fill
    >>>> my temporary recordset (name of temp file is based on user name and

    time
    >>>> that report was kicked off), when the report is closed it destroys the
    >>>temp
    >>>> file. My worry is the amount of users using this and the amount of temp
    >>>files
    >>>> created simultaneously, if it will have a performance drag or not...

    >we
    >>>will
    >>>> see...
    >>>>
    >>>> Thanks
    >>>> D-
    >>>>
    >>>> "Michael Culley" <mike@vbdotcom.com> wrote:
    >>>> >> of data, the only reason there are so many is because FoxPro has

    a
    >>2GB
    >>>> per
    >>>> >> table limitation.. so when a table has reached its limit they start
    >>>> >filling
    >>>> >> a new one...
    >>>> >
    >>>> >All I can say is YIKES!
    >>>> >
    >>>> >> I cannot create any new objects in FoxPro
    >>>> >> to help so FoxPro queries are also out.
    >>>> >
    >>>> >Why is this?
    >>>> >
    >>>> >> I think I've come up with a workaround for the time being but I'd

    >still
    >>>> >appreciate
    >>>> >
    >>>> >What report designer are you using. I know I could come up with a simple
    >>>> >workaround for this in active reports real easy. I can explain the

    method
    >>>> I
    >>>> >would use for active reports and you may be able to apply it to whatever
    >>>> you
    >>>> >are using:
    >>>> >
    >>>> >Active reports can create unbound reports, they work just like bound
    >>>reports
    >>>> >but they get the data from an event instead of from a recordset. Code

    >>is
    >>>> >something like this:
    >>>> >
    >>>> >Sub Report_DataInitialize
    >>>> > Me.Fields.Add "Code"
    >>>> > Me.Fields.Add "Node"
    >>>> >End Sub
    >>>> >
    >>>> >sub Report_DataFetch (EOF as Boolean)
    >>>> > Fields("Code").Value = rs!Code
    >>>> > Fields("Name").Value = rs!Name
    >>>> > rs.Movenext
    >>>> > EOF = rs.EOF
    >>>> >end sub
    >>>> >
    >>>> >In your case I would just add in some code that when the recordset

    became
    >>>> >EOF I would reopen it for the next table and continue on. As far as

    >the
    >>>> >report was concerned it would be the same recordset
    >>>> >
    >>>> >What report designer are you using?
    >>>> >
    >>>> >--
    >>>> >Michael Culley
    >>>> >www.vbdotcom.com
    >>>> >
    >>>> >
    >>>>
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center