Your opinion on ADP

[ions]

What is the Access community's opinion of ADP ? I have been considering creating a Access project for SQL Server 2008. I have been searching for books on Access Projects but nothing has been published on this topic since 2002.

Also I have been getting mixed opinions on the number of concurrent users a split access desktop application can handle.

According to some books if your Data is under 200 MB and tables are well designed you can get up to 20 concurrent users using bound forms. With unbound forms you can get over 50 concurrent users.

Thanks.

 

[TexanInParis]

ADPs are dead technology. The feature hasn't been updated since 2002, so they don't really support SQL Server 2005 and later. They can be handy for providing a GUI to manage table / SP / Function designs, but you'll get some annoying error messages (all bugs) if you use it with SQLS 2005 or 2008.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[boblarson]

And my opinion, since you asked, is fairly straightforward - I have not used them. I have always used MDB/ACCDB files for connecting to SQL Server.

 

[TexanInParis]

I agree, Bob. Microsoft now recommends using Access with linked tables to use Access as a front end to SQL Server. They've all but abandoned ADPs.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[ions]

That would explain why the ADP chapter is only covered in the accompanying CD in your Inside Out 2007 book.

What books do you recommend for building enterprise applications (50 users, 500 MB of data) using Access as the Front End and SQL Server 2008 for backend?

Perhaps I should first build a solution using Access desktop application (MDB) and then upsize to SQL Server? I don’t have any experience with SQL server other than installing the desktop version SQL Server 2000 and playing around with ADPs.

Thank you

Peter.

 

[TexanInParis]

Peter-

I would start with Microsoft Access Developer's Guide to SQL Server by Andy Baron and Mary Chipman. http://www.amazon.com/dp/0672319446?tag=viescaconsulinc Although written for Access 2000, it's chock-full of good advice that's still applicable. As I browse around on Amazon, SQL: Access to SQL Server (Harkins, Reid), and From Access to SQL Server (Sinclair) also look promising, but I haven't had a chance to personally look at either of those books.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[ions]

Thank you for the Advice.

I took a look at the books you mentioned a while back but was reluctant to pick them up due to the published date 2002 or later. (I did pick yours up partly because it was published 2005)

John yesterday I had an interesting discussing about the use of ADP in the following thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=177871

BobMcClallen has had success by combining ADP and SSMS for SQL Server 2005.

However since you mention Microsoft has abandoned ADPs, MDB with ODBC links is most likely a safer choice for the future.

I am now realizing I will have to learn a new development environment SSMS. I am assuming if I use MDB linked to SQL I can keep using DAO and don’t have to learn ADO? Finally, I probably won’t be using DAO too much since I can do most of what DAO does using Stored Procedures?

Should one of my next immediate steps be to start learning SSMS for SQL Server 2008 and learning how to write Stored Procedures as well as buying Microsoft Access Developer's Guide to SQL Server by Andy Baron to understand how Access and SQL server interact?

Thank you for everyone’s advice: BobLarson, Banana, BobMcClallen and TexanInParis. I am very happy to be getting such quality advice from the top experts and am grateful.

Sincerely,

Peter.

 

[TexanInParis]

Peter-

<< MDB with ODBC links is most likely a safer choice for the future. >>

Yes.

<< I am assuming if I use MDB linked to SQL I can keep using DAO and don’t have to learn ADO? >>

Well, it's a mixed bag. All form and report recordsets are DAO, but you should probably use ADO and the Connection object when manipulating recordsets in code. Bill Mosca, another MVP who runs the MS_Access_Professionals forum over on Yahoo, swears by ADO to do DDL stuff for both Access and SQL Server. So, you should probably learn ADO, but you can get by with DAO.

<< Should one of my next immediate steps be to start learning SSMS for SQL Server 2008 and learning how to write Stored Procedures as well as buying Microsoft Access Developer's Guide to SQL Server by Andy Baron to understand how Access and SQL server interact? >>

Yes. If you're using SQL Server 2005 or later, it'll be better to manipulate the server objects (tables, views, functions, SPs) using the SQLS tools.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[Banana]

To clarify.

Jet/DAO can invoke stored procedure as well other native dialects of any ODBC sources via passthrough queries. However it's rare for me to use passthrough queries I usually think about using queries to produce a updatable recordset that I can bind to form. With this in mind, I just create a standard Access saved query which itself is fairly simple:

SELECT blah, blah
FROM some table
WHERE some criteria

which I then use the recordsource. In those cases, I follow to SQL standard as closely as I can, so the SQL has to be understood by both Jet and the ODBC backend of choice.

Remember that when you create a query that is going to be a recordsource, you have some certain rules about how you can write it and still be updatable. For most purposes, this excludes any kind of aggregating, unioning, distinct and several instances of joins. The general rule would be that there has to be one-to-one correspondence between the query's row to the base table's row. If a table's row appears twice or more then it's very likely it's not updatable. It's more complicated than that but that's a good rule to keep in mind.

In few cases where I actually need more complex statement that may not be understood by Jet but is understood by backend or use stored procedure and still want to bind the result to form *and* have it updatable still, then I run over to ADO to do the whole thing. An example where I needed this was to implement a paging mechanism for users to scroll through the recordset in pages, something that would be not as practical in DAO.

 

[ions]

Thanks John & Banana,

Banana I will read your article later today after work.

Peter.