OK time to switch my first DB to SQL Backend

[travismp]

I have a few access 2000 DBs that need to be switched over to SQL 2005.

I want to host the tables on my SQL server and keep all queries, forms, reports, ect in access. I have my SQL 2005 server up and running, I have the Access DBs on a file server.

What is the best way to get the tables into SQL then link back to the Access DBs? What is the best connection type to use and how should do you set it up? I am willing to buy a good book if there is one specifically for SQL 2005 to Access 2003. Thank you all.

I will add that I have about 30 users, my DB has about 400,000 records, 4 or 5 tables in the main DB that I will switch last.

 

[MSAccessRookie]

One good Tool is the MicroSoft SQL Server Migration Assistant for Access (SSMA). It performs all of the migration work for both the database tables and the actual data, evaluating the resulting Database, making recommendations and modifications as it finds the need to do so.

It will also convert limited SQL code to views when you get to the point of moving some of the code to the server to improve efficiency.

I am finishing a project similar to yours (Access 2003 to Server 2005) except that we have about 30 users, the DB has about 300,000-400,000 records, and 40 or 50 tables in the main DB. We converted everything at the same time into our development server and we expect to do the turnover during the upcoming July 4 weekend.

The initial conversion using SSMA was relatively easy. There were a few column names that were non standard and needed to be changed, but other than that, it went very smoothly.

 

[pbaldy]

A book I really like is the Microsoft Access Developer's Guide to SQL Server, by Chipman and Baron.

I haven't used the tool recommended by MSAccessRookie, so I'm not saying it's better, but you can also use the Upsizing wizard in Access. Perhaps somebody who has used both can weigh in on the merits of each.

 

[MSAccessRookie]

I will offer another vote for the Chipman and Baron Book. I have a copy of it on my desk right now.

I cannot compare the two SQL Migration tools, but I can say that I am a consultant, and before I arrived at this location they had tried the Upsizing Wizard and it was not successful for them. Perhaps it might be for you.