Should I worry that my Access to SQL conversion has gone so well?

[Bobby]

I've been set the task of converting a 20 user Access 2003 database to
SQL server 2000.

The original access version is two databases, a front end and a back
end. The front end uses about 40 forms. Without using the upsizing
wizard, I have simply imported the backend into SQL, made a few changes

to data types, and set a few primary keys etc., and then linked the
front end to the SQL database using ODBC.

The two main issues I have encountered are 1) poor performance due to
various counts and sums in fields on forms, (so I have removed these or

found work arounds) and 2) I have a sub form which uses an access query

with two tables. In Access the sub form will allow me to add new items,

but in SQL it won't. I'm not sure why yet, but can probably find a work

around to this. Any suggestions would be gratefully received.

Now that I have resolved issue 1 (above) everything seems nice and fast

(certainly no slower than the access version was), and although I still

have a lot more testing to complete, my 6 month project seems to have
been almost completed in a week.

Should I be worried??? It seems too easy. Have I gained anything my
moving from an Access back end to SQL in this way? Before I started I
was given all kinds of dire warnings about having to re-write my front
end in VB.NET. Why was it so easy??? Something nasty must be waiting
around the corner. Any suggestions what it might be?


Bobby

 

[Jeff Boyce]

Bobby

Having migrated over 10 applications from an Access/JET back-end to a
SQL-Server back-end, then re-linking from the front-end(s) for my day job,
you seem to have covered it all. There might be a few more "tuning" issues,
but I don't recall anything other than performance and SQL-Server
permissions issues that bit me.

I'd be curious, though, what qualifications the person had who told you that
you would have to rewrite the front-end in VB.NET? Any chance s/he was a
VB.NET programmer?<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hartman\(MVP\)]

No worry, it really is that simple to do the conversion but simply
converting a typical Access application to use SQL Server (or other RDBMS)
tables will not engender any performance improvements and may in fact be
slower than the original Access/Jet database.

To gain performance improvements, you need to make the fe take advantage of
having a real server as the be. To do that, all forms/reports must be based
on queries that have selection criteria to drastically reduce the data
retrieved from the server. You can use the where argument of the OpenForm
or OpenReport method to restrict the data retrieved from the server or use a
where clause with parameter in the form's RecordSource. But filters, which
are frequently used in native Jet applications, are applied locally and
require the entire table to be retrieved from the server. So, filters are
almost never used when the data is in a non-Jet back end.

You also need to be careful about where you use user defined or VBA
functions in your queries. Jet attempts to send all queries to the server
for processing but certain things prevent that and one of them is using
functions in the where clause that have no SQL equivalents. This causes Jet
to ask the server to return ALL rows from the table so that Jet can apply
the criteria with the function locally.

There are articles in the KB and/or MSDN library that discuss client/server
optimization for Access. That will give you some ideas on what you should
change. But you won't need anything close to 6 months to complete this
task.

 

[Albert D. Kallal]

You got some good comments, and I will add a few more:

Your sub form editing problems will likely go away if you make sure that
ALL tables have a primary key, and also have a time stamp field. These
fields need to be exposed to the access form (and sub-form). When you do
this, you should get your editing, and adding ability back (access uses the
time stamp field to figure out what has been changed..and if no timestamp
field..it looks at all of the fields.....and makes life difficult for
ms-access). In fact, you don't usually even have to change your
forms/sub-forms from the tables to queries..but, you do need to have the pk
+ timestamp field exposed for smooth operations...

the above apples to most forms....they need that time stamp AND a PK field
exposed. This will ensure smooth editing from ms-access.

Should I be worried??? It seems too easy. Have I gained anything my
moving from an Access back end to SQL in this way?

Well, if you needed to move to sql server, then the above is a good gain. IF
you did not need to move to sql server, the you gain nothing (except a more
time consuming sql server system to maintain in addition to your software).
Unless your software is multi-user, and has a heavy load of many users, then
sql is not always needed. I mean, with 10 users on a cheap office network,
ms-access will absolute scream in terms of performance. However, sql server
is more reliable, and does not suffer from corruptions like a JET based
appcation can. Further, sql server can scale to more users with more
reliability. So, did you gain?...well, only you can answer this, and that
answer is going to depend on your given situation.

However, moving a stand alone JET based appcation to sql server will most
certainly run slower then a JET based one. Often, the JET will run about two
times as fast as a sql based application (same machine, same hardware).

Before I started I
was given all kinds of dire warnings about having to re-write my front
end in VB.NET. Why was it so easy??? Something nasty must be waiting
around the corner. Any suggestions what it might be?

There is no big deal here. Only those people with their noses high in the
air that look down on ms-access think that you must use sql server for
everything, and that the JET database engine with ms-access is a toy.

However, what is really nice is that at the end of the day, ms-access is
great front end to sql server.

 

[Bobby]

Thanks to everybody for their comments and suggestions. Everything is
progressing very well with the conversion at the moment, and you have
given me plenty to think about, but it certainly looks positive so far,

Thanks again

Colin

 

[Guest]

Access does some magic behind the scenes to allow you to
add records to two related tables at the same time. This magic
depends on the fact that Access knows what the new auto
number value will be, even before it has been assigned.

That doesn't work with SQL Server (by design, there is a
good reason). You will have to think about what you are trying
to do, and use code or a stored procedure.

Also, dao transactions don't work. If you have dao transactions,
you will have to remove them. You might not notice this at first.

(david)