Table is too large

[davedinger]

Many moons ago I had a problem with my access 2000 database where I found the
overall size of the database had reached the maximum size. So I took my
largest table [narratives] and put it in its own seperate database, I named
the database "Narratives link" I then Linked it to the form. Now the problem
is that the table is again too large even after I compress it. I recall
someting about a way to "daisy chain" the tables so that I can just keep
linking one table to the next. for example Narratives Link Narratives link 2,
Narratives link 3. this would allow the main form to access to all of the
data from the 3 tables.

Can you help me? How do I link one table to the next and to the next ect.?

Thanks

 

[david epsom dot com dot au]

You can use a union query:

Select * from tbl_a
union all
select * from tbl_b
union all
select * from tbl_c

But you will find that you can't write to that query, so
after having found the record, you will still have to open
the appropriate table to do an update.

Also, you may not be able to open the whole query at once
-- and you probably wouldn't want to, because it would
be so slow -- so you might have to modify your find and
select procedures.

Another way to do it is to create a seperate index table,
and use something like this:

Select tbl_A.*, tbl_b.* from ((tbl_index left join tbl_a
on tbl_index.idx = tbl_a.idx) left join tbl_b on tbl_index.idx
= tbl_b.idx)

You can use calculated columns to get the matching columns
from each table into single columns, but again calculated
columns are not updatable.

What are you trying to do? Is there a better way to do it?

(david)

 

[davedinger]

This just part of the entire database but what is happening is my users open
a list of names with social security numbers attached. They double click on
one of the names and it opens a form that shows all of the daily narratives
from the narratives table that are equal to the same SSN as the one they
clicked on. The form displays one record at a time, but my users can “scrollâ€
through past narratives by clicking on a control for previous or next record.
If the users want to enter a new narrative they click on a "new button"
which simply creates a blank record using the same social Security number as
default value. The SSN is obtained from the previous form from the list they
came from. I'm afraid that a query won’t work because data entry wont be
allowed. I think I basically need a way to make an extension to the one
table so that when the form opens it pulls all of the records from the linked
tables as if all of the records were all in the same database/table.

You can use a union query:

Select * from tbl_a
union all
select * from tbl_b
union all
select * from tbl_c

But you will find that you can't write to that query, so
after having found the record, you will still have to open
the appropriate table to do an update.

Also, you may not be able to open the whole query at once
-- and you probably wouldn't want to, because it would
be so slow -- so you might have to modify your find and
select procedures.

Another way to do it is to create a seperate index table,
and use something like this:

Select tbl_A.*, tbl_b.* from ((tbl_index left join tbl_a
on tbl_index.idx = tbl_a.idx) left join tbl_b on tbl_index.idx
= tbl_b.idx)

You can use calculated columns to get the matching columns
from each table into single columns, but again calculated
columns are not updatable.

What are you trying to do? Is there a better way to do it?

(david)

Many moons ago I had a problem with my access 2000 database where I found
the
overall size of the database had reached the maximum size. So I took my
largest table [narratives] and put it in its own seperate database, I
named
the database "Narratives link" I then Linked it to the form. Now the
problem
is that the table is again too large even after I compress it. I recall
someting about a way to "daisy chain" the tables so that I can just keep
linking one table to the next. for example Narratives Link Narratives link
2,
Narratives link 3. this would allow the main form to access to all of the
data from the 3 tables.

Can you help me? How do I link one table to the next and to the next ect.?

Thanks

 

[Lynn Trapp]

I'm afraid that David's suggestion for a Union query is the only way to get
all the records from multiple tables together into one recordset. Perhaps
you need to be asking yourself why this table has grown so large. Are you
storing any pictures in the table? Are you storing records from several
years back that no one needs to reference any more? You also may need to
consider that it's time to move to a database that can handle more
information -- like SQL Server or Oracle.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

This just part of the entire database but what is happening is my users
open
a list of names with social security numbers attached. They double click
on
one of the names and it opens a form that shows all of the daily
narratives
from the narratives table that are equal to the same SSN as the one they
clicked on. The form displays one record at a time, but my users can
"scroll"
through past narratives by clicking on a control for previous or next
record.
If the users want to enter a new narrative they click on a "new button"
which simply creates a blank record using the same social Security number
as
default value. The SSN is obtained from the previous form from the list
they
came from. I'm afraid that a query won't work because data entry wont be
allowed. I think I basically need a way to make an extension to the one
table so that when the form opens it pulls all of the records from the
linked
tables as if all of the records were all in the same database/table.

You can use a union query:

Select * from tbl_a
union all
select * from tbl_b
union all
select * from tbl_c

But you will find that you can't write to that query, so
after having found the record, you will still have to open
the appropriate table to do an update.

Also, you may not be able to open the whole query at once
-- and you probably wouldn't want to, because it would
be so slow -- so you might have to modify your find and
select procedures.

Another way to do it is to create a seperate index table,
and use something like this:

Select tbl_A.*, tbl_b.* from ((tbl_index left join tbl_a
on tbl_index.idx = tbl_a.idx) left join tbl_b on tbl_index.idx
= tbl_b.idx)

You can use calculated columns to get the matching columns
from each table into single columns, but again calculated
columns are not updatable.

What are you trying to do? Is there a better way to do it?

(david)

Many moons ago I had a problem with my access 2000 database where I
found
the
overall size of the database had reached the maximum size. So I took
my
largest table [narratives] and put it in its own seperate database, I
named
the database "Narratives link" I then Linked it to the form. Now the
problem
is that the table is again too large even after I compress it. I
recall
someting about a way to "daisy chain" the tables so that I can just
keep
linking one table to the next. for example Narratives Link Narratives
link
2,
Narratives link 3. this would allow the main form to access to all of
the
data from the 3 tables.

Can you help me? How do I link one table to the next and to the next
ect.?

Thanks

 

[david epsom dot com dot au]

In a similar situation, I just do a search of the (two) tables
to find a record. When they click on another button, it takes
them to another action, or another record. I found this was faster
than querying a union query, particularly because in my case the
second table contains mostly inactive records. These records are
not editable from the search form: if the user wants to edit, they
have to click on the edit button.

In any case, I don't edit or append against a union query: once
I have identified which table contains the relevant record, I
open a query against that table.

What have you got in your table? I find it hard to image
a usable Jet table with millions of records, and if you
have fewer, larger records, perhaps you could restructure
your data?

(david)

This just part of the entire database but what is happening is my users
open
a list of names with social security numbers attached. They double click
on
one of the names and it opens a form that shows all of the daily
narratives
from the narratives table that are equal to the same SSN as the one they
clicked on. The form displays one record at a time, but my users can
“scrollâ€
through past narratives by clicking on a control for previous or next
record.
If the users want to enter a new narrative they click on a "new button"
which simply creates a blank record using the same social Security number
as
default value. The SSN is obtained from the previous form from the list
they
came from. I'm afraid that a query won’t work because data entry wont
be
allowed. I think I basically need a way to make an extension to the one
table so that when the form opens it pulls all of the records from the
linked
tables as if all of the records were all in the same database/table.

You can use a union query:

Select * from tbl_a
union all
select * from tbl_b
union all
select * from tbl_c

But you will find that you can't write to that query, so
after having found the record, you will still have to open
the appropriate table to do an update.

Also, you may not be able to open the whole query at once
-- and you probably wouldn't want to, because it would
be so slow -- so you might have to modify your find and
select procedures.

Another way to do it is to create a seperate index table,
and use something like this:

Select tbl_A.*, tbl_b.* from ((tbl_index left join tbl_a
on tbl_index.idx = tbl_a.idx) left join tbl_b on tbl_index.idx
= tbl_b.idx)

You can use calculated columns to get the matching columns
from each table into single columns, but again calculated
columns are not updatable.

What are you trying to do? Is there a better way to do it?

(david)

Many moons ago I had a problem with my access 2000 database where I
found
the
overall size of the database had reached the maximum size. So I took
my
largest table [narratives] and put it in its own seperate database, I
named
the database "Narratives link" I then Linked it to the form. Now the
problem
is that the table is again too large even after I compress it. I
recall
someting about a way to "daisy chain" the tables so that I can just
keep
linking one table to the next. for example Narratives Link Narratives
link
2,
Narratives link 3. this would allow the main form to access to all of
the
data from the 3 tables.

Can you help me? How do I link one table to the next and to the next
ect.?

Thanks