Migrating Complicated Access 2000 DB to SQL Server

[jimgower]

I have 200 tables and hundreds of other objects in Access. There are
linked-tables to backend mdb, vba modules and everything. There are about
20-40 users for the DB through LAN. I know there is no easy way to migrate
the backend to SQL Server. The problem has been around for years. Anyone can
give me some idea of following questions:

1. When I migrate, should I use ODBC linked tables to link the backend SQL
Server DB or should I re-engineer it to ADP?

2. I tried to migrate the backend mdb to SQL Server and to use ODBC linked
tables but the speed became intolerable. I know it was because the frontend
retrieved too many records from ODBC source. I plan to reform the frontend
forms to bind to less records. But again, am I going to the right direction?

3. Will new version of Access (2007) do any good in this situation?

 

[Rick Brandt]

I have 200 tables and hundreds of other objects in Access. There are
linked-tables to backend mdb, vba modules and everything. There are
about 20-40 users for the DB through LAN. I know there is no easy way
to migrate the backend to SQL Server. The problem has been around for
years. Anyone can give me some idea of following questions:

1. When I migrate, should I use ODBC linked tables to link the
backend SQL Server DB or should I re-engineer it to ADP?

You could go either way, but the former is more flexibel and is the more
recommended approach.

2. I tried to migrate the backend mdb to SQL Server and to use ODBC
linked tables but the speed became intolerable. I know it was because
the frontend retrieved too many records from ODBC source. I plan to
reform the frontend forms to bind to less records. But again, am I
going to the right direction?

Yes. Also look at your indexing. Access/Jet oftne preforms so well that many
"sins" go unnoticed. With a "good" design you should not see any performance
penalty using ODBC linked tables.

In the case of quieries with joins you might need to create some views and/or
stored procedures on the server so that the joining is more efficiently
performed on the server.

3. Will new version of Access (2007) do any good in this situation?

No better. No worse.

 

[Pat Hartman \(MVP\)]

1. Definitely use ODBC linked tables. Support for the .adp is diminishing
and new features will not be added even though Access will continue to
support existing applications.
2. Simply porting your data to the server will not improve performance as
you have discovered and in fact may make it worse. There is an article in
the knowledge base on optimizing for client/server that is worth reading.
in addition to binding your forms to queries that return limited sets of
data, you will find other suggestions regarding the queries themselves.
Indexes are also important to help the database engine retrieve records
efficiently.
3. The problem is not with Access. The problem is with your data access
methodology. You would have the same results if your front end were written
in C#.

 

[jimgower]

Thank you so much!

1. Definitely use ODBC linked tables. Support for the .adp is diminishing
and new features will not be added even though Access will continue to
support existing applications.
2. Simply porting your data to the server will not improve performance as
you have discovered and in fact may make it worse. There is an article in
the knowledge base on optimizing for client/server that is worth reading.
in addition to binding your forms to queries that return limited sets of
data, you will find other suggestions regarding the queries themselves.
Indexes are also important to help the database engine retrieve records
efficiently.
3. The problem is not with Access. The problem is with your data access
methodology. You would have the same results if your front end were written
in C#.

 

[jimgower]

Thank you so much!

You could go either way, but the former is more flexibel and is the more
recommended approach.


Yes. Also look at your indexing. Access/Jet oftne preforms so well that many
"sins" go unnoticed. With a "good" design you should not see any performance
penalty using ODBC linked tables.

In the case of quieries with joins you might need to create some views and/or
stored procedures on the server so that the joining is more efficiently
performed on the server.


No better. No worse.

 

[Tony Toews [MVP]]

I have 200 tables and hundreds of other objects in Access. There are
linked-tables to backend mdb, vba modules and everything. There are about
20-40 users for the DB through LAN. I know there is no easy way to migrate
the backend to SQL Server.

You've already received excellent answers for your questions but I'll
also point you to my web page on this topic.

Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page
at http://www.granite.ab.ca/access/sqlserverupsizing.htm

There is a new tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/default.mspx

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