• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1745
  • Last Modified:

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
0
dataflowjoe
Asked:
dataflowjoe
2 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now