Combo box specific value to different table (1 Viewer)

[waseem0888]

Hello

I need suggestions i am facing a database slowness issues due to high numbers of records in a single table. I have one combo box from where I have to select different activities but there are two activities one is Stormwater and the other is Sewer each activity is having more than 50,000 records and its increasing. i have put my backend in the server and the frontend is used by almost 30 users. but the DB is very slow now to all users. my question is that is possible for these two specific activities when the user selects these activities it goes to two separate tables like one for stormwater and other for sewer. Or kindly suggest me alternative solution to how I can solve this issue to make the DB fast.

Thanks

 

[arnelgp]

did you give each User a copy of his Front End?
Try googling "Persistent Access Connection to backend".

 

[Pat Hartman]

To be a little more specific.

1. The proper way to implement a multi-user Access application is to put the BE (tables only) in a shared folder on the server and distribute a copy of the FE to each user which will be downloaded to their C: Drive. Every user will end up with his own copy of the FE. The FE is NEVER, EVER shared. There are a number of easy ways to do this and they're posted here. If you need additional help, just ask.
2. 50,000 is trivial in the world of record counts. Access can work with millions of rows.

Start by compacting the BE and then compacting the FE.
Distribute the FE to each user. I use a shortcut that runs a batch file. The batch file copies the common FE master from the server to the user's local drive. So, to start the process, you would need to send everyone a shortcut. There are other methods, mostly more complicated.

You do not need to, nor should you split the table into two tables. I'm sure that would cause other complications in your app.

 

[waseem0888]

did you give each User a copy of his Front End?
Try googling "Persistent Access Connection to backend".

Thank you for the reply yes brother every user is using a separate copy of the frond-end and to make sure I copy myself for every user to their desktop.

I have tried the technique you suggested "Persistent Access Connection to backend" i follow the steps by making even separate dummy table and using a piece of codes but I didn't improve anything.

 

[waseem0888]

To be a little more specific.

1. The proper way to implement a multi-user Access application is to put the BE (tables only) in a shared folder on the server and distribute a copy of the FE to each user which will be downloaded to their C: Drive. Every user will end up with his own copy of the FE. The FE is NEVER, EVER shared. There are a number of easy ways to do this and they're posted here. If you need additional help, just ask.
2. 50,000 is trivial in the world of record counts. Access can work with millions of rows.

Start by compacting the BE and then compacting the FE.
Distribute the FE to each user. I use a shortcut that runs a batch file. The batch file copies the common FE master from the server to the user's local drive. So, to start the process, you would need to send everyone a shortcut. There are other methods, mostly more complicated.

You do not need to, nor should you split the table into two tables. I'm sure that would cause other complications in your app.

Thank you for the reply yes in Backend only tables in the server and link it through IP address to the front end and copy the front end for every user on their desktop it is not shared. the current number of records is almost 180690 for total in the table what I mention is 50000 for the activity sewer and 50000 for the stormwater activity for these two activities records are high and it's increasing. i am not facing issues like opening slow forms are opening normally but when i try to retrieve the result for these two activities is slow on the network but for other activities its working better. data from other tables is retrieving normally but only from this one table where I am having 180690 records is slow. but when I used in the same system where I kept my Back-end it's working fine and when i try to filter the data directly from the backend it's filtering the data very slow. I have installed the same DB in other my many other site projects where the number of records is not high maybe 70,000 in total its working normally. But only in this project, I am facing issue even here also other activities are ok but only stormwater and sewer is slow when the user tries to retrieve the results through the form. So I was thinking maybe the reason is the high number of records for these two activities. Initially, it was working perfectly it become slow with time when the numbers of records start increasing

 

[arnelgp]

what does the combobox do? filter the record to that Attribitute only?
what code did you make for the "persistent" connection?

 

[waseem0888]

what does the combobox do? filter the record to that Attribitute only?
what code did you make for the "persistent" connection?

Yes, it is used to show the details of that specific activity and to store the details of that specific activity.

 

[arnelgp]

can you show the code you have of retrieving records when the combo changes it's value.

 

[Pat Hartman]

Make sure that you have appropriate indexes. Access automatically creates hidden indexes on foreign keys when you create relationships using the Relationship window.

Compact the BE regularly. Then compact the master copy of the FE. When you use saved querydefs the query access plans are stored and reused. If the BE is changing enough, the access plans might be inefficient since they are calculated based on old information. So compacting the BE makes the BE as efficient as possible and updates all the statistics used by the query plan engine. And then compacting the FE removes the saved execution plans. They are then rebuilt the first time an uncompiled query is run.

 

[Isaac]

Thank you for the reply yes in Backend only tables in the server and link it through IP address to the front end and copy the front end for every user on their desktop it is not shared. the current number of records is almost 180690 for total in the table what I mention is 50000 for the activity sewer and 50000 for the stormwater activity for these two activities records are high and it's increasing. i am not facing issues like opening slow forms are opening normally but when i try to retrieve the result for these two activities is slow on the network but for other activities its working better. data from other tables is retrieving normally but only from this one table where I am having 180690 records is slow. but when I used in the same system where I kept my Back-end it's working fine and when i try to filter the data directly from the backend it's filtering the data very slow. I have installed the same DB in other my many other site projects where the number of records is not high maybe 70,000 in total its working normally. But only in this project, I am facing issue even here also other activities are ok but only stormwater and sewer is slow when the user tries to retrieve the results through the form. So I was thinking maybe the reason is the high number of records for these two activities. Initially, it was working perfectly it become slow with time when the numbers of records start increasing

Stepping back for a minute, why are you designing it this way to begin with? Returning fifty thousand records when someone selects something in a combobox? Do any of your end-users really "work" 50,000 items in an afternoon?

My suggestion would be to simply limit the amount of records returned by the combobox dropdown...To something reasonable, and something that has a direct correlation to the job duty they are performing at the time.