Tim-
If your application is slow using Access in a shared back end, it's probably not going to improve much (might even get worse) to move the back end to SQL Server without doing some "tuning" on the front end to minimize network traffic. Some of the key things to do to optimize a client/server application include (from Building Microsoft Access Applications):
1) Never open a form unfiltered. All my client/server apps pop open a criteria form for the user to select / filter the data needed to do the task at hand. Do they really need to look at all clients when all they want is the ones in a certain city?
2) Never open a report unfiltered. Same advice as above for forms.
3) Avoid use of combo boxes that return more than 100 rows. Instead, consider using unbound text boxes where the user can type what they think they need and your code pops open a filtered list to make the final selection.
4) Pull data into a local table for complex reports - a report often makes two or more passes at the data to format correctly. Much better to run the complex query once against the server to dump into a local table, then base the report on that.
5) Avoid multiple subforms on a form. Consider using a "fake tab" control (a series of option buttons will do the trick) and load the requested subform on demand into a single subform control.
6) If much of your lookup data is static, consider loading that data into a local table on startup. You can use regular combo / list boxes for lookups of large lists if the data is local.
7) When using an Access back end, declare and open a recordset on any linked table when the app starts and leave the recordset open until the app exits. This allows access to share the already created thread to the server rather than opening a new thread (lots of overhead) every time you open a form or report.
The last "tip" can often make a huge difference. You should try that first before making any other drastic changes to your application.
As for why to move to SQL Server - the number 1 reason is to get good security. There's no way to secure a purely Access database application. Other reasons include huge data tables and the ability to "pre-program" some stuff using Functions and Stored Procedures in SQL Server. However, if you're "no programmer", you might find working with advanced stuff on SQL Server beyond you.
Good luck!
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of timdbui@gmail.com
Sent: Monday, December 09, 2013 4:29 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Advice on SQL Server on back end
My IT department finally agreed to let me have SQL Server 2012 to work on the back end. I will continue to keep Access as the front end because of its ease in creating reports and forms. I have a few questions if any of you could help:
1. Right now I distributed the front ends to my team mates and put the back end on the company's server, but Access is extremely slow. By putting the back end into a SQL Server, would I gain performance speed?
2. Would I still have the same limitations of Access or would I have more features that belong to SQL Servers (larger file size, more numbers of data items in a query, more statistical functions,...)?
3. Over time, would I be better off learning another front end to create queries, forms and reports or should I continue to stick with Access (I am no programmer)?
Thanks in advance for your advice!
Tim
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar