Conversion to adp

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I am converting my access front-end/backend mdb app to adp. Are there any
pitfalls I should be aware of?

Thanks

Regards
 
John said:
Hi

I am converting my access front-end/backend mdb app to adp. Are there any
pitfalls I should be aware of?

Thanks

Regards
Click to expand...

Yes, if you got an existing application, then it is a LOT better to simply
link the tables to sql server. If you use a adp project, then all of your
dao VBA code will NOT work, and have to be re-written. So, if your
application has a good deal of code and complexity to it, then I would NOT
use the adp. A adp is a possible solution for a NEW project to sql
server..but for a existing one, you will have to re-write too much code.

So, I would consider using odbc links to sql server, and then simply fix the
small amounts of code that does not work with sql server. (this is going to
be WAY WAY less work then re-writing for a adp). On the other hand, if you
were strictly a ADO developer, then converting to a adp project would be
less work, and more code would function under the adp.

So, one issue is how much dao code so you have...and do you want to re-write
that code (which you have to do with a adp).
 
John said:
I am converting my access front-end/backend mdb app to adp. Are there any
pitfalls I should be aware of?
Click to expand...

Don't. Read what Albert said a second time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Just remember that if you go with linked tables and the tables are large
or you have complex quieries then the network traffic may become
prohibitive because the entire table (tables) must be fetched to do a
query in Access. Ideally if there are complex queries you will want to
run them on the SQL Server to minimize network traffic.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.
 
Just remember that if you go with linked tables and the tables are large
or you have complex quieries then the network traffic may become
prohibitive because the entire table (tables) must be fetched to do a
query in Access.
Click to expand...

As a general rule, linked tables are NOT dragged across the network. If the
query is complex, or you are tying to join several tables via linked tables,
then of course that is a warning flag. Of course, any competent developer
will build the view on the sql side..and link to that view to solve that.

So, large tables, and linked ODBC tables generally only drag the one record
you request. Complex quires as you point out often need attention, and the
solution is to link to a view.
 
Hi John,

There is the issue of performance, there seems to be a lot of people that
think that ADP's will give better performance out of the box, and the
performance gains to be had with ADP generally require quite a lot of work,
e.g. writing of stored procedures etc, DAO is fundamentally faster that ADO
(because it is a more specialised data access language, ADO is far more
general purpose) and to try and get true SQL performance requires a good
understanding of SQL server. The only reason I 'upsize' to SQL is volume of
data/users and then the next choice is mdb/adp and you need a good reason to
move to ADP projects. Then the issue of joins if you have queries that join
more than 2 levels of tables theses normally need re-writing if they are to
be updatable. I use both methods (mdb/adp) and I use these rules, if I am
migrating a mdb to SQL I leave the front-end as an MDB (most of the time),
if I am writing something from scratch then ADP to SQL is my preferred
route. The real benefit of using SQL over access for storing the data is
moving code into the database layer rather than processing on the front-end,
and once written these pieces of code (stored procedures) are accessible via
an MDB front-end anyway.
 
Jens Peter Karlsen said:
Just remember that if you go with linked tables and the tables are large
or you have complex quieries then the network traffic may become
prohibitive because the entire table (tables) must be fetched to do a
query in Access. Ideally if there are complex queries you will want to
run them on the SQL Server to minimize network traffic.
Click to expand...

Wrong. This is one of those enduring myths about how Access, or
rather Jet, works.

If the selection and sorting criteria involve an index then only those
pages containing the relevant index entries are downloaded. Then the
relevant pages containing the selected records are downloaded.

If the selection and sorting criteria can't use an index, for example
a data field which doesn't happen to have an index on it, then yes,
the entire table is downloaded to the computer.

Now yes, Jet does create temp files on the PC as required but this
does not mean that the entire table gets put in the hard drive either.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top