Solved

adp vs mdb and SQL Sever

Posted on 2002-06-12
10
847 Views
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
0
Comment
Question by:BBrian
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 10

Expert Comment

by:KenAdney
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.
0
 
LVL 5

Expert Comment

by:funke
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?
0
 

Author Comment

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

http://office.microsoft.com/assistance/2002/articles/WaysToWorkWithSQLData.aspx


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.
0
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.

 
LVL 5

Expert Comment

by:funke
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.
0
 
LVL 2

Accepted Solution

by:
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.
0
 

Author Comment

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

Expert Comment

by:__Holly__
ID: 7168299
carlovski;

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...
0
 
LVL 2

Expert Comment

by:Carlovski
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.
0
 
LVL 1

Expert Comment

by:AAMFK
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...
0
 

Author Comment

by:BBrian
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?
0

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

786 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