Converting from Access to MS SQL Server

[plh]

Hello Everyone,
What sort of issues arise when converting a database from Access to MS SQL
Server? I know nothing of the latter, but this issue has come up where I work,
because the company wants to take some legacy Access databases and move them to
the MS SQL Server system. Some of these databases are, well, let's just say that
they were not put together in as skillful a manner as one would wish. (In fact,
I have looked at the structure of a couple of them and there are some hack jobs
out there.) Are there specific features or techniques used in Access that make
the process of conversion difficult? Are there reliable sources of information
on line?
Thank you,
-plh

 

[Rick Brandt]

Hello Everyone,
What sort of issues arise when converting a database from Access to
MS SQL Server? I know nothing of the latter, but this issue has come
up where I work, because the company wants to take some legacy Access
databases and move them to the MS SQL Server system. Some of these
databases are, well, let's just say that they were not put together
in as skillful a manner as one would wish. (In fact, I have looked at
the structure of a couple of them and there are some hack jobs out
there.) Are there specific features or techniques used in Access that
make the process of conversion difficult? Are there reliable sources
of information on line?
Thank you,
-plh

First off you must realize that all you can "convert" is the tables. You
can store your data in SQL Server tables instead of Jet (mdb) tables.
However; once you move the data to SQL Server you still need a user
interface and SQL Server has no tools for building that. So...is it your
intention to continue to use Access as the interface to the tables?

That is a perfectly legitimate thing to do, but if you have "hack job"
Access applications that would likely mean that both the table structures
and the interface portion are equally messed up and moving the tables to SQL
Server is not going to solve either problem.

The primary reasons to move to a server database like SQL Server are better
security, scalability (both amount of data and number of concurrent users),
and less chance of data loss due to file corruption. Are these the problems
that your organization is trying to address?

I would take this opportunity to pick each database application one at time
and rebuild them with properly designed tables on the SQL Server and
properly designed Access applications interfacing with them. Let management
decide which ones to give priority to. Otherwise you are just converting
bad Access apps into bad Access apps that just happen to store their data on
a SQL Server.

 

[plh]

First off you must realize that all you can "convert" is the tables. You
can store your data in SQL Server tables instead of Jet (mdb) tables.
However; once you move the data to SQL Server you still need a user
interface and SQL Server has no tools for building that. So...is it your
intention to continue to use Access as the interface to the tables?

That is a perfectly legitimate thing to do, but if you have "hack job"
Access applications that would likely mean that both the table structures
and the interface portion are equally messed up and moving the tables to SQL
Server is not going to solve either problem.

The primary reasons to move to a server database like SQL Server are better
security, scalability (both amount of data and number of concurrent users),
and less chance of data loss due to file corruption. Are these the problems
that your organization is trying to address?

I would take this opportunity to pick each database application one at time
and rebuild them with properly designed tables on the SQL Server and
properly designed Access applications interfacing with them. Let management
decide which ones to give priority to. Otherwise you are just converting
bad Access apps into bad Access apps that just happen to store their data on
a SQL Server.

Dear Mr. Brandt,
Thank you for your reply.
The reasons to move to SQL Server as as you mentioned, and yes, the intention is
to continue to use Access as the interface to the tables.
Can you steer me to reliable sources of information about what is involved when
using Access as the interface for SQL Server? Also important is information
about what constitutes a properly designed table and relational table structure.
I like to think of my own as properly designed, but as the song says I can deal
with criticism.
Thank you again,
-plh

 

[Rick Brandt]

Dear Mr. Brandt,
Thank you for your reply.
The reasons to move to SQL Server as as you mentioned, and yes, the
intention is to continue to use Access as the interface to the tables.
Can you steer me to reliable sources of information about what is
involved when using Access as the interface for SQL Server? Also
important is information about what constitutes a properly designed
table and relational table structure. I like to think of my own as
properly designed, but as the song says I can deal with criticism.
Thank you again,

Kind of a tough question, because it depends on who is asking. What is of
paramount importance in a client server database app is limiting the total
bytes that are pulled across the network. However; what is of paramount
importance in a networked file/share app is ALSO limiting the total bytes
that are pulled across the network.

What I'm getting at is that if one takes a poorly designed Access app that
works "pretty well" on a single desktop and tries to make it into a proper
client server application with a SQL Server back end it might very well need
to be completely re-written. But, if one takes a properly designed
networked file/share Access Application and revises it to put the tables on
a SQL Server it might need zero modifications.

What is important is using all "best practices" for a networked, shared
application. If one does that then whether the data is in a shared MDB or
in a SQL Server database will not make that much difference beyond the
security, scalability, etc., issues described previously.

Assuming a "proper" app to start with I recommend setting up the tables on
SQL Server, transferrring the data, replacing all Access/Jet tables and
links with ODBC links to the new tables on the SQL Server and then see what
you've got. Many areas of the application will "just work" and you won't
have to do anything else. Depending on the amount of data and what you are
doing with it you will likely have some areas that will need some tweaks
here and there to make them work and some areas that will function, but
might need additional work to speed them up and to take full advantage of
the server back end.

It is certainly not true that moving to SQL Server means doing everything
with Views, Passthrough queries, and Stored Procedures. Those all become
*additional* tools that are at your disposal and they do have their place in
an Access App with a server back end, but those can almost always be
introduced gradually and only where they actually provide a benefit. Most
people who do this are surprised just how well an application can work
without using any of those.

 

[plh]

Kind of a tough question, because it depends on who is asking. What is of
paramount importance in a client server database app is limiting the total
bytes that are pulled across the network. However; what is of paramount
importance in a networked file/share app is ALSO limiting the total bytes
that are pulled across the network.

What I'm getting at is that if one takes a poorly designed Access app that
works "pretty well" on a single desktop and tries to make it into a proper
client server application with a SQL Server back end it might very well need
to be completely re-written. But, if one takes a properly designed
networked file/share Access Application and revises it to put the tables on
a SQL Server it might need zero modifications.

What is important is using all "best practices" for a networked, shared
application. If one does that then whether the data is in a shared MDB or
in a SQL Server database will not make that much difference beyond the
security, scalability, etc., issues described previously.

Assuming a "proper" app to start with I recommend setting up the tables on
SQL Server, transferrring the data, replacing all Access/Jet tables and
links with ODBC links to the new tables on the SQL Server and then see what
you've got. Many areas of the application will "just work" and you won't
have to do anything else. Depending on the amount of data and what you are
doing with it you will likely have some areas that will need some tweaks
here and there to make them work and some areas that will function, but
might need additional work to speed them up and to take full advantage of
the server back end.

It is certainly not true that moving to SQL Server means doing everything
with Views, Passthrough queries, and Stored Procedures. Those all become
*additional* tools that are at your disposal and they do have their place in
an Access App with a server back end, but those can almost always be
introduced gradually and only where they actually provide a benefit. Most
people who do this are surprised just how well an application can work
without using any of those.

Dear Mr. Brandt,
I greatly appreciate your response. It has already helped me deal more
constructively and effectively with my managers, in that they can see the
importance of not waiting until the application is "up and running" before
moving to SQL Server. Your statement,

Assuming a "proper" app to start with I recommend setting up the tables on
SQL Server, transferrring the data, replacing all Access/Jet tables and
links with ODBC links to the new tables on the SQL Server and then see what
you've got.

is especially helpful in this regard.

However, one rather important question remains unaddressed:I would greatly appreciate references to books, articles, web pages, or training
programs.
Thank You,
Paul Hossfield

 

[Douglas J. Steele]

I would greatly appreciate references to books, articles, web pages, or
training
programs.

The definitive book is probably "Microsoft Access Developer's Guide to SQL
Server", by Andy Baron and Mary Chipman.

See, for example,
http://www.amazon.com/Microsoft-Acc...ef=sr_1_2/105-3485522-4610025?ie=UTF8&s=books