How hard is it to convert an Access mdb to SQL Server?

[Mark B]

We have an Access app (quite big) at www.orbisoft.com/download.

We have had requests by potential users to have it converted to an SQL
version for them since there corporate policy excludes them from buying mdb
backends.

Here's the (million dollar?) questions :)

How long and how difficult a process would it be?
Which SQL platform would we be best to develop it on?
Are there third party companies that could do it all for us?
Anticipated costs?
Anticipated time frames?
Anticipated issues?

Thanks for any thoughts...

Regards
Mark

[Steve Jorgensen]

On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<re************ *************** *********@ihug. co.nz> wrote:

We have an Access app (quite big) at www.orbisoft.com/download.

We have had requests by potential users to have it converted to an SQL
version for them since there corporate policy excludes them from buying mdb
backends.

Here's the (million dollar?) questions :)

How long and how difficult a process would it be?
That depends quite a bit on the application. For most apps, a nominally
working up-size can be done in somewhere between a day to a week, but then a
few performance snags will have to be ironed out as they are discovered during
testing. I recommend keeping with an MDB during up-sizing. If you try to
switch to an ADP, you'll end up reworking 3/4 of the app.
Which SQL platform would we be best to develop it on?
Access can work decently with most any back-end that has an ODBC driver, but
it's happiest with an MS SQL Server back-end. If you use MS SQL Server, you
can use TIMESTAMP (not the same thing as DATETIME) columns in your tables to
improve the performance and reliability of optimistic locking, (and all your
form edits will rely on optimistic locking).
Are there third party companies that could do it all for us?
Sure - lots. Any consultant or company that has expertise in Access C/S
applications. I could do it for you if we can work out a mutually acceptable
deal.
Anticipated costs?
Impossible to say without knowing -lots- more about the app.
Anticipated time frames?
Probably under a week, but again, it very much depends on the app.
Anticipated issues?

Again, it very much depends on the app.

One serious issue that commonly comes up is that if an Access app was designed
to open forms based directly on large, unfiltered, unaggregated tables, it
will berform dismally and place a drain on your network resources. These
parts of the app will need to be changed to use a more C/S-friendly design
paradigm such as a drill-down.

[Rich P]

Just my 2 cents worth, but one fully bloated to the max Access mdb =
0.001% of the capacity of one MS Sql Server2000 DB. Sql Server is the
industrial sized RDBMS (same as Oracle). As such, you are only going to
port data to a Sql Server backend. Access is ideally a front end system
with some RDBMS capabilities (kind of like VB6 has some OOP
capabilities, where VB7 (.net) is fully OOP). You can create views in
Sql Server to filter your datasets if you have forms that are based on
such. Even using ODBC, you will always have more performance using Sql
Server on the backend than Access. Matter of fact, if your project
includes several Access apps, you can combine all of them into one Sql
Server DB. To give you an idea of Sql Server dimension, one Sql Server
table can support 1024 columns. You can keep your architecture the same
in Access (same forms - same table/query names). The trick is just
porting your data to the Sql Server. The only catch is that Sql Server
is way less forgiving than Access in data integrity (thus, more
reliability). Here is where the headaches begin. My workaround is to
create beginning tables in sql server that use nvarchar for all the
columns. Then create the actual tables with the actual datatypes. If
you encounter errors when porting the data from one table to the next in
sql server it is way easier to figure out the problem once you have all
the data in sql server. Query Analyzer gives you a lot of information.

And if you really need performance and have forms based on tables, use
stored procedures (SP's) in Sql Server to populate your Access tables
with just the amount of data that you need and invoke the SPs with ADO -
way faster than ODBC (ADO.Net is the fastest). You can use ODBC for
adhoc stuff.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

On 06 Jan 2004 23:39:33 GMT, Rich P <rp*****@aol.co m> wrote:

....

And if you really need performance and have forms based on tables, use
stored procedures (SP's) in Sql Server to populate your Access tables
with just the amount of data that you need and invoke the SPs with ADO -
way faster than ODBC (ADO.Net is the fastest). You can use ODBC for
adhoc stuff.

I take issue with much of this. JET/ODBC does a fine job in many cases, and
performs well if you avoid the things that make it perform poorly. ADO can be
faster in some cases so long as the back-end has a native ADO driver, but only
if you're very careful. ADO, especially when used with an ADP front-end,
tends to ask for lots of metadata about the back-end before performing an
update, and it doesn't cache this data between queries unless you are reusing
the same Command object, so it can slow you down repeatedly.

There are also optimizations possible with DAO that are not possilbe with ADO
(unless using the JET provider, and then what's the point). For one thing,
you can cache data locally and do joins between local and remote data.

Oh, and by the way, there is a bug in ADPs that make TIMESTAMP basically
impossible to use, and this has a negative impact on the performance of
updates using optimistic locking (all updates via bound forms, for one thing).

When up-sizing an Access/JET database, given that JET/ODBC, properly tended,
can run most queries perfectly well and efficiently, and given the steep
learning curve for ADPs, and the inconsistency of dealing with ADO and DAO at
the same time in an MDB, there is certainly a strong reason to stick with
DAO/ODBC, then upsize individual queries to stored procedures and views as
necessary, accessing them all via DAO.

[Rich P]

I don't dispute anything on this forum (whether correct or not). Most
of the info is quite valid. I offer suggestions and solutions (for the
sake of staying in practice with stuff I haven't done for a while) that
work for me. At my place where I work, we have used Access for years.
But the data got too big for access. Queries taking hours. When I
switched em over to Sql Server 2000 the procedures took andywhere from a
few miliseconds to a few seconds using regular ADO without any hitches.
But that workd for me. Just a suggestion. Just an alternative.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tony Toews]

Steve Jorgensen <no****@nospam. nospam> wrote:

That depends quite a bit on the application. For most apps, a nominally
working up-size can be done in somewhere between a day to a week, but then a
few performance snags will have to be ironed out as they are discovered during
testing.

Whereas one app I worked on I estimated a total of two or three months. But this
included porting the queries to views and stored procedures.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"Mark B" <re************ *************** *********@ihug. co.nz> wrote:

We have had requests by potential users to have it converted to an SQL
version for them since there corporate policy excludes them from buying mdb
backends.

Here's the (million dollar?) questions :)

How long and how difficult a process would it be?

Could be months. Especially if you have a lot of functions in the queries.

Do a search at the Knowledge Base at support.microso ft.com using the keywords
"upsizing" to review the various white papers on upsizing Access to SQL Server as
well as to ensure you have any updates required.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
my website.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tom van Stiphout]

On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<re************ *************** *********@ihug. co.nz> wrote:

I found there is an easy way and a hard way.
Easy: use the upsize wizard to move the tables to SQL Server, attach
the tables to the Access front-end, and pretty much call it good.
Time: a few hours to a few days.
Cons: not much (if any) performance gains.

Hard: realize that SQL Server really shines in a client/server model,
which essentially requires a rewrite of your app, or at least a
re-assessment. It requires all queries to be rewritten as views and
stored procedures, and probably would mean you would use ADO with
Visual Basic or Access ADP as the front-end.
Time: probably weeks or months.
Cons: see Time.

-Tom.

We have an Access app (quite big) at www.orbisoft.com/download.

We have had requests by potential users to have it converted to an SQL
version for them since there corporate policy excludes them from buying mdb
backends.

Here's the (million dollar?) questions :)

How long and how difficult a process would it be?
Which SQL platform would we be best to develop it on?
Are there third party companies that could do it all for us?
Anticipated costs?
Anticipated time frames?
Anticipated issues?

Thanks for any thoughts...

Regards
Mark

[Steve Jorgensen]

On Tue, 06 Jan 2004 20:47:15 -0700, Tom van Stiphout <to*****@no.spa m.cox.net>
wrote:

On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<re*********** *************** **********@ihug .co.nz> wrote:

I found there is an easy way and a hard way.
Easy: use the upsize wizard to move the tables to SQL Server, attach
the tables to the Access front-end, and pretty much call it good.
Time: a few hours to a few days.
Cons: not much (if any) performance gains.

Hard: realize that SQL Server really shines in a client/server model,
which essentially requires a rewrite of your app, or at least a
re-assessment. It requires all queries to be rewritten as views and
stored procedures, and probably would mean you would use ADO with
Visual Basic or Access ADP as the front-end.
Time: probably weeks or months.
Cons: see Time.

-Tom.

Ack - people keep saying that for an Access C/S app to perform well,
everything must be rewritten as stored procedures and views! I'm not saying
that's never a valid approach, but it's not the only way. It is very possible
to have a well-written, efficient Access C/S application running mainly with
ordinary Access queries, and letting JET handle the translation to the
back-end.

The Access JET engine does, for the most part, a fine job of submitting
queries to the back-end in an efficient way, so long as the general good
practices are followed regarding limiting the number of output rows in a
single result set. When you use this approach, more of the nice, flexible
things we like about Access still work, and don't have to be discarded. This
includes things like editing query data based on a join between 2 tables
through a form. There are a few tricks to learn like using dynamic SQL, not
subform master-child links to filter a subforms since the link just filters
the visible records in the fully populated recordset, not the query that
generates the recordset (in an MDB).

Here are the rules I follow when deciding how to implement an Access C/S
database app.

Case 1: The database's function is solely or primarily to act as a back-end
to a single Access application.

Approach: Do as much as possible with plain ol' Access queries. Tweak the
queries as necessary to get them to optimize well, and implement a fiew views
and stored procedures as necessary. This approach keeps the application
flexible and not tightly tied to one particular back-end. If you decide later
to implement a smaller copy with a JET back-end, or switch the back-end to
PostgreSQL, your job will not be too hard.

A have had great results with this approach on a seriously complex
application. Probably 85% of the querying and updating was done using normal
bound forms and Access queries.
Case 2: The Access application is one of several interfaces to a database.

Approach: Do -EVERYTHING- through stored procedures and views. This allows as
much as possible of the business logic to be handled in the back-end where it
will apply uniformly to all interfaces. We pretty much give up on editing
data through bound forms, and give a second thought to whether this really
should be an Access application at all, and not in something like VB since the
only benefits Access now gives you are continuous, view-only forms and one of
the coolest reporting systems on the planet.

[Tony Toews]

Tom van Stiphout <to*****@no.spa m.cox.net> wrote:

I found there is an easy way and a hard way.
Easy: use the upsize wizard to move the tables to SQL Server, attach
the tables to the Access front-end, and pretty much call it good.
Time: a few hours to a few days.
Cons: not much (if any) performance gains.
However if your query names have spaces and are nested then you will hit a bug.
Hard: realize that SQL Server really shines in a client/server model,
which essentially requires a rewrite of your app, or at least a
re-assessment. It requires all queries to be rewritten as views and
stored procedures,
Unless your query has a function or is relatively complex, however complex is
defined, you can programmaticall y copy your query to SQL Server. Thus many don't
need rewriting.

Most of my action queries are based on select queries and are strings in VBA code
with the WHERE clause added. So these likely aren't a big deal. Although it would
be interesting to do a performance comparison on this. Now where there are a number
in a row, which is seldom, then I could manually create some SPs.
and probably would mean you would use ADO with
Visual Basic or Access ADP as the front-end.

Now that, to me, doesn't follow. Well the ADP bit might but any temporary tables
would have to be converted to SQL Server views and SPs.

But I don't see at all why you'd want to use VB in this. Or ADO if everything is
working in DAO.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm