One front End - Multiple Backends

[disneygoof via AccessMonster.com]

Good day all...

I currently have a front-end that resides on each pc, linked to a back-end on
the server...no issues. I want to take the back-end and break it into
multiple back-ends...for example: I have time tracking tables, Project Tables,
Employee Tables, Process Tables, etc...in the back-end. I am thinking about
creating a db for each group (Time,Project, Employee, Process, etc), then
link each to the front-end...does anyone see any issues with doing this? All
table/db will need to link when the front-end is opened.

Thanks,
David

 

[Marshall Barton]

I currently have a front-end that resides on each pc, linked to a back-end on
the server...no issues. I want to take the back-end and break it into
multiple back-ends...for example: I have time tracking tables, Project Tables,
Employee Tables, Process Tables, etc...in the back-end. I am thinking about
creating a db for each group (Time,Project, Employee, Process, etc), then
link each to the front-end...does anyone see any issues with doing this? All
table/db will need to link when the front-end is opened.

The only serious concern is that tables in different files
can not have enforced referential integrity.

OTOH, what benefit are you looking to gain from doing this?

 

[disneygoof via AccessMonster.com]

My current db of tables is getting quite large and growing...I am worked
about maxing the db out. Also I thought this would be easier to maintain the
tables.

I currently have a front-end that resides on each pc, linked to a back-end on
the server...no issues. I want to take the back-end and break it into

[quoted text clipped - 3 lines]

link each to the front-end...does anyone see any issues with doing this? All
table/db will need to link when the front-end is opened.

The only serious concern is that tables in different files
can not have enforced referential integrity.

OTOH, what benefit are you looking to gain from doing this?

 

[disneygoof via AccessMonster.com]

Worked should be worried...

My current db of tables is getting quite large and growing...I am worked
about maxing the db out. Also I thought this would be easier to maintain the
tables.
[quoted text clipped - 6 lines]

OTOH, what benefit are you looking to gain from doing this?

 

[John Spencer]

How big is your database at this point?
Have you ever compacted it?

Unless you are approaching at least one gigabyte in size, I would not
reorganize the tables into multiple databases. It will complicate using the
database to have multiple database backends.
== Referential integrity cannot be automatically maintained between tables in
different databases. You will have to do that through VBA code and queries.
== You will need a way to relink to the tables that allows you to specify
which table is in which back-end.
== You might see some performance hits when working in a query and referring
to tables in different back-end databases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Worked should be worried...

My current db of tables is getting quite large and growing...I am worked
about maxing the db out. Also I thought this would be easier to maintain the
tables.

I currently have a front-end that resides on each pc, linked to a back-end on
the server...no issues. I want to take the back-end and break it into

[quoted text clipped - 6 lines]

OTOH, what benefit are you looking to gain from doing this?

 

[disneygoof via AccessMonster.com]

Presently 223MB, and I can not conpact yet...I made some bad decisions and if
I compact now I will mess up some autonumbers...working to correct. but
taking time with everything else.

I will hold off...thanks

How big is your database at this point?
Have you ever compacted it?

Unless you are approaching at least one gigabyte in size, I would not
reorganize the tables into multiple databases. It will complicate using the
database to have multiple database backends.
== Referential integrity cannot be automatically maintained between tables in
different databases. You will have to do that through VBA code and queries.
== You will need a way to relink to the tables that allows you to specify
which table is in which back-end.
== You might see some performance hits when working in a query and referring
to tables in different back-end databases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Worked should be worried...

[quoted text clipped - 6 lines]

[quoted text clipped - 6 lines]
OTOH, what benefit are you looking to gain from doing this?

 

[Marshall Barton]

My current db of tables is getting quite large and growing...I am worked
about maxing the db out. Also I thought this would be easier to maintain the
tables.

I currently have a front-end that resides on each pc, linked to a back-end on
the server...no issues. I want to take the back-end and break it into

[quoted text clipped - 3 lines]

link each to the front-end...does anyone see any issues with doing this? All
table/db will need to link when the front-end is opened.

The only serious concern is that tables in different files
can not have enforced referential integrity.

Define your meaning of "quite large" and how fast is it
"growing".

MDB files are linited to 2GB.

Depending on why it is growing, Compacting the file may(?)
make your question moot.

If you really have that much data, does your proposed
partioning of the table make a real difference? For
example, if all the "growth" is in one or two tightly
related tables, the referential integrity issue may be
difficult to deal with. OTOH, if there is a natural
partition into sets of unrelated tables, it could be a no
brainer.

 

[disneygoof via AccessMonster.com]

Thanks...frist I need to fix my numbering issue before I do anything from I
am getting...then we shall see how things stand.

My current db of tables is getting quite large and growing...I am worked
about maxing the db out. Also I thought this would be easier to maintain the

[quoted text clipped - 8 lines]
Define your meaning of "quite large" and how fast is it
"growing".

MDB files are linited to 2GB.

Depending on why it is growing, Compacting the file may(?)
make your question moot.

If you really have that much data, does your proposed
partioning of the table make a real difference? For
example, if all the "growth" is in one or two tightly
related tables, the referential integrity issue may be
difficult to deal with. OTOH, if there is a natural
partition into sets of unrelated tables, it could be a no
brainer.

 

[John Spencer]

Make a copy of your database and then do a compact. Test and see if that
causes you any problems.

Compacting a database should have no effect on the autonumber values (or any
values in any field).

223 MB is very manageable as a database size - unless it took only a very
short time to get to that size.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Presently 223MB, and I can not conpact yet...I made some bad decisions and if
I compact now I will mess up some autonumbers...working to correct. but
taking time with everything else.

I will hold off...thanks

How big is your database at this point?
Have you ever compacted it?

Unless you are approaching at least one gigabyte in size, I would not
reorganize the tables into multiple databases. It will complicate using the
database to have multiple database backends.
== Referential integrity cannot be automatically maintained between tables in
different databases. You will have to do that through VBA code and queries.
== You will need a way to relink to the tables that allows you to specify
which table is in which back-end.
== You might see some performance hits when working in a query and referring
to tables in different back-end databases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Worked should be worried...

[quoted text clipped - 6 lines]

[quoted text clipped - 6 lines]
OTOH, what benefit are you looking to gain from doing this?

 

[John W. Vinson]

Presently 223MB, and I can not conpact yet...I made some bad decisions and if
I compact now I will mess up some autonumbers...working to correct. but
taking time with everything else.

If you can't compact, splitting up the database won't help: it will require
compaction when you split, and importing tables will reset autonumbers in any
case.

If your data is approaching two billion bytes, you really should be moving to
client-server (SQL/Server for example).

 

[John W. Vinson]

Thanks...frist I need to fix my numbering issue

What's the numbering issue?

 

[disneygoof via AccessMonster.com]

As I "guessed" my way through things early on, I used auto numbering in some
tables and should not have, becasue they ended up being temp table...so If I
compact and repair the back-end table, it will re-order my autonumbers. So
once the data is transfered to the final tables, and I reference the
information I will get data from, lets say Job 1 and Job 199 becasue the
value is the same...somthing like that...I know what I did now was stupid,
but it worked for me. NOW I am paying for it. I just need to go back though
the db and redo a number of tables and code...but just have not had time with
everything else on my plate...same story too busy.

 

[Marshall Barton]

As I "guessed" my way through things early on, I used auto numbering in some
tables and should not have, becasue they ended up being temp table...so If I
compact and repair the back-end table, it will re-order my autonumbers. So
once the data is transfered to the final tables, and I reference the
information I will get data from, lets say Job 1 and Job 199 becasue the
value is the same...somthing like that...I know what I did now was stupid,
but it worked for me. NOW I am paying for it. I just need to go back though
the db and redo a number of tables and code...but just have not had time with
everything else on my plate...same story too busy.

Compacting will not change any existing values. If you have
made assumptions about an autonumber field's values for
records that have not yet been created, then you have made a
serious mistake because there is no way to predict the
"next" autonumber.

The only thing that compacting does to incrementing
autonumbers is set the maximum number that has been used to
the highest value in the field. That may be less than the
highest number actually used if you added some records and
then deleted them.

I am having trouble imagining a scenario where any of this
could possibly make a difference to anything.

 

[disneygoof via AccessMonster.com]

Let me see if I can try to explain this better...

Autonumber: 1, 2, 3 , 4, in Table A (Temp Table) have been used and
transfered to a final Table K for JOB 1

Time goes by: NO Compacting done: AutoNumbers in Table A are now at 100, 101,
102, etc...Keep in mind Table A is emptied all the time

If I compact Table A, will not the Autonumber go back to 1, 2, 3, 4, etc...?
If this happens, and now I am on JOB 60, then when I referenc JOB 60
information, I will/may also get JOB 1 info.

Not sure I explained this exactly right, but I have tried compacting and this
is what I got...messed up JOB overlapping data...

...bottom line, I did things that worked at the moment and did not think
about the future...I messed up early on, made many mistakes and now I need to
correct them. When I correct it, I will try to repost what I did wrong
exactly and how I fixed it. I have not looked at it in about 9 months and my
memory isn't as good as it use to be...

As I "guessed" my way through things early on, I used auto numbering in some
tables and should not have, becasue they ended up being temp table...so If I

[quoted text clipped - 5 lines]

the db and redo a number of tables and code...but just have not had time with
everything else on my plate...same story too busy.

Compacting will not change any existing values. If you have
made assumptions about an autonumber field's values for
records that have not yet been created, then you have made a
serious mistake because there is no way to predict the
"next" autonumber.

The only thing that compacting does to incrementing
autonumbers is set the maximum number that has been used to
the highest value in the field. That may be less than the
highest number actually used if you added some records and
then deleted them.

I am having trouble imagining a scenario where any of this
could possibly make a difference to anything.

 

[John W. Vinson]

As I "guessed" my way through things early on, I used auto numbering in some
tables and should not have, becasue they ended up being temp table...so If I
compact and repair the back-end table, it will re-order my autonumbers.

Compacting has never reordered any existing numbers in my tables. Please
explain.

So once the data is transfered to the final tables, and I reference the
information I will get data from, lets say Job 1 and Job 199 becasue the
value is the same...somthing like that...I know what I did now was stupid,
but it worked for me. NOW I am paying for it. I just need to go back though
the db and redo a number of tables and code...but just have not had time with
everything else on my plate...same story too busy.

We'll be glad to help. It's very easy to convert a table field from Autonumber
to Long Integer; it will preserve the existing values.

 

[John Spencer]

You can compact and very probably not mess up the autonumbers too much if you
add one record to each of the empty temp tables before you compact the
database. Once you have done the compact, delete the record you added to each
of those tables.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[disneygoof via AccessMonster.com]

I can't explain any more...I don't know exactly myself what occured, until I
get back into those areas, I just can exactly remember. However my
understand is compacting reorders auto numbers to the MAX last used, if the
table is empty I thought it started over at 1? Maybe I am wrong, does not
matter, I need to fix whatever I did wrong to correct what ever is casuing my
problem.

Thanks for everyone's help! I do appreciate all!

 

[disneygoof via AccessMonster.com]

That makes sense. I will still need to fix the underlying issue, if for some
reason I am not here to do this, others will not know what to do. THanks I
will try this short term...

You can compact and very probably not mess up the autonumbers too much if you
add one record to each of the empty temp tables before you compact the
database. Once you have done the compact, delete the record you added to each
of those tables.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Let me see if I can try to explain this better...

[quoted text clipped - 16 lines]

exactly and how I fixed it. I have not looked at it in about 9 months and my
memory isn't as good as it use to be...

 

[John W. Vinson]

However my
understand is compacting reorders auto numbers to the MAX last used, if the
table is empty I thought it started over at 1?

Your understanding is either incorrect or I'm not understanding your
understanding.

Compaction will (depending on your Access version) change the "seed" - the
value that will be assigned to the next autonumber to be created. It will
emphatically NOT reorder or reassign existing values now in the table.

 

[disneygoof via AccessMonster.com]

I agree it won't change what's already in a table...let me try again...


1. Open Form A and SubForm A:
2. Pick Items in subform which populate Table A: Auto#: 1, 2, 3 and 4
3. Click some button...then Transfer Values in Table A to Table K
4. Table K now as numbers 1, 2, 3, 4
5. Time goes on...1 thru 4 goes on daily
6. Now 6 months later I compact the back-end db
7. Open form A and Subform A:
8. Pick Items in subform which populate Table A: Auto#: 1, 2, 3 and 4
9. Click some button...then Transfer Values in Table A to Table K (now I have
TWO, 1's, TWO 2's, TWO 3's, etc...This is the problem...if I understand what
compacting does...