Form performance EXTREMELY slow

[jgrout]

I just upgraded to Access 2003 from Access 97, and my form performance
completely tanked (I am accessing the database on a network drive). I found
a fix on Microsoft that seemed to work great, until I restarted my database.
The fix was to change all the [Auto] values to [None], using a Macro provided
by Microsoft. I thought perhaps the Name Auto Correct was turned on, but it
wasn't, and I was able to verify that the values that had been changed to
[None] were still [None]. I found one line in my project that still had
[Auto] in it, it was the line rplpropValue = "[Auto]". I changed this to
[None] and problem was solved--until I restarted my database. As before, the
database was so slow that it is impossible to do anything in it. I checked
the code and the value was still set to [None]. Now here's where it gets
confusing--just to see what would happen, I changed the line back to
rplpropValue = "[Auto]" and all of a sudden my database started functioning
again. Until next time I started my database up--slow, slow again. So I
changed it back to [None] and it worked fine--until next time. So now, every
time I start the database I have to go into the code and change the
rplpropValue line to opposite of what I had in my previous session. I need a
better fix as other users will soon be accessing the database and certainly
can't be going into the code to make changes--can someone help?

 

[Allen Browne]

If your tables are attached (i.e. the data is actually in a different Access
database), open the back end database and ensure Name AutoCorrect is off and
Subdatasheet Name is "[None]" for all tables in the back end also. Then
compact/repair the back end, and finally compact/repair the front end.

For more suggestions, go to Tony Toews' Performance FAQ for Access:
http://www.granite.ab.ca/access/performancefaq.htm
Work through *each* of the items in Tony's list.

 

[jgrout]

My tables are not attached, I only have one small database. I've been able
to determine that Subdatasheet property setting has nothing whatsoever to do
with my issues. In fact, I've found out that if I go into my code and change
anything, even adding a comment, my form functionality improves dramatically
and I can work again. Want to hear something even stranger?--when I go into
the code and DON'T change anything my form slows down to a crawl again!
Here's exactly what I did: (1) went into design view of form, (2) went to
View-->Code, (3) added the line 'test, (4) saved my form. Problem solved,
the form is working fine. So then I repeated the steps above except for #3
and then my form slowed down to a snails pace again. So I went back in and
did all above steps again, this time added the number "2" after the word
test, and my form is working fine again. Of course, next time I start my
database it will be as slow as a snail until I go back and change something
in the code--help! I reviewed the stuff on the site you recommended below,
but most of it is over my head and seems to be for larger databases. I am a
fairly basic user with enough knowledge of VB to get what I want, but no
knowledge of SQL. Thanks again!

If your tables are attached (i.e. the data is actually in a different Access
database), open the back end database and ensure Name AutoCorrect is off and
Subdatasheet Name is "[None]" for all tables in the back end also. Then
compact/repair the back end, and finally compact/repair the front end.

For more suggestions, go to Tony Toews' Performance FAQ for Access:
http://www.granite.ab.ca/access/performancefaq.htm
Work through *each* of the items in Tony's list.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I just upgraded to Access 2003 from Access 97, and my form performance
completely tanked (I am accessing the database on a network drive). I
found
a fix on Microsoft that seemed to work great, until I restarted my
database.
The fix was to change all the [Auto] values to [None], using a Macro
provided
by Microsoft. I thought perhaps the Name Auto Correct was turned on, but
it
wasn't, and I was able to verify that the values that had been changed to
[None] were still [None]. I found one line in my project that still had
[Auto] in it, it was the line rplpropValue = "[Auto]". I changed this to
[None] and problem was solved--until I restarted my database. As before,
the
database was so slow that it is impossible to do anything in it. I
checked
the code and the value was still set to [None]. Now here's where it gets
confusing--just to see what would happen, I changed the line back to
rplpropValue = "[Auto]" and all of a sudden my database started
functioning
again. Until next time I started my database up--slow, slow again. So I
changed it back to [None] and it worked fine--until next time. So now,
every
time I start the database I have to go into the code and change the
rplpropValue line to opposite of what I had in my previous session. I
need a
better fix as other users will soon be accessing the database and
certainly
can't be going into the code to make changes--can someone help?

 

[Allen Browne]

Sounds like your database needs a compact, decompile, compact, compile
sequence.

Try the steps in:
Recovery sequence - Standard steps to recover a database
at:
http://allenbrowne.com/recover.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My tables are not attached, I only have one small database. I've been
able
to determine that Subdatasheet property setting has nothing whatsoever to
do
with my issues. In fact, I've found out that if I go into my code and
change
anything, even adding a comment, my form functionality improves
dramatically
and I can work again. Want to hear something even stranger?--when I go
into
the code and DON'T change anything my form slows down to a crawl again!
Here's exactly what I did: (1) went into design view of form, (2) went to
View-->Code, (3) added the line 'test, (4) saved my form. Problem solved,
the form is working fine. So then I repeated the steps above except for
#3
and then my form slowed down to a snails pace again. So I went back in
and
did all above steps again, this time added the number "2" after the word
test, and my form is working fine again. Of course, next time I start my
database it will be as slow as a snail until I go back and change
something
in the code--help! I reviewed the stuff on the site you recommended
below,
but most of it is over my head and seems to be for larger databases. I am
a
fairly basic user with enough knowledge of VB to get what I want, but no
knowledge of SQL. Thanks again!

If your tables are attached (i.e. the data is actually in a different
Access
database), open the back end database and ensure Name AutoCorrect is off
and
Subdatasheet Name is "[None]" for all tables in the back end also. Then
compact/repair the back end, and finally compact/repair the front end.

For more suggestions, go to Tony Toews' Performance FAQ for Access:
http://www.granite.ab.ca/access/performancefaq.htm
Work through *each* of the items in Tony's list.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I just upgraded to Access 2003 from Access 97, and my form performance
completely tanked (I am accessing the database on a network drive). I
found
a fix on Microsoft that seemed to work great, until I restarted my
database.
The fix was to change all the [Auto] values to [None], using a Macro
provided
by Microsoft. I thought perhaps the Name Auto Correct was turned on,
but
it
wasn't, and I was able to verify that the values that had been changed
to
[None] were still [None]. I found one line in my project that still
had
[Auto] in it, it was the line rplpropValue = "[Auto]". I changed this
to
[None] and problem was solved--until I restarted my database. As
before,
the
database was so slow that it is impossible to do anything in it. I
checked
the code and the value was still set to [None]. Now here's where it
gets
confusing--just to see what would happen, I changed the line back to
rplpropValue = "[Auto]" and all of a sudden my database started
functioning
again. Until next time I started my database up--slow, slow again. So
I
changed it back to [None] and it worked fine--until next time. So now,
every
time I start the database I have to go into the code and change the
rplpropValue line to opposite of what I had in my previous session. I
need a
better fix as other users will soon be accessing the database and
certainly
can't be going into the code to make changes--can someone help?

 

[jgrout]

Solution found! I did the steps you said below, but it didn't solve my
problem. However, while going through the steps I noticed something
strange--when Access reopened my database after recompiling, it didn't seem
to have any of the slowness issues. And that's when I found my solution--I
had always opened my database by clicking on a shortcut on my taskbar that
referenced the file name on my shared drive. I have now discovered that if I
instead open Access first and THEN open my database from within Access, my
slowness issues are GONE! I have no idea why using a shortcut to open the
database would cause this, but after a couple of days of testing, I have
confirmed 100% that this was definitely the issue. Thanks for all your
helpful hints.

Sounds like your database needs a compact, decompile, compact, compile
sequence.

Try the steps in:
Recovery sequence - Standard steps to recover a database
at:
http://allenbrowne.com/recover.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My tables are not attached, I only have one small database. I've been
able
to determine that Subdatasheet property setting has nothing whatsoever to
do
with my issues. In fact, I've found out that if I go into my code and
change
anything, even adding a comment, my form functionality improves
dramatically
and I can work again. Want to hear something even stranger?--when I go
into
the code and DON'T change anything my form slows down to a crawl again!
Here's exactly what I did: (1) went into design view of form, (2) went to
View-->Code, (3) added the line 'test, (4) saved my form. Problem solved,
the form is working fine. So then I repeated the steps above except for
#3
and then my form slowed down to a snails pace again. So I went back in
and
did all above steps again, this time added the number "2" after the word
test, and my form is working fine again. Of course, next time I start my
database it will be as slow as a snail until I go back and change
something
in the code--help! I reviewed the stuff on the site you recommended
below,
but most of it is over my head and seems to be for larger databases. I am
a
fairly basic user with enough knowledge of VB to get what I want, but no
knowledge of SQL. Thanks again!

If your tables are attached (i.e. the data is actually in a different
Access
database), open the back end database and ensure Name AutoCorrect is off
and
Subdatasheet Name is "[None]" for all tables in the back end also. Then
compact/repair the back end, and finally compact/repair the front end.

For more suggestions, go to Tony Toews' Performance FAQ for Access:
http://www.granite.ab.ca/access/performancefaq.htm
Work through *each* of the items in Tony's list.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I just upgraded to Access 2003 from Access 97, and my form performance
completely tanked (I am accessing the database on a network drive). I
found
a fix on Microsoft that seemed to work great, until I restarted my
database.
The fix was to change all the [Auto] values to [None], using a Macro
provided
by Microsoft. I thought perhaps the Name Auto Correct was turned on,
but
it
wasn't, and I was able to verify that the values that had been changed
to
[None] were still [None]. I found one line in my project that still
had
[Auto] in it, it was the line rplpropValue = "[Auto]". I changed this
to
[None] and problem was solved--until I restarted my database. As
before,
the
database was so slow that it is impossible to do anything in it. I
checked
the code and the value was still set to [None]. Now here's where it
gets
confusing--just to see what would happen, I changed the line back to
rplpropValue = "[Auto]" and all of a sudden my database started
functioning
again. Until next time I started my database up--slow, slow again. So
I
changed it back to [None] and it worked fine--until next time. So now,
every
time I start the database I have to go into the code and change the
rplpropValue line to opposite of what I had in my previous session. I
need a
better fix as other users will soon be accessing the database and
certainly
can't be going into the code to make changes--can someone help?