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

[Tom van Stiphout]

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

On Wed, 07 Jan 2004 04:53:43 GMT, Steve Jorgensen
<nospam@nospam. nospam> wrote:

<clip>
If - and from reading the discussions that's a bigger if for some
people than for me - you have chosen to use ADO, with the ability to
use disconnected recordsets, VB is on the table again.

-Tom.

[R Baumann]

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

[color=blue]
>"Steve Jorgensen" <nospam@nospam. nospam> wrote in message[/color]
news:k3gnvvssov eqvum5fpb201f91 0vnk67f7l@4ax.c om...[color=blue]
>
>SNIP
>
> In my extensive experience with Access using both MDBs and ADPs as[/color]
front-ends[color=blue]
> to SQL Server, and writing both DAO and ADO code, I would say that, if[/color]
your[color=blue]
> front-end is Access, you should be using an MDB, not an ADP, and you[/color]
should[color=blue]
> use DAO unless you come up with a special in which there is a clear case[/color]
for[color=blue]
> ADO. If your front-end of choice is -not- Access, then ADO is probably[/color]
the[color=blue]
> 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.[/color]

Steve, I have read many, many of your posts over the years in
comp.databases. ms-access, and microsoft.publi c.adp.sqlserver , and a couple
of other NG's, and have watched you gain much experience with MDB, ADP, and
SQL databases. Perhaps it's about time(if you haven't already done so) to
create a web page of your own of hints and tricks and tips, upsizing, things
to do, things to avoid. Maybe even combine some efforts with Tony Toews,
Tom van Stiphout, Doug Steele to provide a REALLY useful web page on Access
DB design and use.

Just a thought. :-)

Ruben

[Steve Jorgensen]

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

On Wed, 07 Jan 2004 06:56:09 -0700, Tom van Stiphout <tom7744@no.spa m.cox.net>
wrote:
[color=blue]
>On Wed, 07 Jan 2004 04:53:43 GMT, Steve Jorgensen
><nospam@nospam .nospam> wrote:
>
><clip>
>If - and from reading the discussions that's a bigger if for some
>people than for me - you have chosen to use ADO, with the ability to
>use disconnected recordsets, VB is on the table again.
>
>-Tom.[/color]

When I first started playing with ADPs, the ability to bind forms to
disconnected recordsets seemed really great. Heck, you could even use it to
make Access a front-end to 3-tier application without losing any of Access'
form functionality. Then, I found out that there are so many don'ts and
problems with doing this that it really should be avoided. Unless you want to
risk having the app hard crash from time to time, and walk a tight rope to
make sure Access won't try to reconnect your recordset before you want it to,
you don't want to use them.

Furthermore, unless they've fixed it in A2003 (which would be really neat if
they have) the idea of editing a disconnected recordset, then doing a batch
update is right out because Access mangles the optimistic locking data in an
OptimisticBatch recordset when you try to edit. This is a case where it would
be comelling if it worked.

For all I know, they've got all this working better now in A2003, so if that's
so, I'll stand corrected. The history with A2000 and A2002 is, though, that
ADP-related bugs are slow to get fixed, and tend to re-break in subsequent
updates, so I haven't been real hopeful about that stuff in A2003.

[David W. Fenton]

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

rpng123@aol.com (Rich P) wrote in
<3ffb52ee$0$204 $75868355@news. frii.net>:
[color=blue]
>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.[/color]

I have never seen an Access query that was part of the daily
operations of an application take hours, even is apps with 100s of
thousands of records in the data tables involved. The only queries
I've ever encountered that *do* take hours are update queries that
I've run on large batches of data during data import from old data
into new database schemas. Things like populating 4 Soundex fields
(soundex and soundex2 fields for LastName and FirstName) for a
people table with 350K records do, in fact, take a lot of time. Of
course, doing 4 separate updates works faster than doing them all
in one query (if you update all fields, you have to run four
functions per record; if you update the FirstName soundex fields
only for those records that where FirstName Is Not Null, you're
updating fewer records; then for the Null fields, I populate the
appropriate soundex fields with the relevant number of zeroes).

Yes, during data massage operations like this, I've set an update
query running and gone to another machine to work on something else
for an hour or two.

But I've simply never seen a query operation that's part of regular
working operations that needs to operate on such a large number of
records. Any query that has that kind of problem needs to be
optimized, the same way you'd optimize a query on SQL Server, by
looking at how you're performing the operations.

In other words, you're blaming the poor performance on Jet when the
actual cause is bad query design.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

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

tom7744@no.spam .cox.net (Tom van Stiphout) wrote in
<500nvvggau690r rteoo1aa6csreqm 77d37@4ax.com>:
[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]

Possibly even performance penalties in many instances.
[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, 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.[/color]

First, you don't have to rewrite everything, just the parts that
don't perform properly. After that, you could re-architect the
parts that already perform satisfactorily but could be made to
perform better if moved to SQL Server.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

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

rpng123@aol.com (Rich P) wrote in
<3ffbba0a$0$195 $75868355@news. frii.net>:
[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
>sophisticate d 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).[/color]

I don't understand your scenario. Are you saying that opening a DAO
recordset from a SQL Server database that you connect to with ODBC
and then walking that recordset and writing to Excel is slower than
opening an ADO recordset from a SQL Server database with a direct
connection and then walking that recordset and writing to Excel?

I don't understand what exact mechanism could be causing the
*writing to Excel* part to be slower just because you've used a
different method to open the recordset.

It makes no sense whatsoever, so I can only conclude that I've
completely misinterpreted the scenario you're describing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[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]

[Larry Linson]

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

I am in agreement with Steve -- in my experience, the clients did not want
to be held hostage to the "server database DBA/developer marketplace" so
specifically requested that we minimize the use of server-specific features.
Thus, because they had a ready supply of competent Access developers for
front-end work, we had barely a handful of views and two or three stored
procedures (which returned a next unique id so we could avoid using the
server equivalent of AutoNumber). And, performance was good, all things
considered.

I've recently heard, too, that some very heavy-duty insiders at Microsoft
have stated that MDB-Jet-ODBC-server is always a preferrable configuration
to ADP-ADODB-server. Jerry Boone, posting here, pointed out that he'd had
good luck with an ADP communicating across the Internet to a server DB,
though, so that configuration might be an exception.

A good single-user standalone database application just split with the data
tables on a server isn't necessarily a good multiuser application. Neither a
good single-user standalone nor a good multiuser application is
_necessarily_ a good client-server application. Performance is attained
through different approaches in all these environments.

I can't speak to the conversion effort, because most of the projects I've
worked that ended up client-server also _began_ in that configuration. The
apps (prototypes, mostly) that I have "converted" were small and simple,
usually nearly trivially so. It does take a little more time and effort to
develop the same type app in the client-server environment, but with
MDB-Jet-ODBC-server, my experience is that the operative word is "little"
more.

I don't doubt that an outstanding SQL Server DBA/developer can write some
stored procedures, etc., that "sing" and "zing". But, my clients found that
"outstandin g SQL Server DBA/developers" were far harder to find that good,
competent Access developers and much, much more expensive.

Larry Linson
Microsoft Access MVP

[Salad]

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

Tony Toews wrote:
[color=blue]
> 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]

I wouldn't mind your opinion.

I have. in my apps, tried to limit the number of queries in my database. For the most
part I may create a query for a report or form, get the SQL statement from the builder,
and paste that in as the recordsource/rowsource for the form/report or combo/listbox.
IOW, the record/row source contains the SQL statement, not a query name. There ARE time
I use queries, but for the most part...my record/row source is the SQL statement. I know
that having a query (thus gets compiled) is supposed to be faster than a non-query
(created from a parsed SQL statement) but overall I'd say my apps are quick and
snappy....my user wouldn't/couldn't differentiate a query executing .0001 second faster.

So...regarding the SQL Server topic, is it best that all queries for record/row source be
queries or is a SQL statement fine, and how does one address statement like
Dim strSQL as String
strSQL = "Select Table1.lastName from Table1 where ...."
Me.MainForm.For m.Recordsource = strSQL
Should these be queries also?

[Larry Linson]

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

"Salad" wrote

[color=blue]
> I wouldn't mind your opinion.
>
> I have. in my apps, tried to limit the
> number of queries in my database.
> For the most part I may create a
> query for a report or form, get the
> SQL statement from the builder,
> and paste that in as the recordsource/
> rowsource for the form/report or
> combo/listbox.
> IOW, the record/row source contains
> the SQL statement, not a query name.
> There ARE time I use queries, but for
> the most part...my record/row source
> is the SQL statement. I know that having
> a query (thus gets compiled) is supposed
> to be faster than a non-query (created
> from a parsed SQL statement) but overall
> I'd say my apps are quick and snappy....
> my user wouldn't/couldn't differentiate a
> query executing .0001 second faster.[/color]

Perhaps you were not aware that Access is "smart" enough to recognize that
an SQL Statement in a RecordSource is the equivalent of a Query, and that it
creates a "temporary query" that it saves, so your SQL is treated just like
a saved query.
[color=blue]
> So...regarding the SQL Server topic, is it best
> that all queries for record/row source be
> queries or is a SQL statement fine, and how
> does one address statement like
> Dim strSQL as String
> strSQL = "Select Table1.lastName from Table1 where ...."
> Me.MainForm.For m.Recordsource = strSQL
> Should these be queries also?[/color]

If you are using MDB/MDE-Jet-ODBC-Server, as I would strongly recommend, the
Queries and SQL that you use will be _Jet_ Queries and _Jet_ SQL and will be
translated by Jet/ODBC before being sent to the Server. Jet cannot "prepare"
or "compile" a Query for the Server as it can for Jet, so it will make
little difference.

The exceptions to this will be Queries that you have identified as
"Passthroug h Queries", in which you will use Server SQL conventions, and Jet
will, as requested, simply "pass them through" to Server. You can also
create Views on the Server, which is SQL that is seen by Jet the same as
Server Tables, or Stored Procedures on the Server, which are accessed via a
Passthrough Query. I've used some Views, but never found it necessary to use
Stored Procedures for adequate performance in retrieving data.

And, in my experience in all-Jet databases, with Tables containing tens of
thousands of records, there is little difference in the response that you
see at the monitor between saved Queries and SQL. I read that there is a
_measurable_ difference, and I have not worked with all-Jet databases with
huge numbers of records, so cannot comment on that environment. Thus, my
observation would be that your users don't "pay a penalty" for your use of
SQL instead of saved Queries. It is, IMNSHO, simply a matter of preference
and suitability for a given programming situation.

Larry Linson
Microsoft Access MVP

[Salad]

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

Larry Linson wrote:
[color=blue]
> "Salad" wrote
>[color=green]
> > I wouldn't mind your opinion.
> >
> > I have. in my apps, tried to limit the
> > number of queries in my database.
> > For the most part I may create a
> > query for a report or form, get the
> > SQL statement from the builder,
> > and paste that in as the recordsource/
> > rowsource for the form/report or
> > combo/listbox.[/color]
> Perhaps you were not aware that Access is "smart" enough to recognize that
> an SQL Statement in a RecordSource is the equivalent of a Query, and that it
> creates a "temporary query" that it saves, so your SQL is treated just like
> a saved query.[/color]

No, I wasn't.
[color=blue][color=green]
> > So...regarding the SQL Server topic, is it best
> > that all queries for record/row source be
> > queries or is a SQL statement fine, and how
> > does one address statement like
> > Dim strSQL as String
> > strSQL = "Select Table1.lastName from Table1 where ...."
> > Me.MainForm.For m.Recordsource = strSQL
> > Should these be queries also?[/color]
>
> If you are using MDB/MDE-Jet-ODBC-Server, as I would strongly recommend, the
> Queries and SQL that you use will be _Jet_ Queries and _Jet_ SQL and will be
> translated by Jet/ODBC before being sent to the Server. Jet cannot "prepare"
> or "compile" a Query for the Server as it can for Jet, so it will make
> little difference.
>
> The exceptions to this will be Queries that you have identified as
> "Passthroug h Queries", in which you will use Server SQL conventions, and Jet
> will, as requested, simply "pass them through" to Server. You can also
> create Views on the Server, which is SQL that is seen by Jet the same as
> Server Tables, or Stored Procedures on the Server, which are accessed via a
> Passthrough Query. I've used some Views, but never found it necessary to use
> Stored Procedures for adequate performance in retrieving data.
>
> And, in my experience in all-Jet databases, with Tables containing tens of
> thousands of records, there is little difference in the response that you
> see at the monitor between saved Queries and SQL. I read that there is a
> _measurable_ difference, and I have not worked with all-Jet databases with
> huge numbers of records, so cannot comment on that environment. Thus, my
> observation would be that your users don't "pay a penalty" for your use of
> SQL instead of saved Queries. It is, IMNSHO, simply a matter of preference
> and suitability for a given programming situation.
>
> Larry Linson
> Microsoft Access MVP[/color]

Thanks for your input.

[David W. Fenton]

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

bouncer@localho st.not (Larry Linson) wrote in
<xvhLb.8912$nC. 6805@nwrddc01.g nilink.net>:
[color=blue]
>I've used some Views, but never found it necessary to use
>Stored Procedures for adequate performance in retrieving data.[/color]

What about using SPs for inserting/updating data?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

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

On Thu, 08 Jan 2004 22:33:21 GMT in comp.databases. ms-access,
dXXXfenton@bway .net.invalid (David W. Fenton) wrote:
[color=blue]
>bouncer@localh ost.not (Larry Linson) wrote in
><xvhLb.8912$nC .6805@nwrddc01. gnilink.net>:
>[color=green]
>>I've used some Views, but never found it necessary to use
>>Stored Procedures for adequate performance in retrieving data.[/color]
>
>What about using SPs for inserting/updating data?[/color]

There are other reasons for using SPs to retrieve data, security and
functionality and sometimes speed.

Security:
an SP will run in the context of the SP's owner, therefore you can
have users retrieve data from tables without them having access to
those tables directly so row and column level security can be
implemented in this way. This can also have the opposite effect if
you're careful though.

Functionality.
See Speed.

Speed.
You may write a view that calls a user defined function, in Access if
that function has no parameters, Access will optimise by calling it
once for the query and using it's return result throughout the records
returned. SQL server will blindly run the function for each row, if
you use a SP then you can control this by assigning a local variable
to the function's return value then use that in the final output.
--
A)bort, R)etry, I)nfluence with large hammer.

[Lyle Fairfield]

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

Trevor Best <bouncer@localh ost> wrote in
news:stosvvovj2 ccrf3r0p99v4lp9 92qr3lvk3@4ax.c om:
[color=blue]
> You may write a view that calls a user defined function, in Access if
> that function has no parameters, Access will optimise by calling it
> once for the query and using it's return result throughout the records
> returned. SQL server will blindly run the function for each row, if
> you use a SP then you can control this by assigning a local variable
> to the function's return value then use that in the final output.[/color]

Power ...?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Larry Linson]

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

"David W. Fenton" wrote
[color=blue][color=green]
> > I've used some Views, but never found
> > it necessary to use Stored Procedures
> > for adequate performance in retrieving data.[/color]
>
> What about using SPs for inserting/updating data?[/color]

In several Access-Jet-ODBC-server database applications on which I worked,
the only stored procedure was the one to return a "next unique id" value for
the tables. That one did both retrieve and update the single value that it
returned, but wasn't for inserting/updating the application data. It was
just a workaround for the fact that the server equivalent of AutoNumber
(different designations for different servers, including Identity and
Serial) was not returned when a new record was created.

And, the users and clients believed that performance was
acceptable/adequate. That's not to say that some other approach might have
been more responsive.

As I've noted elsewhere, this was at the clients' request* because it was
far more difficult for them to find competent contractors for the server
database than to find competent contractors for the Access client
application.

* "insistence " might be a better term, but their
wish was the prime contractor's command

And, I suspect that transportabilit y was another consideration -- these used
various server databases and they might one day have wanted to change.

Now, behind the scenes, generated by the data modeling software, there were
"triggers" in some of these servers that did the actual enforcement of
referential integrity. Those are a type of "stored procedure" but the same
data modeling software would generate appropriate triggers for some other
server database had they decided to change.

Larry Linson
Microsoft Access MVP