|  | Search funtion .... | | |
| | | Sue Compelling |  |
|  Posted: Fri Sep 05, 2008 6:06 am Post subject: Search funtion .... |  |
Hi
Using ACCESS 2000
I have just completed a refresh of an existing DB that my users have been
happily using for 5 years, without too much complaint. The refresh was
mainly look and feel of the forms and reports.
Context:
I undertook the dev work in 2007 though saved the DB in 2000 (the version of
my users)
The DB only has some 6,000 records (tables DB is 11 MB, Forms, Queries,
Report DB is 1.5 MB)
The DB has max of 8 users at any one time
Problem:
When I undertake the Ctl F command in the test lab (ie my home) - it works
exactly as it should - quickly and accurately finds a record)
When my users try the same thing it can take up to a minute for a searched
for record to return (obviously unacceptable)
Where do I begin to resolve this issue? (ps they have compacted and
repaired the DB)
TIA
--
Sue Compelling |
| |
| | | Tom Wickerath | |
| | Posted: Sat Sep 06, 2008 1:50 am Post subject: RE: Search funtion .... | |
| |  | |
Hi Sue,
How close does your test lab replicate the production copy? If done
properly, your application has been split into a FE (front-end) and BE
(back-end) databases, and each user has their own local copy of the FE
application loaded onto their hard drives. In other words, the FE is
unshared. Does your test lab at home include a network?
This article may provide some helpful ideas for you:
Implementing a Successful Multiuser Access/JET Application
LINK
Also, I think you would be better off to implement true Query by Form (QBF)
instead of relying on the built-in Find and Replace. A network is the weak
link in the chain (ie. slowest data transfer), so the golden rule is to
"request less data". In order to use the <Ctrl><F> functionality, I'm pretty
sure you would need to drag the entire recordset over the wire. You might
want to have a look at the downloads I offer for the January/February, 2008
meeting presentations at the Seattle Access web site:
LINK
Make sure to disable Name Autocorrect, set all subdatasheets to [None], and
establish a persistent connection. Experiment some with the JETShowPlan
registry setting, as discussed in my Multiuser apps. paper. Try to make sure
that you are not ending up with table scans. If you see table scans in the
resulting Showplan.out files, you should consider applying indexes as needed.
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
| Quote: | Hi
Using ACCESS 2000
I have just completed a refresh of an existing DB that my users have been
happily using for 5 years, without too much complaint. The refresh was
mainly look and feel of the forms and reports.
Context:
I undertook the dev work in 2007 though saved the DB in 2000 (the version of
my users)
The DB only has some 6,000 records (tables DB is 11 MB, Forms, Queries,
Report DB is 1.5 MB)
The DB has max of 8 users at any one time
Problem:
When I undertake the Ctl F command in the test lab (ie my home) - it works
exactly as it should - quickly and accurately finds a record)
When my users try the same thing it can take up to a minute for a searched
for record to return (obviously unacceptable)
Where do I begin to resolve this issue? (ps they have compacted and
repaired the DB)
TIA
--
Sue Compelling |
|
| |
| | | Sue Compelling | |
| | Posted: Sat Sep 06, 2008 4:12 am Post subject: RE: Search funtion .... | |
| | | |
Hi Tim
Appreciate the response and indeed the links to QBF 'training tools'. Have
now saved this to my favs
In response to your questions;
a) Yes - the App is comprised of FE and BE DBs
b) No - my home environment doesn't replicate theirs (as I don't use a
network)
c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)
A question - Rather than me deploying this myself - I did zip and email the
FE to the Manager and talked them through the download and linking (and
copying the master to their local drives etc). Could the zipping and
emailing be part of the problem?
TIA
--
Sue Compelling
"Tom Wickerath" wrote:
| Quote: | Hi Sue,
How close does your test lab replicate the production copy? If done
properly, your application has been split into a FE (front-end) and BE
(back-end) databases, and each user has their own local copy of the FE
application loaded onto their hard drives. In other words, the FE is
unshared. Does your test lab at home include a network?
This article may provide some helpful ideas for you:
Implementing a Successful Multiuser Access/JET Application
LINK
Also, I think you would be better off to implement true Query by Form (QBF)
instead of relying on the built-in Find and Replace. A network is the weak
link in the chain (ie. slowest data transfer), so the golden rule is to
"request less data". In order to use the <Ctrl><F> functionality, I'm pretty
sure you would need to drag the entire recordset over the wire. You might
want to have a look at the downloads I offer for the January/February, 2008
meeting presentations at the Seattle Access web site:
LINK
Make sure to disable Name Autocorrect, set all subdatasheets to [None], and
establish a persistent connection. Experiment some with the JETShowPlan
registry setting, as discussed in my Multiuser apps. paper. Try to make sure
that you are not ending up with table scans. If you see table scans in the
resulting Showplan.out files, you should consider applying indexes as needed.
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
Hi
Using ACCESS 2000
I have just completed a refresh of an existing DB that my users have been
happily using for 5 years, without too much complaint. The refresh was
mainly look and feel of the forms and reports.
Context:
I undertook the dev work in 2007 though saved the DB in 2000 (the version of
my users)
The DB only has some 6,000 records (tables DB is 11 MB, Forms, Queries,
Report DB is 1.5 MB)
The DB has max of 8 users at any one time
Problem:
When I undertake the Ctl F command in the test lab (ie my home) - it works
exactly as it should - quickly and accurately finds a record)
When my users try the same thing it can take up to a minute for a searched
for record to return (obviously unacceptable)
Where do I begin to resolve this issue? (ps they have compacted and
repaired the DB)
TIA
--
Sue Compelling |
|
| |
| | | Tom Wickerath | |
| | Posted: Sat Sep 06, 2008 4:42 am Post subject: RE: Search funtion .... | |
| | | |
Hi Sue,
| Quote: | b) No - my home environment doesn't replicate theirs (as I don't use a
network)
|
In that case, any speed comparison tests of what you get at home versus what
your clients get in their office is an apples and oranges comparison.
| Quote: | c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)
|
What changed between your old version and your newer version? Make sure that
they start with a compacted database (both FE & BE), as compacting will cause
all query plans to be reset. Access will determine the best query plan the
next time each query is run, immediately after a compact and repair. In this
way, you will have the most efficient query plan for the number of records in
each table.
| Quote: | Could the zipping and emailing be part of the problem?
|
I can't imagine how the manager could have done something wrong
here....unless they set everyone up to share a common FE file on the file
server. Is it possible for you to visit this location in person?
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
| Quote: | Hi Tom
Appreciate the response and indeed the links to QBF 'training tools'. Have
now saved this to my favs
In response to your questions;
a) Yes - the App is comprised of FE and BE DBs
b) No - my home environment doesn't replicate theirs (as I don't use a
network)
c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)
A question - Rather than me deploying this myself - I did zip and email the
FE to the Manager and talked them through the download and linking (and
copying the master to their local drives etc). Could the zipping and
emailing be part of the problem?
TIA
--
Sue Compelling |
|
| |
| | | Sue Compelling | |
| | Posted: Sat Sep 06, 2008 4:51 am Post subject: RE: Search funtion .... | |
| | | |
Hi Tom
Take your point on issue a)
And yes - I think I will drive in to deploy.
The difference between the old FE and the new FE was almost solely around
look and feel of the forms and reports, though of note is that I undertook
the dev work in 2007 though saved the DB in 2000 (the version of my users).
Would this conribute to any problems?
--
Sue Compelling
"Tom Wickerath" wrote:
| Quote: | Hi Sue,
b) No - my home environment doesn't replicate theirs (as I don't use a
network)
In that case, any speed comparison tests of what you get at home versus what
your clients get in their office is an apples and oranges comparison.
c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)
What changed between your old version and your newer version? Make sure that
they start with a compacted database (both FE & BE), as compacting will cause
all query plans to be reset. Access will determine the best query plan the
next time each query is run, immediately after a compact and repair. In this
way, you will have the most efficient query plan for the number of records in
each table.
Could the zipping and emailing be part of the problem?
I can't imagine how the manager could have done something wrong
here....unless they set everyone up to share a common FE file on the file
server. Is it possible for you to visit this location in person?
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
Hi Tom
Appreciate the response and indeed the links to QBF 'training tools'. Have
now saved this to my favs
In response to your questions;
a) Yes - the App is comprised of FE and BE DBs
b) No - my home environment doesn't replicate theirs (as I don't use a
network)
c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)
A question - Rather than me deploying this myself - I did zip and email the
FE to the Manager and talked them through the download and linking (and
copying the master to their local drives etc). Could the zipping and
emailing be part of the problem?
TIA
--
Sue Compelling |
|
| |
| | | Tom Wickerath | |
| | Posted: Sat Sep 06, 2008 5:09 am Post subject: RE: Search funtion .... | |
| | | |
Hi Sue,
It's always best to develop using the lowest common denominator version of
Access. In that way, you don't inadvertently use functionality that is not
supported in an earlier version.
You might want to try talking the manager (or do it yourself, when you do go
on-site) into decompiling the VBA code in your project, compacting, and then
re-compiling.
I forgot to ask earlier, but do you have relationships established between
the tables in the BE database, with enforced referential integrity (RI)?
As long as you will be on site soon, I highly recommend that you become
familiar with Access MVP Tony Toews free AutoFE Updater utility. This will
make it breezy-easy for you to deploy updated FE's in the future.
LINK
LINK
Do spend some time using JETShowPlan, using your home test lab. With this
registry flag set, use each command button on a switchboard that a user might
click on. Do some searches using your <Ctrl><F> method. Then open the
resulting Showplan.out file using your favorite text editor, and search for
the word "scan". Don't become too concerned with table scans against small
tables, as scanning may be the most efficient method in that case. However,
if the table has more than, say, 100 records or so, I'd start to get
concerned about seeing table scans. Also, keep in mind that table scans
against lookup tables, for example the rowsource for a combo box, are
perfectly fine. After all, the combo box likely displays all records in the
lookup table.
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
| Quote: | Hi Tom
Take your point on issue a)
And yes - I think I will drive in to deploy.
The difference between the old FE and the new FE was almost solely around
look and feel of the forms and reports, though of note is that I undertook
the dev work in 2007 though saved the DB in 2000 (the version of my users).
Would this conribute to any problems?
--
Sue Compelling |
|
| |
| | | Sue Compelling | |
| | Posted: Sat Sep 06, 2008 5:43 am Post subject: RE: Search funtion .... | |
| | | |
Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.
I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.
Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.
That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).
In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")
TIA
--
Sue Compelling
"Tom Wickerath" wrote:
| Quote: | Hi Sue,
It's always best to develop using the lowest common denominator version of
Access. In that way, you don't inadvertently use functionality that is not
supported in an earlier version.
You might want to try talking the manager (or do it yourself, when you do go
on-site) into decompiling the VBA code in your project, compacting, and then
re-compiling.
I forgot to ask earlier, but do you have relationships established between
the tables in the BE database, with enforced referential integrity (RI)?
As long as you will be on site soon, I highly recommend that you become
familiar with Access MVP Tony Toews free AutoFE Updater utility. This will
make it breezy-easy for you to deploy updated FE's in the future.
LINK
LINK
Do spend some time using JETShowPlan, using your home test lab. With this
registry flag set, use each command button on a switchboard that a user might
click on. Do some searches using your <Ctrl><F> method. Then open the
resulting Showplan.out file using your favorite text editor, and search for
the word "scan". Don't become too concerned with table scans against small
tables, as scanning may be the most efficient method in that case. However,
if the table has more than, say, 100 records or so, I'd start to get
concerned about seeing table scans. Also, keep in mind that table scans
against lookup tables, for example the rowsource for a combo box, are
perfectly fine. After all, the combo box likely displays all records in the
lookup table.
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
Hi Tom
Take your point on issue a)
And yes - I think I will drive in to deploy.
The difference between the old FE and the new FE was almost solely around
look and feel of the forms and reports, though of note is that I undertook
the dev work in 2007 though saved the DB in 2000 (the version of my users).
Would this conribute to any problems?
--
Sue Compelling |
|
| |
| | | Tom Wickerath | |
| | Posted: Sat Sep 06, 2008 6:08 pm Post subject: RE: Search funtion .... | |
| | | |
Hi Sue,
I had called it a night after my last reply--it was after midnight for me,
so I just read your reply shown below.
| Quote: | Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.
|
Maybe, but maybe not....
| Quote: | I changed 2 fields on the form (which were populating using queries) into
lookups.
|
I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box? Are you pulling only the required data for the combo box?
I've often times seen people use rowsources for combo boxes that look
something like this:
SELECT * FROM [TableName] ORDER BY [FieldName]
when all they *really* needed was one or two fields from the table involved.
However, by using the SELECT * syntax, they brought ALL fields in each record
over the wire.
| Quote: | Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.
|
I'm pretty much self taught as well. What parts of my replies have seemed
scary to you?
| Quote: | I think I'm supposed to use the Nz function?
|
The Nz function can be used to convert a null (unknown) to something else,
such as 0 (zero), or a zero length string (ZLS) --> "". A null plus anything
else is still a null, so I think you can use the idea of null concatentation
here, if the fields are truly null (and not ZLS). Try something like this (I
didn't attempt to test it):
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] + (" H: "
& Format([homephone],"(00) 000-0000")) + (" W: " &
Format([workphone],"(00) 000-0000" & ", " & [workextension]) + (" M: " &
Format([mobilephone],"(000) 000-0000"))
Tom Wickerath
Microsoft Access MVP
LINK
LINK
__________________________________________
"Sue Compelling" wrote:
| Quote: | Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.
I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.
Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.
That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).
In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")
TIA
--
Sue Compelling |
|
| |
| | | Tom Wickerath | |
| | Posted: Sat Sep 06, 2008 7:11 pm Post subject: RE: Search funtion .... | |
Let me try that again:
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType]
& (" H: " + Format([homephone],"(00) 000-0000"))
& (" W: " + Format([workphone],"(00) 000-0000" + ", " + [workextension]))
& (" M: " + Format([mobilephone],"(000) 000-0000"))
You might also try something like this, to get the phone numbers to print on
different lines:
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType]
& (Chr(13) + Chr(10) + " H: " + Format([homephone],"(00) 000-0000"))
& (Chr(13) + Chr(10) + " W: " + Format([workphone],"(00) 000-0000" + ", "
+ [workextension]))
& (Chr(13) + Chr(10) + " M: " + Format([mobilephone],"(000) 000-0000"))
Tom Wickerath
Microsoft Access MVP
LINK
LINK |
| |
| | | Sue Compelling | |
| | Posted: Sun Sep 07, 2008 12:58 am Post subject: RE: Search funtion .... | |
| | | |
Hi Tom
Unfortunately this presents a blank field where nothing populates - but it's
fixed now from Marshall using another post:
( .... & Format([homephone]," \H\: (@@) @@@-@@@@") & Format([workphone],"
\W\: (@@) @@@-@@@@") & (" X" +
[workextension]) & Format([mobilephone]," \M\: (@@@) @@@-@@@@")
Appreciate your response regardless. And midnight - bedtime!!!! surely the
night has just begun.
Will respond to your questions using your other reply.
--
Sue Compelling
"Tom Wickerath" wrote:
| Quote: | Let me try that again:
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType]
& (" H: " + Format([homephone],"(00) 000-0000"))
& (" W: " + Format([workphone],"(00) 000-0000" + ", " + [workextension]))
& (" M: " + Format([mobilephone],"(000) 000-0000"))
You might also try something like this, to get the phone numbers to print on
different lines:
Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType]
& (Chr(13) + Chr(10) + " H: " + Format([homephone],"(00) 000-0000"))
& (Chr(13) + Chr(10) + " W: " + Format([workphone],"(00) 000-0000" + ", "
+ [workextension]))
& (Chr(13) + Chr(10) + " M: " + Format([mobilephone],"(000) 000-0000"))
Tom Wickerath
Microsoft Access MVP
LINK
LINK |
|
| |
| Page 1 of 4 .:. Goto page 1, 2, 3, 4 Next | |
|
|
