Solved

What is best ADP or ACCDB as front-end to SQL Server

Posted on 2013-01-15
6
1,440 Views
Last Modified: 2013-01-29
Hi Guys,

I've had a lot of experience developing Access ADP files connected to SQL Server directly. I'm very comfortable with it and have found it to be a great combination. I note that the ADP format is now being phased out slowly in favour of linked tables using DSN. I realise this does give me the benfit of also having local tables alongside the linked tables. The thing that concerns me are 'performance issues' with concurrent users etc. In the past I've linked to SQL Server using Access 97 and found it to be very slow even with unbound forms. I'd be pleased to hear of your experiences with Access ACCDB linked tables to SQLServer. Maybe things I should be aware of etc.

Thanks in advance for giving your time to this question.

Joe
0
Comment
Question by:dataflowjoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 200 total points
ID: 38777820
My choice would be ACCDB if you plan to stick with Access for any duration.

To my understanding MS is dropping support for ADPs.

See ADP Support and the Future here:
http://msdn.microsoft.com/en-us/library/office/jj618413.aspx

I personally have not had any performance issues in our databases using ACCDBs as the UI for SQL Back-ends.

That said, depending on your needs and resources, you might also consider .Net as your interface to SQL Server.  I have found that to be a very fast, powerful and secure combination.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38777858
<<
The thing that concerns me are 'performance issues' with concurrent users etc.
>>

One point, which you likely are aware of is that each user should have their own copy of the .accdb Front End on their own computer, versus sharing a single front-end on a network location.
0
 
LVL 57
ID: 38777866
It's really not an issue in terms of what your thinking.  While working with a ADP is nice, your still talking to SQL.  It's no different with ODBC; just that your taking a differnt path.

  Yes, technically there is another layer or two, but there's no real difference.

  You do not BTW need a DSN  As long as all the connect info is in the connection string, a DSN is not required.

Jim.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 77

Expert Comment

by:peter57r
ID: 38778322
To my mind the key point is that you are not going to have this choice when you next upgrade.
Access 2013 does not support ADPs so they are effectively dead as of now.
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 300 total points
ID: 38783023
Hi Joe,

We've been building complex Access MDB/ACCDB apps with SQL Server for years.  If you follow some guidelines, they perform very well, even over decent WAN connections.

We never used ADPs, primarily because of the lack of local tables, and they were too dependent on a certain version of SQL Server.

I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein
0
 
LVL 2

Author Closing Comment

by:dataflowjoe
ID: 38834128
Apologies for being late with my responses. I also found a good thread on the matter here http://www.dbforums.com/microsoft-access/1649830-adp-vs-accdb-linking-sql.html
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question