If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Taking my application/Access 2009 further...
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! |
#2
|
|||
|
|||
Taking my application/Access 2009 further...
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" wrote in message news 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! |
#3
|
|||
|
|||
Taking my application/Access 2009 further...
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! "Larry Linson" wrote: 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" wrote in message news 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! . |
#4
|
|||
|
|||
Taking my application/Access 2009 further...
On Thu, 15 Apr 2010 03:03:01 -0700, Peter
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Taking my application/Access 2009 further...
"Larry Linson" wrote in
: 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#6
|
|||
|
|||
Taking my application/Access 2009 further...
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! "David W. Fenton" wrote: "Larry Linson" wrote in : 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ . |
Thread Tools | |
Display Modes | |
|
|