Taking my application/Access 2009 further...

[Peter]

I have developed an Access 2009 application. 12 Users. Frontend on local PC’s
(12mb) and Backend on a shared drive (8mb). Excellent performance while
distributed via local LAN but very poor performance distributed via
VPN/remote. Where do I go from here, what options are available in order to
increase performance?
SQL server?
..NET?

Thankful for all suggestions!

 

[Larry Linson]

What you experience is not surprising. Jet and ACE are file server databases
and require a high-speed connection, such as a LAN, to perform acceptably.
Access and .NET do not interact, though you can use a Jet or ACE database
with .NET (that would require a complete rewrite of your application).

Very likely a VPN with Microsoft Terminal Services (based on Citrix) or
Citrix itself is the easiest, but not necessarily he least expensive
approach.

You can backend your app with MS SQL Server (the express edition is free,
and increases your DB size to 4GB), or any ODBC-compliant server database
including MySQL and PostgreSQL. I've used Access front-ends with MS SQL
Server, Sybase SQL Server, Sybase SQL Anywhere, and Informix all with good
results.

Access 2010 adds the option of SharePoint as a data store, but performance
is not the strong point in such a lash-up, not yet, at least.

Larry Linson
Microsoft Office Access MVP

 

[Peter]

Thank you Larry, very valuable advice.

I will take into consideration storing the backend in a SQL format on the
company server..However..the distribution outside the LAN/WAN will still be
via Cetrix/VPN..do I need a lot of “rewriting†of the backend in order to
conform to SQL?

Thanks again!

 

[John W. Vinson]

I will take into consideration storing the backend in a SQL format on the
company server..However..the distribution outside the LAN/WAN will still be
via Cetrix/VPN..do I need a lot of “rewriting” of the backend in order to
conform to SQL?

You may need to make some changes for performance; for instance, you should
avoid basing a Form on a Table or on a query returning a large number of
records. Instead you should have your forms' recordsources retrieve one or a
very few records dynamically, to pervent Access hitting the server for
thousands of records that aren't actually needed.

But even without that, if you now have a split database, it's pretty
straightforward to change the links from an Access/JET backend to a SQL
backend, using ODBC.

 

[David W. Fenton]

Access 2010 adds the option of SharePoint as a data store, but
performance is not the strong point in such a lash-up, not yet, at
least.

I'm not sure you're right there, Larry. I'm pretty sure A2003
supported Sharepoint lists, as does A2007, so it's not some
brand-new feature of A2010.

Also, according to the Access team blog (and Albert Kallal), a huge
amount of work was done on improving performance with large
Sharepoint lists. Albert has said it's a huge change.

So, given that they've also added basic referential integrity in
Sharepoint 2010, I'm not sure Sharepoint has any deficiencies any
longer that make it a problem. Well, other than the fact that you
have to have a Sharepoint server, either in-house or hosted online.

 

[Peter]

Gentlemen,

Mr. Linson, Mr. Vinson and Mr. Fenton,

thank you all for some very valuable suggestions. I will proceed testing the
following solutions.
1. SQL
2. SharePoint (we do have a SharePoint server someware around)

Once again

Thanks!