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

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
LVL 2
dataflowjoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
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
mbizupCommented:
<<
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

peter57rCommented:
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
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dataflowjoeAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.