Access Split - Performance

[Guest]

I developed a database application in Access 2000 and placed it on a network
shared drive. I was running into data corruption issues (but the speed was
fine) so I split the database into a FE/BE solution. The front-end on the
users hard drive, and the back-end still on the network shared drive. The
split solved my corruption issue, however now I have a big performance issue.
The front-end components are fast as expected, but it is brutally slow
anytime the back-end tables are accessed. I have tried the following common
fixes, which helped a bit, but it is still way too slow:

-established a persistent recordset connection by accessing a table that is
always open
-Set the sub datasheet Name property set to [None]
- Track name AutoCorrect is off.

It doesn't seem to be a hardware issue because the speed was fine before the
split. Something in splitting the database appears to have drastically
effected the performance.

Any help you can provide would be greatly appreciated,

Jay

 

[Mikal via AccessMonster.com]

Hi, Jay:

You probably already know this, but if you don't compact and repair both ends,
you have just doubled the size of your db by splitting it. Do a compact and
repair on each front end and on the back end if you haven't already done so
and see if that helps.

Mike

I developed a database application in Access 2000 and placed it on a network
shared drive. I was running into data corruption issues (but the speed was
fine) so I split the database into a FE/BE solution. The front-end on the
users hard drive, and the back-end still on the network shared drive. The
split solved my corruption issue, however now I have a big performance issue.
The front-end components are fast as expected, but it is brutally slow
anytime the back-end tables are accessed. I have tried the following common
fixes, which helped a bit, but it is still way too slow:

-established a persistent recordset connection by accessing a table that is
always open
-Set the sub datasheet Name property set to [None]
- Track name AutoCorrect is off.

It doesn't seem to be a hardware issue because the speed was fine before the
split. Something in splitting the database appears to have drastically
effected the performance.

Any help you can provide would be greatly appreciated,

Jay

 

[Joseph Meehan]

I developed a database application in Access 2000 and placed it on a
network shared drive. I was running into data corruption issues (but
the speed was fine) so I split the database into a FE/BE solution.
The front-end on the users hard drive, and the back-end still on the
network shared drive. The split solved my corruption issue, however
now I have a big performance issue. The front-end components are fast
as expected, but it is brutally slow anytime the back-end tables are
accessed. I have tried the following common fixes, which helped a
bit, but it is still way too slow:

-established a persistent recordset connection by accessing a table
that is always open
-Set the sub datasheet Name property set to [None]
- Track name AutoCorrect is off.

It doesn't seem to be a hardware issue because the speed was fine
before the split. Something in splitting the database appears to
have drastically effected the performance.

Any help you can provide would be greatly appreciated,

Jay

As noted do make sure you compact the databases, but I fear your problem
may be LAN performance. Access demands a great deal from a LAN.

 

[Joseph Meehan]

Joseph Meehan wrote:
...

As noted do make sure you compact the databases, but I fear your
problem may be LAN performance. Access demands a great deal from a
LAN.

Hit that send button too quick.

You may want to take a look at your split design. Any static tables
should be kept in the front ends. Static tables are usually look up tables
of data that seldom if ever changes. If you can move some of those to the
front end, it reduces the traffic.

 

[Guest]

Thank for the response. But, yes - I've been repairing and compacting the
database regularly.

Hi, Jay:

You probably already know this, but if you don't compact and repair both ends,
you have just doubled the size of your db by splitting it. Do a compact and
repair on each front end and on the back end if you haven't already done so
and see if that helps.

Mike

I developed a database application in Access 2000 and placed it on a network
shared drive. I was running into data corruption issues (but the speed was
fine) so I split the database into a FE/BE solution. The front-end on the
users hard drive, and the back-end still on the network shared drive. The
split solved my corruption issue, however now I have a big performance issue.
The front-end components are fast as expected, but it is brutally slow
anytime the back-end tables are accessed. I have tried the following common
fixes, which helped a bit, but it is still way too slow:

-established a persistent recordset connection by accessing a table that is
always open
-Set the sub datasheet Name property set to [None]
- Track name AutoCorrect is off.

It doesn't seem to be a hardware issue because the speed was fine before the
split. Something in splitting the database appears to have drastically
effected the performance.

Any help you can provide would be greatly appreciated,

Jay

 

[Albert D.Kallal]

As a general rule, I do find the persistent connection does bring
performance back up to normal.

So, a few things:

-established a persistent recordset connection by accessing a table that
is
always open

double check the above is in fact the case.

-Set the sub datasheet Name property set to [None]
- Track name AutoCorrect is off.

good, and good...

I would also suggest you try using a mde file on each machine.

It doesn't seem to be a hardware issue because the speed was fine before
the
split. Something in splitting the database appears to have drastically
effected the performance.

Make sure there no un-linked tables, or tables linked to no where.

Also check default printers - especially if they are network ones..and don't
exist...

All other things remaining the same, if performance was fine before the
un-split..then one should be albe to get decent performance after the split.

So, try a mde file on a computer...and double check the persistent
connection....

 

[Tony Toews]

-established a persistent recordset connection by accessing a table that is
always open
-Set the sub datasheet Name property set to [None]
- Track name AutoCorrect is off.

Have you tried any of the other tips at Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm ?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

As a general rule, I do find the persistent connection does bring
performance back up to normal.

So, a few things:

double check the above is in fact the case.

A good way of testing this is to ensure you are the only one opening
the back end. Then run the database in your font end until it gets to
the main menu. At this point you should see an LDB file present on
the server with the same name as the back end.

If you don't see this LDB file then you know you don't have a
persistent connection.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm