Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running MS Access with SQL Server


Running MS Access with SQL Server

Author
Message
mikemartineac
mikemartineac
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 40
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
WendellB
WendellB
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1634
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!
kcspud5759
kcspud5759
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 278
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search