Access 07 on Network - SLOW

[bline22]

Hello,
I have a small access database I am putting out on a network share for myself and others to use. When I open it it can take a minute to do anything. I click on a form about 80 seconds later it opens. Query is nearly the same. I tried to do a split and it made it worse by at least double. I have looked for some methods to try and make it a bit quicker but many of the suggestions involve making changes on the server hosting the share which is not possible. Anyone have any suggestions in speeding this up?

The share is accessed via a VPN connection.
Clients windows 7.
Shared drive is NTFS on windows 2003. that is about all i can gather concerning the share.

Any help would be appreciated.

Thanks.

 

[HiTechCoach]

I think it is probably the way you are using the VPN/WAN that is causing your issues.

A typical VPN/WAN over the internet normally is at least 50 to 100 times slower that a 100 mbs local network connection.

See:
Remote access for Access Applications

 

[bline22]

Thanks for the reply and that was my gut feeling.

I saw replication on your list. Is it possible to run the database locally and have a close event sync the data up to the table on the vpn share? Not sure how it would handle the same record being updated by more then one person though.

 

[HiTechCoach]

Thanks for the reply and that was my gut feeling.

I saw replication on your list. Is it possible to run the database locally and have a close event sync the data up to the table on the vpn share? Not sure how it would handle the same record being updated by more then one person though.

When the same record is modified on two separate replicates (remote copies), when the sync happens, you must have to some way handle this manually or with VBA code. The possible los of changes is very high.

Replication works better when user are adding data. Also the data tends to not be modified on other replicas (other remote copies)

Does not sounds like replication would be a good fit for your needs. Also Replication does not tend to work well over the internet.

Thin Client Technology works great for how you use the data.

Note: I work 99% of the time via Thin Client. I am posting now from a Terminal Server Session.

 

[dfenton]

@HiTechCoach:

Where do you get the idea that Jet replication is not suitable for updates in multiple locations? It definitely is, though it can cause problems if you have certain kinds of updates. For instance, a single record updated in multiple locations is going to be a problem, but I have a very hard time conceiving of a schema, application and business rules that would lead to that in a way that is not easily engineered away with a transactional structure.

 

[dfenton]

Replication works fine with synchs across a VPN if you use INDIRECT replication. However, that is much hard to set up and maintain that plain vaniall DIRECT replication (the kind you get from the Access UI).

See the Jet Replication Wiki for everything you always wanted to know about Jet Replication:

http://dfenton.com/DFA/Replication/

Note that even though I'm quite experienced with Jet replication, in the situation you describe, with an active VPN already in place, I'd recommend Windows Terminal Server to host your app in one location, and the users at the other locations would run it via Remote Desktop. This is probably cheaper and easier to maintain than setting up Jet indirect replication.

 

[HiTechCoach]

@HiTechCoach:

Where do you get the idea that Jet replication is not suitable for updates in multiple locations? It definitely is, though it can cause problems if you have certain kinds of updates. For instance, a single record updated in multiple locations is going to be a problem, but I have a very hard time conceiving of a schema, application and business rules that would lead to that in a way that is not easily engineered away with a transactional structure.

I never said it was not suitable for multiple locations.

 

[dfenton]

I never said it was not suitable for multiple locations.

You did write this:

When the same record is modified on two separate replicates (remote copies), when the sync happens, you must have to some way handle this manually or with VBA code. The possible los of changes is very high.

Replication works better when user are adding data. Also the data tends to not be modified on other replicas (other remote copies)

I don't think either of your major premises the is strictly true. Loss of data from conflicts is not very common at all -- indeed, you have to ignore your conflict resolution in order to lose data. That is, in any synch, Jet resolves the conflict, and one edit wins. Then Jet saves the losing record to the appropriate conflict table and informs you of the conflict so that you can check to see if Jet made the right choice. If it didn't, you can roll it back.

So, I don't see how there is any possible loss of data there.

You secondly suggest that "replication works better...adding data...not modified on other replicas." That seems to me to be equivalent to what I said you asserted, which is "Jet replication is not suitable for updates in multiple locations". OK, I guess that's stronger than what I read you as saying, but it's wrong either way.

All that said, replication is something that only an expert Access users should delve into, in my opinion. I've made all the mistakes with it you can possibly make, and it took me years to adjust my conceptual map of how I thought it should work to correspond to how it actually works. I keep learning things even now, after 13 years of building replicated Access apps.