ADP vs. MDB

[Nick Stansbury]

Hi,
A question that has been thrashed about a lot here I imagine but can
anyone answer it anyway. What are the advantages / disadvantages to using
ADP's or MDB's as the front end to a sql server database. Do those answers
change if the sql server database is located off site and the only way to
connect to it is via and unsecured tcp/ip connection directly over the
internet?

Thanks

Nick

 

[BJ Freeman]

First it depends on your application. What you intend to do.

As far as remote, yes if you are handling sensitive data you can not use the
standard SQL drivers.
I use VB and ADP to connect to SQL DB over the internet via TCPIP.
I wrote a Active DLL That I reference in the VB and ADP VBA section, that
connects to an Active dLL on the SQL server.
In VB and VBA code, I replace the "currentproject" with the Object created
for the Active DLL.
The two DLL communicated with encryptions over TCPIP, as pass-through
drivers.

 

[Sirocco]

ADP's allow you to modify the SQL table structure, whereas MDB do not (in
which case you need Enterprise Manager to modify table structure). Some
would view this as an advantage for ADP. But you have to learn T-SQL and be
an expert with VB to effectively use ADP front ends, which may favor the use
of an MDB front end. ADPs have a steep learning curve. Linking an MDB
file to a SQL back end provides almost all the advantages that SQL has to
offer.