December 5, 2007 at 12:28 pm
I upgraded a MS Database so that it uses a SQL back-end database now. thinking it would make it run a lot faster, and it did.
With one problem. The find fields hangs when searching for a record.
any suggestion on what happen during the migration.
December 6, 2007 at 11:48 am
It could be that Access decides to pull the whole table in and than doing the find.
Usually it worth creating an extra "query" (on the linked table?) to search for specific conditions.
December 7, 2007 at 1:54 am
We've had similar problems. What I have noticed is that if you search on a non-key field searching is (diabolically) slow. We were searching for a student name in a big view with about 120 fields. What we ended up doing was creating a view with just the student name and the student number. We did a dlookup on the small view to get the student number and then did a docmd.findrecord using that student number in the big view. OK it's a bit of a kludge but it worked for us.
Yes I agree that Access is trying to pull in too much data as it searches because it got worse for us as the number of records increased. When we did the initial testing on a few records it worked well - much of the code was borrowed from the original Access application - but when we went to a more realistic size it slowed to a crawl.
April 24, 2008 at 7:00 pm
To avoid the problem of Access pulling all records and filtering on the client side, use "pass-through" queries. These do the filtering/sorting on the sql side (warp speed) and returns only what you want, how you want it.
I've seen this reduce 20 minute response to 20 seconds. However, it is read only. You can't use pass-throughs for updates or inserts.
July 22, 2008 at 11:00 am
I agree with Scott (mostly). You only want to pull back records for 1 person (place or thing) instead of searching a table with thousands (consequently millions) of records. I've noticed that once an Access table gets more than 100K records things tend to creep. Time to move that processing to SQL Server and only use Access to 'peer' into the data. Try using a stored procedure or a view on SQL Server, that should speed things up quite a bit.
However, you CAN use pass-throughs for inserts and updates, I do it all the time. Whether it's directly into a table thru an Access form or using an Access form and using a stored procedure to do the update. You can do it all.
July 24, 2008 at 10:40 pm
My opinion is/was based on statements by a handful of Access MVPs.
Perhaps they were too circumspect. I shouldn't say it can't be done.
Upon reflection, it seems to me that a PTQ wouldn't actually offer much benefit for an insert/update from an Access DE form. Just not enough data involved.
The real advantage of the PTQ comes from:
1- the SQL engine's superior filtering and sorting speed per se (and likely superior bandwidth of the server h/w);
combined with
2 -the reduced duration of the data return transmission due to the reduced record/column count.
And, of course, the more involved the 'select', the greater the savings.
Perhaps your PTQs move data from around in the sql engine. Or, ...
Could you offer some code that demonstrates how you do/did inserts or updates via PTQs?
This would be much appreciated.
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply