Access to SQL Server - Step by Step

There seems to have been a lot of request for a step by step guide on how to migrate the Access version to SQL Server, so here is my attempt at giving you one.

1. Open Enterprise Manager for the SQL Server

2. Create a blank (empty) database on the SQL Server

3. Go to Tools, Data Transformation Services, Import Data

4. Use the Import wizard to get the info from the Access .mdb file:

    - Choose a datasource: Use DropDown menu and scroll up until you see Microsoft Access
    - Select Microsoft Access from the dropdown list, this will change the options on the tab
    - In the FileName section, browse to where you have the WebWiz database stored and select the file
    - Add a username and or password if necessary (by default it isn't)

    Click Next and this brings you to the Destination tab
    - Leave the default Destination value of "Microsoft OLEDB Provider for SQL Server"
    - Fill in your server information on this tab
    - Don't forget to select your new empty database from the dropdown list at the bottom
   
    Click Next and this brings you to the "Specify Table Copy or Query" tab
    - Select the first option "Copy Tables and Views from the Source Database"
   
    Click Next to see a list of all the tables that you can copy to the new database
    - Select All

    Click Next and let it go.

After all the data is imported (this should be error free, if it wasn't, maybe you already had some data
in your destination database, delete it all and try again)

Then modify the SQL_server_connection.asp file in the Forum/Admin folder

Lines 59, 60, 61 and 62 are the important ones... Just fill in the name of your SQL Server, User
name, password and the name of the empty database that you just populated.

Save your changes and then run the msSQL_server_setup.asp file in the main Forum folder.

This will create all the necessary stored procedures to run the SQL Server version of WebWiz.

It will generate some errors, this is simply because it is trying to create the tables that you've already
imported from the access file. Don't worry about them. The important thing to get out of running this script
are all the stored procedures.

Once the msSQL_server_setup.asp script is finished, you are done.

Check your tables in the SQL Server via Enterprise Manager if you want to make sure the data is there, but
you should be able to log into the forum and use it right away.

This process worked perfectly for me and hopefully it will help you as well but I do not guarantee anything. :-)



Edited by -boRg- - 05 January 2006 at 3:09pm

ukakht wrote: I get this error when sending pm or posting posts ADODB.Recordset error '800a0cb3' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype. /webwiz/pm_post_message.asp, line 306 Please help me

how many members is good enough for moving on to SQL server.......
wat i want to know is that though it is well known that access supports small database sizes with fewer DB calls but exactly how much members & how many daily/simultaneoul hits R  gud enough to move onto SQL ????