adp vs mdb

[Mark]

Can someone explain the difference between the following:

An Access mdb declaring an ADODB.Connection which calls a Stored Procedure.

An Access adp which calls the same procedure.

I’m trying to determine the most cost effective means to upsize mdb’s to SQL
Server. It seems that the first example above requires less development time.
My concern is band width levels. Are there differences in the two examples
and relative band widths?

Thanks,

Mark

 

[a a r o n . k e m p f]

the ADP method would _DEFINITELY_ run better over any network.

the ADP method; you don't need to create a secondary connection.
Sounds much simpler to me.

Most cost effective means 'use ADP'.

MDB scans the whole table - when using linked tables - and using Sql
Passthrough- you are not able to change the connection string using
the Linked Table Manager; so SPT seems like a waste to me

-Aaron

 

[Mark]

Aaron,
Thanks for the reply.
When calling the SP from an mdb establishing a new connection, is there data
transfer back to the front end as in the typical mdb fashion (band width
problems)?
Costs to rewrite all DOA into ADO is costly on large apps. If only the
problematic sources are addressed with new ADO connections, the time and
costs are substantially lower.
Mark

 

[Dale Fye]

Mark,

Most cost effective does not mean "use ADP" if it means you have to rewrite
the entire application.

I've got numerous Access (mdb) front ends working with linked SQL Server
backend databases, all of which scream (across the network) as compared to
their Access -> Access brothers.

Generally, upsizing an Access application backend from Access to SQL Server
is quite easy, and requires very little in the way of changes on the
front-end side. I generally change all of my Currentdb.Execute statements to
include the dbSeeChanges option, but other than that, you will find very few
mandatory changes to the application. On the other hand, there a lots of
tweaks you can make to significantly improve performance above and beyond the
mandatory changes.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Mark]

Dale,
What is the dbSeeChanges option?
M

Mark,

Most cost effective does not mean "use ADP" if it means you have to rewrite
the entire application.

I've got numerous Access (mdb) front ends working with linked SQL Server
backend databases, all of which scream (across the network) as compared to
their Access -> Access brothers.

Generally, upsizing an Access application backend from Access to SQL Server
is quite easy, and requires very little in the way of changes on the
front-end side. I generally change all of my Currentdb.Execute statements to
include the dbSeeChanges option, but other than that, you will find very few
mandatory changes to the application. On the other hand, there a lots of
tweaks you can make to significantly improve performance above and beyond the
mandatory changes.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

It is just one of the options accepted by the Execute method. I've generally
found that when I use SQL Server linked tables, I get an error that requires
that I include that option. Normally, I would use syntax that looks like:

currentdb.Execute strsql, dbFailOnError

but when using SQL Server linked tables, I have found that I need to use the
dbSeeChanges option with this command:

currentdb.Execute strsql, dbFailOnError + dbSeeChanges

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[a a r o n . k e m p f]

it's not a rewrite, kid

learn how to copy and paste

nobody should ever have to use the CurrentDb.Execute-- It's just not
necessary.

Any library-- that -HANGS- and makes you explicitly close all your
variables?
It's just not worth using.

Sorry

-Aaron

 

[a a r o n . k e m p f]

re:
all of which scream (across the network) as compared to
their Access -> Access brothers

I mean.. what kind of networks??

All the idiots around here agree that Access won't work across VPN,
WAN or Wireless.

I know for a fact that a linked Access-> SQL just DOES NOT GIVE THE
PERFORMANCE NECESSARY.

Sorry

-Aaron

 

[a a r o n . k e m p f]

I'm sorry dude.. it''s not a cost to rewrite DAO..

You should have moved off of DAO a decade ago.
Its not a cost-- it's a cost because you were too stupid to change a
decade ago.

Is it my fault that you can't read directions?

 

[a a r o n . k e m p f]

and for the record; I convered 400 _PAGES_ of VBA.. from DAO to ADO..
in less than a week.

it's really not that much time.

And I'm sure you're not talking about 400 _PAGES_ of DAO.
I just gave up on DAO a decade ago.. because it makes you .CLOSE and
Set = Nothing in order to have _ANY_ stability.

Any library- that is flaky like that- is not worth using.

-Aaron