ADP vs. MDB

[Peter]

Hi all
I'm going to Upsize an Access 2003 database to SQL server. Access is still
going to be the front end.
My question is: Should I Upsize the frontend to ADP or keep it as MDB ??
What are the pros and cons?

Thanks

regards
Peter

 

[Norman Yuan]

ADP is specifically designed for Access to work as SQL Server's front end
tool. I'd use ADP, rhater than MDB to work with SQL Server as long as the
front ent is to be developed. But if you already have a fairly complicated
MDB front end and redevelop would involve lot of work, then you may want to
stick with the MDB front end.

ADP uses ADO to directky connect to SQL Server, while MDB uses linked table
through ODBC (extra data access layer, comparing to ADP).

 

[Sylvain Lafontaine]

Start with MDB and make tests with ADP; as nothing forbid you to use both at
the same time and MDB is ten times easier then ADP but also slower in some
occasions. Don't forget to read the previous posts in the newsgroup.

S. L.

 

[Gerald Aichholzer]

Hi all
I'm going to Upsize an Access 2003 database to SQL server. Access is still
going to be the front end.
My question is: Should I Upsize the frontend to ADP or keep it as MDB ??
What are the pros and cons?

Hi Peter,

there're some caveats when using the Upsizing assistant. Also an ADP
behaves in some cases different than an MDB, e.g. a recordsource which
is editable in a MDB might be readonly in an ADP. But there're some
new form properties which might help in such cases.

Take care that you activate the DRI-option for the relationships bet-
ween the tables when upsizing. The trigger-option is for SQL server
7.0 only.

You should also add a timestamp field to each table which helps
Access very much in identifying modified records and speed up up-
dates.

Problems which might occur:

- timeouts due to large tables (if an upsizing query takes more
than 60 seconds)

=> HKLM\Software\Microsoft\Jet\4.0\Engines\ODBC\QueryTimeout=0

- upsizing wizard ignores records having date fields with values
< 1.1.1900 or > 6.6.2079

- some queries are not processed, e.g. queries having the keyword
DISTINCTROW (doesn't exist on SQL server), cross table queries
and queries using VBA functions having no T-SQL equivalent

and some other minor issues.

HTH,
Gerald

PS: most of the hints I have from a book I'm reading right now
unfortunately it's in German, so I can't recommend it to you