[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


adp vs mdb and SQL Sever

Posted on 2002-06-12
Medium Priority
Last Modified: 2013-12-05
Hello, I moving the tables of an access application to SQL Server.  I am not sure whether link tables using ODBC with an mdb file or try the adp route.  I don't know the pro's and con's.  Any advice?

Thank you
Question by:BBrian
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 10

Expert Comment

ID: 7073899
I haven't used adp but I've linked via ODBC to a table with 80,000 records and not had any problems with running queries or reports against it.

Expert Comment

ID: 7073969
If you are moving them to SQLServer - why are using linked tables or ADP (what is ADP)?   importing them into SQL is relatively simple - what is the purpose of the move and what kind of front-end are you using to access the data?

Author Comment

ID: 7076260
I got the following quote from...


A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB component architecture. Using an Access project, you can create a client/server application as easily as a file server application. This client/server application can be a traditional solution based on forms and reports, or a Web-based solution based on data access pages, or a combination of both. You can connect the Access project to a remote SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop engine.
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!


Expert Comment

ID: 7076412
Thanks - makes sense.  I would steer clear of both.  If new versions, upgrades etc come you don't have to worry about compatability of Access or installations or if you move to a web interface etc..  I would just stick with ADO or DAO and use what ever for a front end --access or vb or web - all of which can use ADO/DAO etc. and not have to worry about a "carrier" mechanism such as linked tables, or an adp which may become dated.

Accepted Solution

Carlovski earned 400 total points
ID: 7083961
The ADP route does make things quite easy, and provides you with a nice interface to work on the database through access, particularly if you are used to access and not sql server tools like query analyser. It makes binding forms to tables and stored procedures quite easy, but there lies one of the cons- You probably don't want to be binding your forms and controls - this will create a connection for the duration of the form being open, and it becomes very easy to fall into the trap of developing in the access 'get everything, then filter it' style.
You really are better off fetching only the data you need, and then closing the connection. You will need to do this yourself anyway, so this makes the ADP option less appealing. It is also very easy to fall into the trap of including access functions, or using access specific sql in your queries. This will transfer the load from the server (Where it should be!) to the client, and also increase the bandwidth requirements, as the whole result set in sent to the client to be processed.
But, if you only have a small user base or you want to develop something quick and easy they are fine, I have developed one, and although there were some quirks to get over, it went fairly ok.

Author Comment

ID: 7090296
Thank You!  It sounds like there are pro's and con's.
I will probably initially use ODBC and link the tables.

Expert Comment

ID: 7168299

i think that data integrity and bound controls are thr route to go with ADP.

i mean--

its worth $100/seat; isnt it?

cmon ease of development/maintenance/troubleshooting should be our #1 goal..

and bound controls are the best way to do this.

(and ADPs w/SQL are _very_ high performance)

the inclusion with SQL Functions in SQL 2000 makes SQL/ADP the best platform out there...

Expert Comment

ID: 7168903
I agree, ease of maintenance should be the top consideration (processing power is cheaper than developement time etc....) But with good coding practises,design and documentation, the unbound solution can be maintained easily too.
Perhaps my experiences with using bound forms were due to my lack of experience with the tool, but when I re-wrote most of the forms to be unbound, I got a significant performance boost, and also felt I had a much tighter control over the data, and when changes were commited.
I must admit though, that one form (the main one actually) which was, in effect, based on a single table I kept as a bound form, and that worked fine.
But next time I do one of these, I think I will write it unbound from scratch (well maybe use bound forms for a bit of quick prototyping). It probably is just me, but I like the idea of doing a single explicit update with a 'SAVE RECORD' button (Most of my customers prefer that to actually, they always try and click the save icon on the toolbar, if I leave it there!).
So no definitive answer, I think it is probably best to do what you are most comfortable with.

Expert Comment

ID: 7262351
just a quick note-- if you use Access 2002, you can batch update using the GUI instead of needing to write all the code yourself..

i think that batchupdate is just a property on the form or something...

Author Comment

ID: 7262479
Just how much work is involved in changing from .mdb to .adp?

Does it automatically covert queries to stored procedures?
and are you saying that it can automatically create the
new interface?

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

650 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