467,918 Members | 1,235 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,918 developers. It's quick & easy.

Data Access in multiple history tables across multiple mdb's

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
Nov 13 '05 #1
  • viewed: 1646
Share:
3 Replies
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

Nov 13 '05 #2
why not create a union query that returns the values from the three
database tables and then query that?
Nov 13 '05 #3
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!
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

32 posts views Thread by Neil Ginsberg | last post: by
6 posts views Thread by Peter Frost | last post: by
9 posts views Thread by Anil Gupte | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.