Re: Should I worry that my Access to SQL conversion has gone so well?
- From: "Pat Hartman\(MVP\)" <patsky@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 1 Aug 2006 21:58:03 -0400
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.
"Bobby" <bobby2@xxxxxxxxxxxxxxxx> wrote in message
news:1154441774.158154.132940@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Prev by Date: Re: Should I worry that my Access to SQL conversion has gone so well?
- Next by Date: Re: Converting Access 97 - Acess2003/VB 6.0
- Previous by thread: Re: Should I worry that my Access to SQL conversion has gone so well?
- Next by thread: Re: Should I worry that my Access to SQL conversion has gone so well?
- Index(es):
Relevant Pages
|
