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.com> 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.microsoft.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.spam.cox.net>
wrote:

On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<re************************************@ihug.co.n z> 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.spam.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 programmatically 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

[Steve Jorgensen]

On Wed, 07 Jan 2004 04:38:41 GMT, Tony Toews <tt****@telusplanet.net> wrote:

Tom van Stiphout <to*****@no.spam.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.

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.

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 programmatically copy your query to SQL Server. Thus many don't
need rewriting.

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.

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.

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.
But I don't see at all why you'd want to use VB in this. Or ADO if everything is
working in DAO.

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]

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.CopyFromRecordset
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]

On 07 Jan 2004 07:49:30 GMT, Rich P <rp*****@aol.com> wrote:

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.CopyFromRecordset
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!

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]

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" <re************************************@ihug.co.nz > wrote in message news:<bt**********@lust.ihug.co.nz>...

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

[Mark B]

> 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" <to*****@no.spam.cox.net> wrote in message
news:50********************************@4ax.com...

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 mdbbackends.

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

[Tom van Stiphout]

On Wed, 07 Jan 2004 04:53:43 GMT, Steve Jorgensen
<no****@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]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message news:k3********************************@4ax.com...
SNIP

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.

Steve, I have read many, many of your posts over the years in
comp.databases.ms-access, and microsoft.public.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]

On Wed, 07 Jan 2004 06:56:09 -0700, Tom van Stiphout <to*****@no.spam.cox.net>
wrote:

On Wed, 07 Jan 2004 04:53:43 GMT, Steve Jorgensen
<no****@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.

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]

rp*****@aol.com (Rich P) wrote in
<3f*********************@news.frii.net>:

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.

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]

to*****@no.spam.cox.net (Tom van Stiphout) wrote in
<50********************************@4ax.com>:

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.
Possibly even performance penalties in many instances.
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.

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]

rp*****@aol.com (Rich P) wrote in
<3f*********************@news.frii.net>:

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.CopyFromRecordset
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).

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]

> 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" <to*****@no.spam.cox.net> wrote in message
news:50********************************@4ax.com...

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 mdbbackends.

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

[Larry Linson]

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
"outstanding 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]

Tony Toews wrote:

Unless your query has a function or is relatively complex, however complex is
defined, you can programmatically 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.

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.Form.Recordsource = strSQL
Should these be queries also?

[Larry Linson]

"Salad" wrote

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.
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.
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.Form.Recordsource = strSQL
Should these be queries also?

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
"Passthrough 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]

Larry Linson wrote:

"Salad" wrote

> 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.

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.

No, I wasn't.

> 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.Form.Recordsource = strSQL
> Should these be queries also?

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
"Passthrough 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

Thanks for your input.

[David W. Fenton]

bo*****@localhost.not (Larry Linson) wrote in
<xv****************@nwrddc01.gnilink.net>:

I've used some Views, but never found it necessary to use
Stored Procedures for adequate performance in retrieving data.

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]

On Thu, 08 Jan 2004 22:33:21 GMT in comp.databases.ms-access,
dX********@bway.net.invalid (David W. Fenton) wrote:

bo*****@localhost.not (Larry Linson) wrote in
<xv****************@nwrddc01.gnilink.net>:

I've used some Views, but never found it necessary to use
Stored Procedures for adequate performance in retrieving data.

What about using SPs for inserting/updating data?

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]

Trevor Best <bouncer@localhost> wrote in
news:st********************************@4ax.com:

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.

Power ...?

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

[Larry Linson]

"David W. Fenton" wrote

I've used some Views, but never found
it necessary to use Stored Procedures
for adequate performance in retrieving data.

What about using SPs for inserting/updating data?

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 transportability 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