mdb to SQL server backend question

[djc]

Current db is developed in Access 2002.

1. What is the best way to convert an access .mdb backend file to SQL Server
2000? Just link the tables through ODBC and thats it? Front end stays .mdb?
or is it this .adp?

2. How much time is generally involved in converting a backend .mdb file
into a SQL Server 2000 database? Any code changes? Or does the 'upsizing
wizard' take care of that?

3. Same subject as number 1 but Whats the most 'common' way?

Looking for general info and guidance and this point... links to info are
certianly welcome!

Thanks

[Eddie W. Stroud]

Hate to give you an "It all depends" answer but - if its pretty simple it
will probably convert pretty well. I've done some that are completely
functional using the Wizard. On the other hand, if it's old, has a lot of
DAO stuff, etc. you may need to do quite a bit. My suggestion is to run the
wizard. See what you get and look at the error logs and you will be able to
answer most of your own questions.

Ed

"djc" <no...@nowhere.com> wrote in message
news:OhtQdhB...@TK2MSFTNGP12.phx.gbl...

[djc]

thanks. I beleive its all DAO. No ADO.

Is there a difference between just using an mdb front end with linked tables
from a SQL server and using the upsizing wizard to create an adp? If so,
whats the difference?

Does the former still use Jet and the latter the SQL Server engine?

"Eddie W. Stroud" <est...@bak.rr.com> wrote in message
news:b8sc1i$dhffg$1...@ID-142628.news.dfncis.de...

[aaron kempf]

rewrite the DAO as ADO. move to ADP.

dont listen to anyone that tells you otherwise.

its a tough conversion.. but it is worth it i PROMISE

dont use so much DAO/ADO in the future. It just isnt necessary in many
situations.

use SQL Server cursors in places like that--

or even SQL Server User Defined functions.. (but dont overuse these)

frequently people use DAO/ADO too liberally, and it puts too much of the
business logic into the application, when you can centralize this business
logic into the database tier and it really simplifies deployment.

"djc" <no...@nowhere.com> wrote in message

news:efIGBeKE...@TK2MSFTNGP10.phx.gbl...

[Vadim Rapp]

ak> rewrite the DAO as ADO. move to ADP.

I think this is bad recommendation. Pure ADO does not live well with Access,
this newsgroup has a lot of examples. For best results, everything must be
rewritten in Access, if you will, i.e. docmd whenever possible. ADO is
indeed good, but to use ADO fully (hierarchial and disconnected recordsets,
server cursors, etc.), you have to program in VB, not in Access.

Move to ADP = sacrifice locks and transactions. A database without locks and
transactions is hardly a database at all, so, strictly speaking, ADP forms
should not be used for interactive work of multiple users with the database.

Vadim

[Lyle Fairfield]

"aaron kempf" <aaron...@hotmail.com> wrote in
news:e3ugmINE...@TK2MSFTNGP11.phx.gbl:

> rewrite the DAO as ADO. move to ADP.
>
> dont listen to anyone that tells you otherwise.
>
> its a tough conversion.. but it is worth it i PROMISE
>
> dont use so much DAO/ADO in the future. It just isnt necessary in many
> situations.
>
> use SQL Server cursors in places like that--
>
> or even SQL Server User Defined functions.. (but dont overuse these)
>
> frequently people use DAO/ADO too liberally, and it puts too much of the
> business logic into the application, when you can centralize this
> business logic into the database tier and it really simplifies
> deployment.

I don't think every MDB application should be rewritten as an ADP, but the
work for those for which one could predict a long-life, is likely to be
worth the effort.
Your last sentence identifies an enormous power of ADP-SQL, but it seems to
be a new way of thinking for experienced MDB developers, who may resist or
even denigrate the idea (moving much of the business logic to the
Database). Conditional execution and looping within MS-SQL statements takes
some getting used to, (we just did not have this available in JET), and as
the syntax is a bit different from VBA syntax, may cause some frustration.
On the other hand, my several second (time) VBA-ADO procedures are
virtually instantaneous when converted to T-SQL.

--
Lyle

[aaron kempf]

i dont see why you cant use real ADO in ADP. Sure it has a wrapper around
the connection object (i cant remember the name of it offhand-- client data
manager?)

but it allows you to easily do everything that you liked doing in an MDB
environment, with all of the benefits of ADO and SQL Server.

it is MUCH MUCH MUCH faster

and MUCH MUCH MUCH more stable.

and MDB is the one that always gives me problems with locking my SQL Server
data.

i think that MDB has got to be the WORST PRODUCT ever made. Especially if
you watch a SQL Server trace when you update through the twelve different
layers between MDB and SQL Server.

you CAN implement transactions through stored procedures.
this is standard BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN syntax.

Vadim, if you have proof of something different than this, i would love to
see it.

"Vadim Rapp" <v...@nospam.myrealbox.com> wrote in message
news:OGb96KZ...@TK2MSFTNGP12.phx.gbl...

[Vadim Rapp]

ak> i dont see why you cant use real ADO in ADP. Sure it has a wrapper around
ak> the connection object (i cant remember the name of it offhand-- client data
ak> manager?)

you can, but "independently", i.e. you can't mix ado recordsets with controls'
recordsets (forms, combo etc) Technically you can, but it results in very
unstable operation and crashes. For example, if you assign a recordset to a
form's datasource and then requery, the connection between the recordset and
form is broken. This is all pretty much unpredictable and takes lots of time.

ak> you CAN implement transactions through stored procedures.
ak> this is standard BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN syntax.

Sure. But usually transactions are wanted when working with the data
interactively. For example, I open a form, make a change here and there, go to
the subform, add a record, delete a record, then go back to the main form, then
I change my mind and want to cancel everything. Logically, this should be
wrapped in transaction, and this is the most ordinary scenario. How are you
going to use an s.p here? have everything unbound, and then programmatically
collect the data and run the s.p? how is it different then from VB?

also, while I'm working with the form, the record should be locked in the
database. This is exactly the purpose of database locks, to prevent others from
working with my data, and many thousands of man-hours were put in order to
implement it. In adp it's all discarded and replaced by "you know, your data
somehow was modified by someone else while you were working on it" (where
someone might actually be myself, or a trigger triggered by meself). Excuse me,
but if I purchased a full-power database with all kinds of isolation levels and
other fine tunings, I'm expecting it to prevent this kind of scenario.

Sure, ADP is fast and easy, but it's only because it deliberately implements
the easiest subset of ADO. If this subset is sufficient, then it's the best
choice. But it can only be sufficient in case of not more than few database
users writing into the database (technically, only one user). The logis "so
easy, no wonder it's #1" may be good for AOL users, but I would hate databases
to degrade to that level after decades of progress.

Vadim