Split Form Slows Database

[split_atom18]

I have Split form with a searchbox on it. I can filter the records based on what is typed in the searchbox. However this is a multi user database. When someone has open that form and it is filtered, everyone else using the database comes to a slow crawl. Most of my forms are unbound, however I don't know how to create a datasheet with thousands of records unbound so that it doesn't slow down the database. When someone is viewing entire tables. No changes are made on this form.

Any links or arrows or examples would be greatly appreciated. I am not looking for someone to do it for me, just a place to start looking.

Thanks in advance,

Dan

[darbid]

Is your database split with a front end and back end?

[split_atom18]

It is and the back end is on a windows server 2008, office is wired for 10gbs however we only have 1 gig switches. So it is on a 1 gbs network.

On a side note, although it is split. I don't use transactions/connection strings to a point, as all the tables are linked to the back end. Is this something I should change?

One other thing, I do have a SQL Server license however I don't really know SQL to terribly well, so I have been putting off that move for the time being, until almost all the kinks are worked out.

Thanks for any help,

Dan

[darbid]

One other thing, I do have a SQL Server license however I don't really know SQL to terribly well, so I have been putting off that move for the time being, until almost all the kinks are worked out.

I must first say I am no expert. I have however managed to get Access 2003 to work with SQL 2000. It significantly speeds things up. Especially where you have a back end on a networked drive. You can link you tables just like you have now to SQL.

This form that gives you the problem. I assume that you have the form record source set to a table. Then you are filtering. That is like going to the well to get a bucket of water and then when you get home you drink 1 glass and throw the rest away.

One way of making this better is that once you have your criteria of what the user wants to see, then change the forms record source.

[split_atom18]

The form is Contacts it is a list of all our customers/dealers/suppliers/etc etc. It is a portal into their details. For instance if you call up and I see on the caller id you number, I can punch it in the search field and it will filter it to your account. Then I can click the open hyperlink which opens the Contact Details form. However sometimes I want to see all the customers from Texas for instance. If I follow you correctly I should not filter the form with the filter property I should change the sql statement to limit it to only the records that conform to what I am looking for?
The form speed is fine. However, if you have that form open and I open a different front end on a different computer, and try to manipulate an unbound form or any other form for that matter, it takes like 3 seconds to respond and do what you want it to. Behind buttons there are commonly ADO recordset manipulation on a single record. I believe because it is looking at all those records when you try and manipulate another one it slows me up.

I appreciate all the feedback and the continued discussions.

Dan

[darbid]

However sometimes I want to see all the customers from Texas for instance. If I follow you correctly I should not filter the form with the filter property I should change the sql statement to limit it to only the records that conform to what I am looking for?
The form speed is fine.

That is exactly what I am saying. Currently you have text boxes or other controls which people are filling, then they are clicking a search button. You are building a WHERE string and using the filter method of the form. Instead of using a filter change the RecordSource. This way the form is not bringing the WHOLE table accross the wire first inorder for it to do the filtering. (I must say my theory here is based on SQL and I just remembered you have not changed over yet but I think it works with an access backend too.) The theory is get the backend to do the work, and only send the data the user wants. Currently you are making the back end work its **** off to send all the data, you are using lots of bandwidth and then you are filtering most of it away, in fact when you look at one record you are thought almost 100% of it away.

However, if you have that form open and I open a different front end on a different computer, and try to manipulate an unbound form or any other form for that matter, it takes like 3 seconds to respond and do what you want it to. Behind buttons there are commonly ADO recordset manipulation on a single record. I believe because it is looking at all those records when you try and manipulate another one it slows me up.

This does not make sense opening an unbound form on another computer can never (if it is truly unbound) be effected by activities elsewhere. I would love for someone to tell me I am wrong though.

If you are then executing code which opens ADO recordsets etc then this does have an impact on the backend. Are you using ADO with the linked tables or are you using them directly on the back end? I understand that DAO would be better for Access and the link tables, but I think this is very theoretical.

I have had speed issues in the past too and written in forums. I am way over 500 posts in another forum. I doubt you will get someone saying..here is your problem and this is how you fix it. You are going to have to just play around and see what you can do.

[split_atom18]

I do use ADO here is an example from one of my unbound forms:

Please Login or Register  to view this content.

Every time you click a checkbox it does call for a pricing update, so that the current pricing is shown on the form after each click. This my be my issue. I agree with you there is no one thing fix. I appreciate all the help you have given me so far. I like to try and make the back end do the work.

Here is my search sting on my frmContactsList: (is a macro, so converted it to vba to post here)

Please Login or Register  to view this content.

I maybe should change it from a macro to vba anyway, but just wanted to give you an idea.

Dan