MS Access project with SQL Server

Author

MDB vs ADP

F. Campos

2005-08-27, 9:24 am

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

2005-08-27, 11:24 am

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" <fcampos2@telefonica.net> wrote in message
news:depmmn$ct0$1@ns
nmpen3-gest.nuria.telefonica-data.net...
> 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.
>
>

F. Campos

2005-08-31, 1:26 pm

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

2005-09-01, 3:25 am

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).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"F. Campos" <fcampos2@telefonica.net> wrote in message
news:df4rik$sj9$1@ns
nmpen3-gest.nuria.telefonica-data.net...
> Thanks Norman,
>
>
> 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
>
>
>

F. Campos

2005-09-06, 7:24 am

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" <sylvain aei ca (fill the blanks, no spam please)>
escribió en el mensaje news:eaOuJ3rrFHA.308@TK2MSFTNGP10.phx.gbl...
> 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).
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "F. Campos" <fcampos2@telefonica.net> wrote in message
> news:df4rik$sj9$1@ns
nmpen3-gest.nuria.telefonica-data.net...
>
>

Nick Stansbury

2005-10-27, 8:32 am

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

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eaOuJ3rrFHA.308@TK2MSFTNGP10.phx.gbl...
> 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).
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "F. Campos" <fcampos2@telefonica.net> wrote in message
> news:df4rik$sj9$1@ns
nmpen3-gest.nuria.telefonica-data.net...
Internet?[color=darkred]
80.90.x.x[color=darkred]
other[color=darkred]

>
>

Sponsored Links