June 21, 2013 at 9:37 pm
I am going to be creating numerous reports for a future client using MS Access (possibly 2007).
My client is running SQL Server (possibly 2005).
What are my options for running MS Access (front-end) with SQL Server (back-end)?
My client mentioned stored procedures and MS Visual Studio (version ?). I've only used MS Visual Studio with Reporting Services. Not MS Access.
So far, the only choices that I'm aware of are:
* ODBC
* OLE DB
* Pass through queries in MS Access
* Invoking stored procedures with pass through queries in MS Access
I suspect that I might need a specific connection string.
I need to mention that I haven't been to my future client's site yet.
Thanks in advance for any assistance.
Sincerely,
Mike
June 22, 2013 at 3:11 pm
We've been doing that very thing for about 20 years, starting with Access 2.0 Beta in 1994, and SQL Server 4.21. Although we do use other strategies from time to time, 95% of the time we use linked SQL Server tables in Access using ODBC. More recently we've been working with DNSless linked tables on some projects, but it still is ODBC. We've found that to be the fastest and most effective way to develop custom applications (by a factor of 2 to 4 or more depending on the kind of application). The strategy we use is to get it developed first and then test for performance issues afterward. We seldom find more than 5% of the queries or reports that need tuning. In those cases, we may resort to a pass-through query, or in some cases actually run a stored procedure from Access. And nearly all of the forms and reports we create are bound to a data source - I've only done a handful of unbound forms in those 20 years.
One option you didn't mention was that of using an ADP. That connects directly to SQL Server, but has the complication of having to do everything in stored procedures or views. We find that environment takes significantly longer to develop in. In addition, while it is available in 2007 and 2010, it has had no significant new functionality added since Access 2002, and it has been deprecated from Access 2013.
I've looked through a number of resources, and don't really find a good tutorial on getting started with Access front-ends to a SQL Server back-end, but http://www.mssqltips.com/sql-server-tip-category/57/microsoft-access-integration/ has several tips associated with how to go about it. There are some books on the subject - but my favorite one is now out of print. So post questions here or in one of the other Access forums and you should get on OK.
Wendell
Colorado, USA
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 5, 2013 at 12:43 pm
IMHO, ODBC connection is the easiest & quickest way to go. I've been doing nothing but Access front-ends to SQL for the last 15 years, and it can make for some quick development turn around. I've got functions out the wazoo for dealing with linked tables & connectivity, converting access queries to SQL views, user information, etc.
One thing to keep in mind is whether or not any other applications will be using the SQL data at the same time, specifically updating with record locking. Since Access keeps a static connection open, if another app is trying to lock a table, it will hang if Access has it open. If you have the time to create views on all of the tables that will reduce the problem.
Also, if you're using integrated security you can utilize user information on the Sql side to track usage. If you're doing more than just report development, move as much business logic as you can to sql. My rule of thumb is to do it in SQL if it can be done.
I'm going to stop rambling now. If you have any specific questions just holler.
Tad
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply