Data Access in multiple history tables across multiple mdb's

[Ron Nolan]

I have a large application that contains lots and lots of financial
history data.
The history data is currently set up in a table called 'TblHist' that
exists inside each of these three .mdb files.
Each .mdb has approx. 175k rows in its history table.
HistData2001.mdb - history data in TblHist for year 2001
HistData2002.mdb - history data in TblHist for year 2002
HistData2003.mdb - history data in TblHist for year 2003.
(All .mdb's are in the same folder)

On a form, the user will know the series of records to look for, but
might not know the year the data was originally submitted.
Is there a way in Access 2000 where a user can select a range of one
or more years from a list box 'selectedyearsX', then search the above
listed .mdb's using 'selectedyear1', then if not found search
'selectedyear2', etc.?

I thought putting each years' tables into one .mdb would pose a higher
risk, so I left things as they are.

(disclaimer: this app only has budget for maintenance right now, so
I'm not looking for this thread to start a 'conversion debate' of
Access to SQL Server, etc.) I've read too many of those in times
past. I'll convert it later when time/budget allows; but for now I
need assistance with this current dillemma please...thanks)

RLN

[Larry Linson]

Create yourself a temporary database with a single table, link the history
DBs, and append their records... then you'll have them all in one table
without any danger. If something should happen to "crater" that temporary
database, you can just regenerate it.

Depending on how many years' history you need to keep, you might be able to
handle it nicely in one table in one database, just retrieving appropriately
with date criteria.

Access 2000 and later can, as I suspect you know, handle a database of up to
2GB.

Larry Linson
Microsoft Access MVP
"Ron Nolan" <rl**********@yahoo.com> wrote in message
news:13**************************@posting.google.c om...

I have a large application that contains lots and lots of financial
history data.
The history data is currently set up in a table called 'TblHist' that
exists inside each of these three .mdb files.
Each .mdb has approx. 175k rows in its history table.
HistData2001.mdb - history data in TblHist for year 2001
HistData2002.mdb - history data in TblHist for year 2002
HistData2003.mdb - history data in TblHist for year 2003.
(All .mdb's are in the same folder)

On a form, the user will know the series of records to look for, but
might not know the year the data was originally submitted.
Is there a way in Access 2000 where a user can select a range of one
or more years from a list box 'selectedyearsX', then search the above
listed .mdb's using 'selectedyear1', then if not found search
'selectedyear2', etc.?

I thought putting each years' tables into one .mdb would pose a higher
risk, so I left things as they are.

(disclaimer: this app only has budget for maintenance right now, so
I'm not looking for this thread to start a 'conversion debate' of
Access to SQL Server, etc.) I've read too many of those in times
past. I'll convert it later when time/budget allows; but for now I
need assistance with this current dillemma please...thanks)

RLN

[Pieter Linden]

why not create a union query that returns the values from the three
database tables and then query that?

[RLN]

I will try that, thank you

---------------
RLN

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!