Access 2003 to SQL Server 2000 Coversion 1

[neeko1226]

Good afternoon -

Myself and another developer in my department have been tasked with converting an environment containing over 200 MS Access 2003 databases to a SQL Server backend - MS Access frontend environment.

I would like to know if anyone knows of a good training course that we could take to get ourselves comfortable with the hurdles we're about to encounter. We have purchased a few books on the subject, but there's no subtitute for hands on training in my opinion.

 

[mrdenny]

Take a look at the Access upsizing wizard from Microsoft. It will greatly help you with the task at hand.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[neeko1226]

Denny -

Thanks for the Reply. I tried using the upsizing wizard in Access, and it created the tables in the SQL DB. It worked extreemly well and did exactly what we'll need to do.

Are you aware of any things that the other developer and I should look out for after the tables have been converted? Almost all of our applications use vba modules with semi-sophisticated functions. Are there any major stumbling blocks we should be aware of going into this?

 

[SQLSister]

Several things to look out for. First look over the datatypes very carefully - the wizard does not alwys make the best choices. Access has amuch smaller limitonthe number of characters a text-based field can have before you have to go to a memeo field. This wizard may make these all text or ntext fields when in reality they could be varchar or nvarchar. the varchar fields are much easier to extract data out of and query.

Another thing you will need to change include replacing IIF with case statments.

Also look at security, Access is often bad on security and a SQL Server databse should be set up from the start to properly secure the data. Never let the application use the sa account to access the database.

Questions about posting. See faq183-874

 

[neeko1226]

I appreciate the input Sister. I will pay close attention to the converted datatypes and replace any If statements with Select Case statements.

I would still like to find some sort of training class for us to attend if at all possible, so if anyone knows of a good one please let me know.

Thanks.

Neeko

 

[mrdenny]

I would also recommend checking the table indexes and make sure that they are still being used as needed. I'm not sure how the upsizing wizard does (as I've never actually used it) when creating indexes on tables.

Check with your local Junior College. They may have clases which would be of use to you. JCs are usually much cheeper than a regular training center.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[nice95gle]

Other than the obvious reasons you need to look at the business side. You don't just want an Access DB that now sits in Sequel. You want a Db that is going to be easy to change as the needs of the business changes. Because I'm sure one of the reason you are upgrading is due to an increase in users. Talk to the DBA and make clear to him\her your plans, this will help you organzie your course of action. So when your organization changes you will be ready.

In addition ask some simple questions if you have not done so already.

How big is my user environment?
Who is my target audience? (Management might care about things the Average user don't. You don't want to have to go back later on and write a bunch of ALTER TABLE statments)
How much tables do I forecast?
How big can these tables get?
What limitations do my server(s) have?
Is my access code transferable? (As SQLSister said things like IIF will have to change)
Indexing? etc...

Oh, and as for courses if you can't find one soon don't worry about it. With 200 db's to convert by the 6th or 7th one you will know what not to do.

Good luck and have fun.


Well Done is better than well said
- Ben Franklin

 

[SQLSister]

Some other things I forgot to mention. Make sure that any auto generated id fileds are set as Identity fields when you convert them from access. Also, you will want to make sure every table has a Primary key or some kinf of unique index. Otherwise you could run into errors when inserting data (you will for sure if you are using linked tables to an Access front end, but I don't know what the effect on your front end would be, it's a good practice in any case)or you could end up having duplicate data inserted.

Questions about posting. See faq183-874