Join Tables to one (1 Viewer)

[Reflex_ht]

Hy,

We have a huge Database (relative huge ). Because of that I had to split the database on multiple tables to not lose performance. The main seperation is for every year and Terminal so I have now 6 tables with the same data and structure. Its making maintaining, changes and coding realy hard

Tables:
tbl_DL_Salzburg_2012
tbl_DL_Salzburg_2013
tbl_DL_Munchen_2012
tbl_DL_Munchen_2013
tbl_DL_Ulm_2012
tbl_DL_Ulm_2013

At the end of every year we have more than 10 000 records in each table.

The second Problem for joining the tables is that i have to filter them on Terminal (Salzburg, Munchen, Ulm) and on Year (2012, 2013, 2014). The Form I use on that table is a split form (reason is the fast filtering and searching in the datashed).

The first problem with the large amount of records i can solwe with an sql server but there is still the problem with the filtering

I tryed to use the form RecordSource and an SQL statement but in that case (depends how the SQL is struktured and the RecordSource is used) the users cant add or delete records in the table. It shows them that the record is added or deletet but when they leav the form they are all gone

P.S. It´s an multiuser database. Making things more complikated

Does enyone know how to solve this second problem with filtering and not disturbing adding and deleting records.

I know that splitforms are not good but the users don´t want to use something other that also takes me away the solution with unbound forms

I would like to move to one table until the end of the year. If not I have to handle 9 same tables. Insane :banghead:

Is maby the filter on form load a solution?

 

[MarkK]

How many tables are there? 10,000 records is not excessive, and not a good enough reason to break up tables.

 

[Reflex_ht]

For now there are 6 Tables:
tbl_DL_Salzburg_2012
tbl_DL_Salzburg_2013
tbl_DL_Munchen_2012
tbl_DL_Munchen_2013
tbl_DL_Ulm_2012
tbl_DL_Ulm_2013

I know now that there is no realy good reason for spliting the table but I wrote my first VBA code 9 months ago. I didn´t know it bether

Because of that I don´t want to use selfmade Ideas for such problems. I rather ask profesionals on this Forum who have more than 9 months experience with Access databases.

The database is growing so fast (we even use SMS for Status notifications with automatic data changes in the databese by recived SMS messages - till now my greates funktion ) that i´m thinking to move on VB.Net.

But if I can´t get one big table only beacuse I don´t know how to filter the data how could I work with VB.Net

The number of records is just a bad excuse for a stupid solution I made in my programer chilldhod