Solved

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

Posted on 2013-01-15
6
1,376 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

810 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