Access 2002 to SQL Server 2000 Conversion 1

[BradB]

In about a week, I'm going to start moving most of my Access 2002 databases to SQL Server 2000. I haven't used SQL server since 6.5.

What are some do's and don't in upgrading my databases from Access to SQL Server 2000? I need some experienced people to inform me of some best practices and common pitfalls in the data conversion process.

Most of my Visual Basic 6.0 Application were written in DAO. Do I have a lot of work ahead of me in converting to ADO? How much success have people had with the upsizing tool Microsoft provides?

 

[Machlink]

Hi BradB!

If you donot have experience use Upsizing tool in Microsoft Access 2002 in order to convert data into Sql server. Just use the wizard it wil guide the conversion process.

 

[TheStriker]

Hiya Brad,

Congrats on finally deciding to use a true client/server method for your users. Fortunately there are two ways you can accomplish this scheme. But the first thing you need to do is convert your code to ADO if necessary...then you can go the MDB/MDE route or you can go the ADP (Access Project) route.

For MDB/MDE, you simply export all of your tables to the SQL Server. When this is done, all of your tables will have a globe as an icon to illustrate that you are now using ODBC to access your tables that are now stored on the SQL Server. This could also be done using the 'Upsizing Wizard' as Machlink so eloquently suggested. The user should not know the difference. The best thing about the MDB/MDE scenario is security, all of your code is protected from the user so they cannot edit the functionality of your app. Now you have the power of both Jet (Access) and T-SQL (SQL Server) at your fingertips. However, there are some pitfalls with this scenario. The ole ODBC timeout issue. Make sure you set your timeout property to the highest value possible to allow enough time to process long requests. Another thing to consider is performance. To maximize performance when requesting data from SQL Server, use filters or parameters to minimize network traffic. Try to avoid requesting recordsets (a list of records) from SQL Server as you may find performance unfavorable. However if you must use recordsets, I would suggest using Jet (Access) to process your recordsets. There may be some syntactical conversions that you may have to do such as when using Passthrough queries. You must use T-SQL syntax when using passthrough queries. Search this forum for topics on T-SQL conversions. Nonetheless, SQL Server is at its best when the record requests are minimal.

The Access Project (ADP) route is where you would convert all of your queries to Stored Procedures or 'sprocs'. SQL Server does not support Jet-SQL or Access SQL however it will convert your existing Access queries to 'Views' as long as your queries do NOT have filters or parameters. If your queries do have filters or parameters, they will be ignored by the 'Upsizing Wizard', forcing you to manually create Stored Procedures. Stored Procedures are a more flexible and powerful way to manipulate your data. If you are going to use SQL Server then you must take the time to become familiar with sprocs, believe me it will be time well spent. Also, the nice thing about ADP is that you could create Stored Procedures with the GUI of Access just like in QBE (Query By Example) with queries in Access. Just insert your tables and click and drag fields, assign aliases and parameters are very easy. Then you can change your view to look at how the code is to become familiar with sproc syntax. (Reply to this post if you want to know about filter by form. I have sort of become an expert at that)
Pitfalls include first of all the learning curve of SQL Server as it is NOT as user-friendly as Access and it is not known as a RAD (Rapid Application Development) tool like Access. Your code is not protected like the MDB/MDE method so users can edit your code. There is no GUI to develop applications withing SQL Server. It is strictly a powerful backend RDBMS (Relational Database Management System), nothing more, nothing less.
Performance considerations when making transactions from Access to SQL Server or vice versa is best when the data is parametized to minimize network traffic. Recordsets can now be acheived on either Jet or SQL Server. I would suggest utilizing SQL Server to handle your recordsets as it is much much more flexible and powerful.
As far as success with performing this using the upsizing tool, converting to ADP is virtually impossible without the upsizing tool unlike the MDB/MDE scenario. I hope this was helpful to you and good luck with your new venture. Post back if you have any more questions.

-You cannot solve a problem with the same level of thinking that created it.

 

[BradB]

The Striker,

I appreciate the time you took to answer my question. You're rewarded with a star!

You've given me some VERY valuable information already. The mere mention of an ODBC timeout issue will help tremndously. I probably would have been banging my head trying to figure out what was wrong.

I'm going to have to do some research on converting my DAO apps to ADO. There might be a tool that will make my life easier.

Again, thanks!