MDB vs ADP

[F. Campos]

Hello everybody,
my name is Fernando

I have an application developed in Access 2002, an MDB file liked to an MDB
file, with
- 72 linked tables and 17 local tables
- about 100 querys
- about 300 forms
- about 120 reports
- The data stored in some tables are Pictures
- Some tables have more than 200.000 records (not pictures)

I want to upgrade my tables to SQL.
Because of so many forms and reports
my question is:
¿Should I keep my mdb file and link to SQL via ODBC? or..
¿Should I rewrite my aplication to an ADP file?
¿Are there some difference that make the difference in performance and
stability?
¿What about if I work in Internet?
¿What should I do?

Thankyou very much.

 

[Norman Yuan]

It is certainly not easy to answer your questions. Just a few thoughts to
consider:

1. ADP does not have local table. So, it you move to ADP, you may have to
re-design part of your database/table.

2. Syntax difference between Access SQL and SQL Server's T-SQL. If you still
use *.MDB, you could keep most queries in FE, so that no need to change
existing queries very much. If using ADP, the queries (Views/SPs) are on the
SQL Server, you may need to re-write quite some of them.

3. If moved to SQL Server, SP is the favorite query means. In conjunction
with the SQL Server security model, common practice is to use SPs as much as
you can and client apps only access data through SPs. This may result in
significant re-writing, whether you use *.mdb FE or ADP FE when moving *.MDB
to SQL Server.

4. Since you have already done so much in the FE (forms, reports, queries),
if have to move BE to SQL Server, I'd look into the possibility to retain
the *.mdb FE first (some changes are must, though).

5. If complete re-writing is possible, before jump on ADP, look into users'
real need to the data in SQL Server again. For example, do the users expect
to access the data through the Internet, or will the data be used by
multiple type of application (office app, CAD app...). Either *.mdb or *.adp
FE/SQL Server BE solution is a typical two-tier structure and suitable on
LAN. You may consider a multiple-tier infomation system and build a seperate
data access layer that can be used/shared by different types of app (desktop
and/or web). In this case, something outside Access (VB, .NET...) may do
better job for you.

6. Either *.ADP or *.mdb FE is not suitable to be used on the Internet

 

[F. Campos]

Thanks Norman,

6. Either *.ADP or *.mdb FE is not suitable to be used on the Internet

Are you sure *.ADP or *.mdb FE is not suitable to be used on the Internet?
I mean, if you have an access to internet with static IP adress, 80.90.x.x
for example, can you configure that PC as a SQL Server in a way that other
PC can access to your DB through internet?
I have hot tried it, but could it work? And could it work fast enought?
without
having to wait more than 5 secs. to show 1000 text records.

Thank you
Fernando

 

[Sylvain Lafontaine]

By suitable, he is talking about speed; which will be borderline with both
MDB and ADP over the Internet; not only when loading 1000 records but also
when navigating from one record to the next (with or without update to the
last current record).

 

[aaron.kempf]

dont listen to these dorks use ADP

 

[F. Campos]

Thank you, Sylvain

My english isn't very good I didn't understand very well what Norman
was trying
to tell.

Anyway, my dilema is still on the table.
I have an applicacion on .mdb that runs very well, but my users need to work
on it
from several places, and right now, when they need to access to it from
outside,
they access via Terminal Server.

Besides that, it's growing.

What I supposed was that if I convert my applicacion on a .adp or link to
SQL
via ODBC, I will be able to make it run fast enought via internet.

If you say that the application will run as Homer Simpson's car, what do you
think
I should do?

Thank you, thank you very much for your answer

Fernando Campos

 

[Sylvain Lafontaine]

I don't know if replacing TS with ADP or ODBC linked table will make the
whole thing faster and I'm not sure if faster would be fast enough for you:
how fast is fast enough?

You have three problems: the database is growing, the number of users is
increasing and you want to access it from the internet instead of simply
accessing it from the LAN. For solving all these three problems; you will
have to consult some local ressource(s) instead of asking some wild guess
from a newsgroup. It is also likely that you will need more than a single,
simple solution to solve all these three.

Using Access with TS is the easy solution for a start and all you to do is
to pay some $ to MS for the TS-CAL; however, when you begin to outgrow this
easy solution, you must be prepared to have to pay some big buck$.

My answer: make a try with ODBC linked tables and a backend SQL-Server (or
MSDE - free) and see what happens with and without TS. Then add some views
to make things faster. See http://support.microsoft.com/kb/q209123/ for
updatable views. After that, if the speed is good enough for you and your
users then you're done; otherwise, take a look at the deep of your pocket.

Other solutions: ADP, ASP.NET, local user's cache with Replication or
disconnected recordsets or ADO.NET, etc.

 

[Nick Stansbury]

Probably worth noting that if you design the system properly this method can
work fine.

I run a large system on exactly this basis - you have to make careful use of
asyncrhonous data access using ado but it does work (and in some cases our
admin system is actually faster at pulling down data that the front ent web
client which is physically located within the same LAN as the sql server.)
So whilst in many cases it can be very slow using an adp / mdb to connect to
a sql server over the net you can make it work.

It's important to note that if you're using Linked tables to connect to the
sql server then this *will* be extremely slow. Re-write using sp's and then
write your own code to populate disconnected client-cursor recordset based
forms that are popultaed from Stored procedures. This way has given us
acceptable performance as above.

Nick

 

[aaron.kempf]

Norman

1) lack of local tables is a BENEFIT
especially since you dont have to use temp tables for everything.
In MDB, query on top of query eventually craps out; so you're forced to
use temp tables. in SQL Server; shit just works correctly.

2) stored procs are much more powerful than Access queries-- any way
you look at it.

5) you are on crack!! seperate data access layer? Maybe you should
just try using a client-server model that WORKS!!! ADP rocks dont
listen to these guys

6) ADP _CAN_ be used on the internet; MDB can't. MDB shouldn't be run
across a LAN.