Migrating from Access to SQL Server

[oumahexi]

Hi

We are going to be moving our Access database over to SQL in a few weeks and I wonder if anyone has any advise they can offer? Are there any pitfalls we should look out for etc.

The situation currently has the database storing data on SQL with Access doing most of the work, I know, I know. So a lot of the Access queries, presumably, will be become SQL Views.

Has anyone ever done this? I'm sure we can't be the first.

Many thanks in advance.

 

[boblarson]

One thing to be aware of is that you will want to use stored procedures for some things (to access them you need some ADO code if you are keeping the Access frontend as an mdb file and not adp. I would suggest staying with the mdb format, myself).

 

[oumahexi]

One thing to be aware of is that you will want to use stored procedures for some things (to access them you need some ADO code if you are keeping the Access frontend as an mdb file and not adp. I would suggest staying with the mdb format, myself).

Thanks Bob, much appreciated. In the ideal world, we'd front end it with a web based application, but, unfortunately, this is not an ideal world so we'll probably be keeping Access.

Presumably this exercise will help the system to work faster on the client side?

Thanks again

 

[boblarson]

If you transition to using stored procedures and views and use ADO to access them, then the server can do a lot of the work and that should speed things up for the client.

 

[oumahexi]

If you transition to using stored procedures and views and use ADO to access them, then the server can do a lot of the work and that should speed things up for the client.

This is good news Bob.

Thanks, I now know where to start my research.

 

[Pat Hartman]

I create all my database from the beginning as if they will end up as SQL Server, DB2, Oracle, etc. I rarely need to use views or stored procedures. For probably 99% of my conversions, I just run the upsizing wizard and tell it to link the tables and I'm done So with validation time (I don't trust the wizard), it rarely takes more than an hour.

In order to take advantage of having a RDBMS backend, you will most likely need to change your forms. Forms should be bound to queries and the queries should use criteria to limit the number of rows returned. If your forms are bound to tables or unqualified queries, you get no advantage since Access just sucks every single row in the table over the network. This behavior is what causes many DBA's to not want people to use Access at all. What they fail to understand is that the behavior is totally under the control of the programmer.

One change I found I had to make when converting to SQL 2005 (which I never had to do before) is to add the dbSeeChanges argument to all my inline DAO code.

Search the MS knowledge base for the paper on Optimizing Client/Server applications. It will give you more information on what you may need to change if you didn’t plan ahead.

 

[oumahexi]

I create all my database from the beginning as if they will end up as SQL Server, DB2, Oracle, etc. I rarely need to use views or stored procedures. For probably 99% of my conversions, I just run the upsizing wizard and tell it to link the tables and I'm done So with validation time (I don't trust the wizard), it rarely takes more than an hour.

In order to take advantage of having a RDBMS backend, you will most likely need to change your forms. Forms should be bound to queries and the queries should use criteria to limit the number of rows returned.

Search the MS knowledge base for the paper on Optimizing Client/Server applications. It will give you more information on what you may need to change if you didn’t plan ahead.

Thanks for this Pat. Although the project was started a very long time ago, by another developer, then another after that and another... you get the picture, it appears that it was pretty well thought out in that all forms are bound to queries. The big problem we have is that the current situation has all the data stored in SQL Server, the queries pull data from SQL into Access where other queries are run to manipulate data into required forms and reports, collect new information and pass the results back into SQL Server.

We also have a situation where, as a result of so many developers doing their own thing, there are queries that query queries about queries that query queries (lost? so are we). This makes it a nightmare to try and make any changes to the sytem because you just don't know what might fall over without investing days tracing dependancies.

I will look for the documents you suggest, I'm sure they will be very useful.

Again, thank you so much for your insight.

 

[Pat Hartman]

Get a product called Total Access analyzer from fmsinc.com. It will help you to view an existing database in an organized fashion. You can get dependency information an various other reports that will help.

I frequently nest queries so that isn't necessarily bad. In fact it is better than making a bunch of temp tables which is the alternative.

 

[oumahexi]

Get a product called Total Access analyzer from fmsinc.com. It will help you to view an existing database in an organized fashion. You can get dependency information an various other reports that will help.

I frequently nest queries so that isn't necessarily bad. In fact it is better than making a bunch of temp tables which is the alternative.

Thanks Pat. I'll see if my employers will shell out for that, but I won't hold my breath, it looks like the cost of a meal for one of them