.mdb or .adp which is best to use with SQL 2K

P

Popinator

I am upsizing an Access 2k3 database to SQL 2000 (part of Small Business
Server 2003 Premium) My question is which is the best front end to run? A
..mdb or a .adp? I have been using user level security complete with user
names and passwords to log into the .mdb Database. I would like to keep this
if I need to go to .adp. I have upsized (parctice db) to a .adp but can find
no place where the .mdw file is used or can have individule login by name.
Certian securtiy is tied into the specific user logged in example user A can
see $ amounts on a form but user B cannot but can see all other info on the
form. Thanks in advance for any help you can give me on this.
 
S

Sylvain Lafontaine

You cannot use a workgroup (wdw) file with ADP, so if you want to keep this
functionality you must use a MDB database file. However, the real level of
security provided by a workgroup file is very low; all you have to do to
verify is to look at the number of perks that you'll find on the internet
about passing through this *security* feature.

If you're new to SQL-Server, you should stick with MDB and ODBC Linked
Tables; as they are much easier to work with when you're a newbie than using
an ADP project file. However, a well build ADP project will provide you
with a much better performance.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
D

David W. Fenton

I am upsizing an Access 2k3 database to SQL 2000 (part of Small
Business Server 2003 Premium) My question is which is the best
front end to run? A .mdb or a .adp? I have been using user level
security complete with user names and passwords to log into the
.mdb Database. I would like to keep this if I need to go to .adp.
I have upsized (parctice db) to a .adp but can find no place where
the .mdw file is used or can have individule login by name.
Certian securtiy is tied into the specific user logged in example
user A can see $ amounts on a form but user B cannot but can see
all other info on the form. Thanks in advance for any help you can
give me on this.

ADPs are deprecated by MS nowadays in favor of MDB/ACCDB/ODBC
(except for reporting).

So, even if this were brand-new development, you would not be
seriously contemplating ADP. That you already have an MDB front end
means it's a no-brainer.
 
A

a a r o n . k e m p f

ADP are NOT depecrated, jet is depecrated




ADPs are deprecated by MS nowadays in favor of MDB/ACCDB/ODBC
(except for reporting).

So, even if this were brand-new development, you would not be
seriously contemplating ADP. That you already have an MDB front end
means it's a no-brainer.
 
A

a a r o n . k e m p f

However, a well build ADP project will provide you
with a much better performance.
 
T

Tony Toews [MVP]

Sylvain Lafontaine said:
However, a well build ADP project will provide you
with a much better performance.

Just curious? How would an ADP project give you better performance
than an MDB? Assuming that both would use the same stored
procedures, views, etc.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
S

Sylvain Lafontaine

Because Access use the T-model when accessing the data trough ODBC Linked
Tables: first, it reads the value of the primary key for tens rows then it
uses these primary keys to read the rest of the values for the other columns
of each table. When you take a look with the SQL-Server Profiler and make a
comparaison between these two, it's easy to see that there is a lot more
traffic and requests made by Access when accessing the SQL-Server with ODBC
Linked Tables than with ADP.

When you're on a LAN, this added traffic and requests might be seen as not
that much important but if you have to go through the WAN, it't practically
to do as a sufficient speed without using Terminal Server; which add $$$ to
the overall costs. With an ADP project, I can easily go through the WAN at
practically the same speed as Terminal Server but without its cost.

Furthermore, even when you are on a LAN, the added requests still have their
impact on the SQL-Server itself: the more work you ask from it, the fastest
it will come to its saturation level as the number of concurrents users is
rising and you will either have to buy a more powerful machine or have a
less pleasant experience for the users.

I suppose that people who are saying that ODBC Linked tables are as good as
ADP are simply people who have never took a single second of their time to
take a look with the SQL-Server Profiler in order to see what's really going
on under the hood.

Part of the above problem can be relieve by using passthrough queries but
there are read-only and cannot be used for sub-reports; two problems that
you don't have with ADP. So saying that you can use the same stored
procedures with an ODBC project than with ADP is not really true.

Of course, there is this problem that we don't really know about the future
of ADP and of ADO but that's a moot point. It's clear that in a few
versions, MS will possibly throw ADO out to the garbage and keep ADO.NET
exclusively but when they will do so, they will quite probably incorporate
the equivalent functionality of ADP directly into what will be then the new
version of Access; whatever the technology that will be in use at this
moment.

In the meantime, I think that is a better idea to build an interface to a
database based on stored procedures than on ODBC linked tables.

Finally, personally, the moment that I stopped using ODBC Linked tables many
years ago was not even about performance or reports; it was simply because I
was becoming sick of seeing Access making p** into my hands everytime I
wanted to create any query that was not very simple; like a combination of
left joins with some subqueries or unions. When you work against a JET
database, the query engine is passable but when you have ODBC Linked tables;
it is simply too buggy for my taste.

And yes, I have worked on many projects with Access and SQL-Server, with
both ODBC Linked Tables or ADP and with or without Terminal Server; so I
know from personal experience the two sides of the medal.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
O

oomine.noriko

Popinator said:
I am upsizing an Access 2k3 database to SQL 2000 (part of Small Business
Server 2003 Premium) My question is which is the best front end to run? A
.mdb or a .adp? I have been using user level security complete with user
names and passwords to log into the .mdb Database. I would like to keep
this
if I need to go to .adp. I have upsized (parctice db) to a .adp but can
find
no place where the .mdw file is used or can have individule login by name.
Certian securtiy is tied into the specific user logged in example user A
can
see $ amounts on a form but user B cannot but can see all other info on
the
form. Thanks in advance for any help you can give me on this.
 
A

a a r o n . k e m p f

it's ****ing sad that you don't know _HOW_ SQL Server _DIRECTLY_ gives
better performance than SQL Server _PLUS_ 12 other layers
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top