adp vs mdb and SQL Sever

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
Who is Participating?
CarlovskiConnect With a Mentor Commented:
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.
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.
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?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

BBrianAuthor Commented:
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.
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.
BBrianAuthor Commented:
Thank You!  It sounds like there are pro's and con's.
I will probably initially use ODBC and link the tables.

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...
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.
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...
BBrianAuthor Commented:
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?
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.

All Courses

From novice to tech pro — start learning today.