Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-15
6
Medium Priority
?
1,621 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 600 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 58
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 900 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

927 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