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]

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

On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<remove_this_bi t_mbrownlee_and _this_bit@ihug. co.nz> wrote:
[color=blue]
>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?[/color]

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.
[color=blue]
>Which SQL platform would we be best to develop it on?[/color]

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).
[color=blue]
>Are there third party companies that could do it all for us?[/color]

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.
[color=blue]
>Anticipated costs?[/color]

Impossible to say without knowing -lots- more about the app.
[color=blue]
>Anticipated time frames?[/color]

Probably under a week, but again, it very much depends on the app.
[color=blue]
>Anticipated issues?[/color]

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]

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

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]

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

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

....[color=blue]
>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.[/color]

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]

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

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]

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

Steve Jorgensen <nospam@nospam. nospam> wrote:
[color=blue]
>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.[/color]

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]

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

"Mark B" <remove_this_bi t_mbrownlee_and _this_bit@ihug. co.nz> wrote:
[color=blue]
>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?[/color]

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]

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

On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<remove_this_bi t_mbrownlee_and _this_bit@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.

[color=blue]
>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
>[/color]

[Steve Jorgensen]

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

On Tue, 06 Jan 2004 20:47:15 -0700, Tom van Stiphout <tom7744@no.spa m.cox.net>
wrote:
[color=blue]
>On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
><remove_this_b it_mbrownlee_an d_this_bit@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.[/color]

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]

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

Tom van Stiphout <tom7744@no.spa m.cox.net> wrote:
[color=blue]
>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.[/color]

However if your query names have spaces and are nested then you will hit a bug.
[color=blue]
>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,[/color]

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.
[color=blue]
>and probably would mean you would use ADO with
>Visual Basic or Access ADP as the front-end.[/color]

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

[Steve Jorgensen]

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

On Wed, 07 Jan 2004 04:38:41 GMT, Tony Toews <ttoews@teluspl anet.net> wrote:
[color=blue]
>Tom van Stiphout <tom7744@no.spa m.cox.net> wrote:
>[color=green]
>>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.[/color]
>
>However if your query names have spaces and are nested then you will hit a bug.[/color]

I recommend manually, not automatically upsizing tables anyway. It takes a
few hours, but it forces you to look at what fields should be indexed, what
indexe, if any, should be clustered on each table, whether the table should
have an IDENTITY column added, etc.
[color=blue]
>[color=green]
>>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,[/color]
>
>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.[/color]

Most don't really need to be copied to the back-end either. Access/JET
handles that just fine. Parameterizes queries are converted to prepared
statements, so they work about as well as stored procedures, and multi-table
joins are properly translated.

[color=blue][color=green]
>>and probably would mean you would use ADO with
>>Visual Basic or Access ADP as the front-end.[/color]
>
>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.[/color]

Actually, that does follow. JET/DAO only works well when there is already an
MDB involved. In VB, yu don't want to haul an MDB component around with the
app, and in an ADP, ADO is the default for everything Access does, and JET is
not even running.
[color=blue]
>But I don't see at all why you'd want to use VB in this. Or ADO if everything is
>working in DAO.[/color]

That's my opinion as well. There are good cases for ADO in some situations,
but for an app you've already decided will use Access, I think an MDB and DAO
is almost always the best way to go.

[Rich P]

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

Over at my place our data (millions of records - daily) ends up in Excel
reports (all formatted and stuff - that is the hard part - not sql
server - have you ever tried automating a real sophisticated report in
Excel - I always end up having to write ActiveX dlls to assist). Then I
have to port detail data to Excel to support the Excel reports - usually
about 5000 - 6000 records. Yes, ODBC queries are about as fast as SPs
for static display of data in Access from sql server. Where ODBC dies
instantly is when you have to port the 5000-6000 records to Excel. Here
is where the SP shines - retrieve a dataset using an sp and an ADO
command object and write directly to Excel using range.CopyFromR ecordset
RSado. 5000-6000 records (30 columns) 0.02 to 0.03 seconds vs all day
with the ODBC query to Excel.

You can't lose with Sql Server2000 ADO and an Access mdb combination.
If the skillset available to your group for this project is limited you
could probably save some money by hiring an entity with the required
skillset (outsource to a sql server consulting group).

Rich

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

[Steve Jorgensen]

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

On 07 Jan 2004 07:49:30 GMT, Rich P <rpng123@aol.co m> wrote:
[color=blue]
>Over at my place our data (millions of records - daily) ends up in Excel
>reports (all formatted and stuff - that is the hard part - not sql
>server - have you ever tried automating a real sophisticated report in
>Excel - I always end up having to write ActiveX dlls to assist). Then I
>have to port detail data to Excel to support the Excel reports - usually
>about 5000 - 6000 records. Yes, ODBC queries are about as fast as SPs
>for static display of data in Access from sql server. Where ODBC dies
>instantly is when you have to port the 5000-6000 records to Excel. Here
>is where the SP shines - retrieve a dataset using an sp and an ADO
>command object and write directly to Excel using range.CopyFromR ecordset
>RSado. 5000-6000 records (30 columns) 0.02 to 0.03 seconds vs all day
>with the ODBC query to Excel.
>
>You can't lose with Sql Server2000 ADO and an Access mdb combination.
>If the skillset available to your group for this project is limited you
>could probably save some money by hiring an entity with the required
>skillset (outsource to a sql server consulting group).
>
>Rich
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]

Well, we can agree to disagree on this one. I won't argue with your results
usinng ADO to query Excel, but I would call that a special case where you
would use ADO because it solves a specific problem.

In my extensive experience with Access using both MDBs and ADPs as front-ends
to SQL Server, and writing both DAO and ADO code, I would say that, if your
front-end is Access, you should be using an MDB, not an ADP, and you should
use DAO unless you come up with a special in which there is a clear case for
ADO. If your front-end of choice is -not- Access, then ADO is probably the
obvious choice since DAO is not terribly useful without an MDB, and why
should, for instance, an Excel/VBA app need to haul an MDB around.

[Len]

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

Mark,

Yes you have asked the million dollar question. The good news is it
will not cost you that much and you can find all you need at
www.upsizewizard.com.

Len

"Mark B" <remove_this_bi t_mbrownlee_and _this_bit@ihug. co.nz> wrote in message news:<btfba5$9d d$1@lust.ihug.c o.nz>...[color=blue]
> 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[/color]

[Mark B]

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

> Cons: not much (if any) performance gains.

Apart from the fact that you can have a billion concurrent users :) ?

Actually, on that, how many concurrent users does SQL actually allow (out of
interest)? I suppose it's similar to a web server with no real limit, just
wait-time increases. I wonder what a practical figure for this spec would be
if a customer asked - would it be safe to say 4000 users?

Here's some other questions that you or the others may be able to answer
(and by the way thank you all for your comments):

- What about functions, e.g. fCallMe() in queries?

- So what _do_ you do about Form![MyForm![MyControl] in queries?

- A lot of our queries are generated on demand in VBA then saved as
querydefs. What about these?

- Some of our queries have nested queries up to 7 levels. Is that an issue
in conversion?

- We manipulate backend table structure via code (change field types) from
the front-end. OK in SQL server?

- Rather than have 2 front-ends to maintain code for, can one front-end
handle both MDB backend or SQL backend depending on a user option chosen in
the front-end? Currently we do the normal RefreshLinks() when then app
starts up for the backend MDB.

- And here's a final question someone might be able to answer.... are there
many commercial apps out there that are actually using this Access-SQL
configuration for large numbers of people? E.g. 10,000+ users on the same
backend tables all using the same Access frontends?

Thanks
Mark




"Tom van Stiphout" <tom7744@no.spa m.cox.net> wrote in message
news:500nvvggau 690rrteoo1aa6cs reqm77d37@4ax.c om...[color=blue]
> On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
> <remove_this_bi t_mbrownlee_and _this_bit@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.
>
>[color=green]
> >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[/color][/color]
mdb[color=blue][color=green]
> >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
> >[/color]
>[/color]