# Visual Basic > Visual Basic 6 and Earlier >  SQL query to link tables with same structures using vb6 and ADODB connection?

## chiuchimu

I have several Ms Access tables all with the same structure; TA, TB ,TC, TD ...
I would like to query the tables so they seem as one table without actually appending the tables together.   The tables can be in the same Ms Access database or separate database.

----------


## gibra

You may use *UNION* :



```
SELECT field1, field2, ...
FROM TA

UNION

SELECT field1, field2, ...
FROM TB

UNION

SELECT field1, field2, ...
FROM TC

UNION

SELECT field1, field2, ...
FROM TD
```

----------


## techgnome

If they all have the same structure, why are they in different tables?

Acceptable answers include: "I don't know. I inherited this mess, the guy before me was an idiot. I'm just making the best of it."
Will also accept "I was that idiot." 
Will not accept: "Each table represents a [year/object/other aspect]" 
May be willing to accept: "I didn't know any better, what can I change to make it better?"


-tg

----------


## DataMiser

I agree does not make sense to have multiple tables with the same structure and union them together. Would be better to have one table.

----------


## dilettante

I've seen applications where at the end of each (fiscal, calendar, etc.) year the past year data gets rolled out into another database for "historical" use.

Most of the "historical" stuff is management-related ad hoc reporting.  Sometimes they want to do multi-year what-iffing on the data so they UNION together linked tables.  Often their tool of choice is (gag) Excel!

See Analysis Services & Power Pivot Blog: Linked Tables.  And yes, it gets boring fast.  :Wink:

----------


## chiuchimu

> If they all have the same structure, why are they in different tables?
> 
> Acceptable answers include: "I don't know. I inherited this mess, the guy before me was an idiot. I'm just making the best of it."
> Will also accept "I was that idiot." 
> Will not accept: "Each table represents a [year/object/other aspect]" 
> May be willing to accept: "I didn't know any better, what can I change to make it better?"
> 
> 
> -tg





> I agree does not make sense to have multiple tables with the same structure and union them together. Would be better to have one table.


If you can tell me how maybe I'll tell you why. :Big Grin: 

@gibra
Thanks, I'll try it.

----------


## techgnome

All it takes is one more field... tells you the type....

consider this: the app I work on has people in it, as well as groups, companies, organizations and other such elements. Rather than having 4 different tables (people, groups, organizations, companies) it's in one table... then we have flags & other elements that tell us what that record type is. 

for our accounting data, we attach a Fiscal Month to every transaction, every Fiscal Month is then tied to a Fiscal Year. Each Fiscal Year is then tied to a General Ledger - you can have more than one... but all of the transactions regardless of their FM, FY or GL are all in the same table, there's no segregation. We don't throw FY20013 into one table and FY2014 into another... that would make reporting a nightmare. So all transactions are in the GL table, with a FY and a FM on it, along with the GL ID.

-tg

----------


## confeder8

> If they all have the same structure, why are they in different tables?
> 
> Acceptable answers include: "I don't know. I inherited this mess, the guy before me was an idiot. I'm just making the best of it."
> Will also accept "I was that idiot." 
> Will not accept: "Each table represents a [year/object/other aspect]" 
> May be willing to accept: "I didn't know any better, what can I change to make it better?"
> 
> 
> -tg


Have you ever handled 2.5 Lacs data, otherwise you have not asked this silly question.. so one has to have splitted database to handle large data.

----------


## confeder8

> I agree does not make sense to have multiple tables with the same structure and union them together. Would be better to have one table.


Have you ever handled 2.5 Lacs data, otherwise you have not asked this silly question.. so one has to have splitted database to handle large data.

----------


## DataMiser

> Have you ever handled 2.5 Lacs data, otherwise you have not asked this silly question.. so one has to have splitted database to handle large data.


What would you consider large data?  I have worked with databases that have over a million  sizable records in a table. No problem.

----------


## vb6forever

I'll come to chiuchimu defense.
A good example of separate tables with the same structure is stock market data.   While one could have a symbol designating
each separate row, it makes more sense to have separate tables.  My2Cents

----------


## ChrisE

> I'll come to chiuchimu defense.
> A good example of separate tables with the same structure is stock market data.   While one could have a symbol designating
> each separate row, it makes more sense to have separate tables.  My2Cents


I doubt if seperate Tables is an option, just use a Filter on the table



```
Sub ADOFilterStockRecordset()

   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\Stock.mdb;"

   ' Open the recordset
   rst.Open "StockData", cnn, adOpenKeyset, adLockOptimistic

   ' Filter the recordset to include only those in
   ' the USA that have Sales > 100
   rst.Filter = "Country='USA' And Sales > 100"
   Debug.Print rst.Fields("CustomerId").Value

   ' Close the recordset
   rst.Close


End Sub
```

----------


## techgnome

> Have you ever handled 2.5 Lacs data, otherwise you have not asked this silly question.. so one has to have splitted database to handle large data.


I have worked with financial systems... both with a structure were things were in different tables.... it's BS. It's horrible. And wring reports on that structure is a nightmare. The b est day at work was when they finally restructured it into  a proper accounts set of tables.  As for sicze ... we'd process thousands of transactions A DAY. Hundreds of thousansa A MONTH. MILLIONS in a year .... and thats one client. We have thousands of clients, and decades worth of Accounting data .... it's never been a problem. 




> I'll come to chiuchimu defense.
> A good example of separate tables with the same structure is stock market data.   While one could have a symbol designating
> each separate row, it makes more sense to have separate tables.  My2Cents


In what world? Not in this known universe. There's no reason for that. You think they hve different tables for posts here? No... there;s ONE table. One. IF you put all your stock nfo in different tables based on the ticker name ..... how in the world do you write a report - or even a query - that gets you the top 5 positive performers and 5 bottom negative performers?  With it all in one table ... easy ... two selects with an order by and a limit ... boom. Done. If you have something spread out over muliptle tables... unions, joins... it gets meesy FAST.


The second worst thing you can do is segregate it by year.... again... the heck? Put it in one table. There is ZERO reason to create tables based on a year.  


People keep asking me if I've dealt with lots of data. I'm thinking, yeah, I've done a lot of data. I've analyzed it, I've converted it, I've done reports on it, I've imported and exported it .... I've refined it, I've manipulated it, I've deleted it, I've created it. I've put it into warehouses. I've loved it, and I've hated it. 

I know a thing or two because I've seen a thing or two.


-tg

----------


## vb6forever

techgnome:
Will have to disagree on this one.  For historical data where time is NOT of the essence, there is NOT much difference of doing a query of a singular table or multiple tables.  However, where one is receiving a real time data feed  of multiple symbols every millisecond or so, and then manipulating that data by symbol, separate tables makes more sense.   I recognize this may go against database theory, -- more of a flat file concept -- but based on my experience seems to work best.

----------


## techgnome

Now, keep in mind, I'm not saying all stock info should be in one table... but all of the related info should be.
tblStockInfo -- Id, TickerSymbo, Name, etc.
tblStockData - Id, StockId, datetime, price, etc.

What you SHOULDN't Do is this:
tblMicrosoft --- blah blah blah
tblTwitter -- blah blah blah....
tblFord -- blah blah blah ....

All too often I see people trying to do this: tbl20022 for financial data... and....seriously, there's no need. 

Now all that said, for historical data... datawarehouse is the way to go, which does flatten the data and and denormalizes it... but those are considered offline tables not fit for transactional purposes.

-tg

----------


## vb6forever

We're in agreement.
Regards.

----------


## DataMiser

Back when we had a FAT16 file system and HDDs were measured in MBs rather than GBs and TBs then it made a little more sense to store large amounts of data in more than one file. Things have changed a lot since then though.

----------


## ntstatic

ive learnt to understand ... that when people who would be asked a question ... start arguing among themselves as to why that problem should not exist ... actually do not know the answer ... 

i am not referring to only this particular problem ... but yes it applies here too .. although @gibra did provide the one possible answer that is to create a Union query ... which slows down the database quite a bit ... probably because Rushmore optimization is not used in a union query

why do we want to question as to y a question exists ... particularly when we do not know the answer ... 
this issue is much much more profound on stackoverflow ... "VBForums" is much much better in that regards ... as Ive found that the "Gurus" are much more understanding ... that it is not an ideal world ... problems which should not exist ... or which should not be allowed to keep existing ... do indeed exist 

... in the end ... still this is the best community i have been part of ..

EDIT : i have a legacy media management program written in VB6 inspired from Charles. P V 's thumbnailer which stores thumbnails of pictures videos etc  in an mdb file ... and because the thumbnails are uncompressed ... the file does reach the 2 gb limit ... i have thought about upgrading the database ... but have not had the inclination to ... too lazy ... i have thought about a couple of methods to use multiple databases ... one of them using the union query as stated above ... another to make a class to maintain a pool of databases ... but do not believe it will be a good solution ... 

currently i just delete the thumbnails and compact the database ... the thumbnails are generated on demand ... so the thumbnails of media which have not been used in quite  some time ... are automatically removed

----------


## techgnome

> ive learnt to understand ... that when people who would be asked a question ... start arguing among themselves as to why that problem should not exist ... actually do not know the answer ... 
> 
> i am not referring to only this particular problem ... but yes it applies here too .. although @gibra did provide the one possible answer that is to create a Union query ... which slows down the database quite a bit ... probably because Rushmore optimization is not used in a union query
> 
> why do we want to question as to y a question exists ... particularly when we do not know the answer ... 
> this issue is much much more profound on stackoverflow ... "VBForums" is much much better in that regards ... as Ive found that the "Gurus" are much more understanding ... that it is not an ideal world ... problems which should not exist ... or which should not be allowed to keep existing ... do indeed exist 
> 
> ... in the end ... still this is the best community i have been part of ..
> 
> ...


Usually it's not because we don't know the answer. It's because we DO know the answer, and insist that "our" answer is the most correct.
Replies in this place come in several flavors.
# the kind that just answer the question susinctly and quickly just based on the info given
# the kind that try to extract a little more info about the context to give a better answer than item #1
# the kind that answer the question, but then try to give it more context and explain the pitfalls and problems with the current structure.

There is enough of us in the forums here that we've pretty much seen it all. We've got decades of experience behind us, both individually and collectively. So when we see someone make a mistake with their architecture or database ... we're going to call them out on it. We might answer the question, we might not. the OP has 4 tables with the exact same structure ... to me this is a design smell ... Sure, and UNION "solves" the problem ... but not really. It's putting a band-aid on a cut that needs stitches. It's a design that's going to come back and bit the OP in the backside at some point. It's not a matter of IF it'll happen, it's a matter of when. "The. Bill. Comes. Due." 

Now, sometimes it's a design we have to live with and we just have to deal with it. I worked with a system that had an Accounts REcievable (A/R) and it was all borken out. It was because it was designed by developers thinking like developers. They were not thinking like accountants, and not like DBAs either. Reporting was a nightmare, few wanted to touch it. I got lucky. I hated it, but I also relished in the challenge, and I also made a lot of noise. I mean A LOT of noise about it. So much so, that when it came time to revamp the system, they brought me into the process. I gave them my ideas and painpoints. When I got to finally see the end result, it was so much better and clearer. 20 tables reduced to 6 core tables. Redundant data was removed and I could instantly see 80% of the custom reporting code I'd written over the years go out the window - I consider that a good thing, because it meant all that code was crap to work around the problems with data being scattered all over the place. 

So, no, we don't argue and bicker because we don't know the answer, we do it because we know sometimes there is a better way. 

-tg

----------


## Phill.W

> Have you ever handled 2.5 Lacs data, otherwise you have not asked this silly question.. so one has to have splitted database to handle large data.


1 "lakh" is 100,000. 
2.5 of those is only a quarter of a million rows.  
To any, half-decent, DBMS that's _child's play_; they'll cope with _billions_ of rows if properly structured and indexed. 

The Table-per-Thing model _always_ breaks down eventually. 

If you have a genuine need to "archive off" old data, then the "modern" solution is Partitioning. 

regards,   Phill  W.

----------


## wqweto

> although @gibra did provide the one possible answer that is to create a Union query ... which slows down the database quite a bit ... probably because Rushmore optimization is not used in a union query


Just don't! Using UNION in this case is almost certainly wrong. What you need to use here is UNION *ALL* and there is a very significant difference in performance and overall results.

Remember, very very rarely one needs to use UNION and more often wants to use UNION ALL but is not clearly remembering/understanding the difference between these opearators.

cheers,
</wqw>

----------


## ntstatic

> Usually it's not because we don't know the answer. It's because we DO know the answer, and insist that "our" answer is the most correct.
> Replies in this place come in several flavors.
> # the kind that just answer the question susinctly and quickly just based on the info given
> # the kind that try to extract a little more info about the context to give a better answer than item #1
> # the kind that answer the question, but then try to give it more context and explain the pitfalls and problems with the current structure.
> 
> There is enough of us in the forums here that we've pretty much seen it all. We've got decades of experience behind us, both individually and collectively. So when we see someone make a mistake with their architecture or database ... we're going to call them out on it. We might answer the question, we might not. the OP has 4 tables with the exact same structure ... to me this is a design smell ... Sure, and UNION "solves" the problem ... but not really. It's putting a band-aid on a cut that needs stitches. It's a design that's going to come back and bit the OP in the backside at some point. It's not a matter of IF it'll happen, it's a matter of when. "The. Bill. Comes. Due." 
> 
> Now, sometimes it's a design we have to live with and we just have to deal with it. I worked with a system that had an Accounts REcievable (A/R) and it was all borken out. It was because it was designed by developers thinking like developers. They were not thinking like accountants, and not like DBAs either. Reporting was a nightmare, few wanted to touch it. I got lucky. I hated it, but I also relished in the challenge, and I also made a lot of noise. I mean A LOT of noise about it. So much so, that when it came time to revamp the system, they brought me into the process. I gave them my ideas and painpoints. When I got to finally see the end result, it was so much better and clearer. 20 tables reduced to 6 core tables. Redundant data was removed and I could instantly see 80% of the custom reporting code I'd written over the years go out the window - I consider that a good thing, because it meant all that code was crap to work around the problems with data being scattered all over the place. 
> ...


objectively the real answer is 
# the kind that just answer the question susinctly and quickly just based on the info given

because even life is unknowable in its entirety ... we have to make do with what we know... 

so if we would further the discussion ... 
VB6 is decades old by now like you so rightfully said  ... and if someone is a noob at it he/she should not be ... and still a question is asked ... the OP wants to know if any solution exist that he/she is not aware of ... knowing that it is not the optimal question ... 

why is the answer always posted ... questioning the existence of the question itself ... 
a good answer would be ...

eg.
no there is no good solution to your question ... you can do a union query .. but that is a bad idea ... mostly in terms of speed ... but also in terms of which table to update in case data is to be added ...

also a class can be created which would query multiple tables and present the data to the consumer part of the program ... also it will decide where new data would be updated .. but it seems more work than it is worth

----------

