adp vs mdb and SQL Sever

Posted on 2002-06-12
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
  • 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.

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 100 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.
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now