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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

930 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

13 Experts available now in Live!

Get 1:1 Help Now