99-01-FM-Transferring ACCESS to SQL Server

Document Sample
99-01-FM-Transferring ACCESS to SQL Server Powered By Docstoc
					ACCESS to SQL Server 2008
A database exists already in ACCESS because it is simpler to transport and modify. The
table structure and the data need to be transferred to SQL Server to be used by
Analysis Services software.

Essential Job number 1
You must turn on all SQL services. If you forget to do this, some things will not work later on and the
error messages will not tell you that you forgot to turn on a service.

Simple rule … turn them all on

1 Go to START
2 Run services.msc
3 This screen appears

4 Scroll down to the SQL services and START them all. You won’t be able to start the disabled
services, but the others should be started. Note in this example that SQL Server Agent is not started.
This is important that it be running. Some will stop again until SQL Server starts up.
How to Create a SQL Server Database

                                                  This may vary depending on where you are
To receive the Access Tables                      working. It might be under Microsoft
1 START                                           Find Microsoft SQL Server
           Microsoft SQL Server
                 SQL Server Management Studio

This screen appears
                                                     Server Type is
                                                     Database Engine

                                                   NOTE: your server will have a
                                                   different name depending upon
                                                   which computer you are working

                                                   This is done on a home computer
                                                   called DESK. In the Seneca Labs
                                                   use the following

This procedure is for the Lab T2108 T2110 and works in other rooms.
Before you start you need to know the name of the PC you are working on.
Go to RUN and type in CMD to bring up the command level window.

Enter ipconfig /all

Usually the first line of the long list generated by the above command is the name of the server. Write
down the server name which looks like T2108pc## …where ## or ### represent numbers or numbers
and x to denote the machine you are working on.

2 Connect. ==> This will take a minute to get up and running.

3 This screen comes up.
Expand databases. You might see different databases that were previously created (if any)

4 Right click databases and select NEW

The New Database window appears. Only the left part of it is shown next.

As you fill in the choice of the table name the logical names will also be replaced.
Choose any name as long as you remember it.

5 Select OK wait while it builds the database.
6 You can see it created the database and if you expand the database and the tables you can see it
has not created any tables yet.

7 You now must import the Access database tables.

Right click the new database you just created (FM-2011-3 in the example above)
      Select Tasks
              Select Import Data

8 The SQL Server Import and Export window appears.

On that welcome window choose NEXT

9 Choose a Data Source window appears

Using the drop down Data Source box look for Microsoft Access

10 The window appears
11 Locate the Access database

Don’t bother with a user and password


12 On the next window, Choose a Destination, make absolute certain that the Windows
Authentication is checked, then select NEXT

13 The Specify Table Copy or Query window appears

Select Copy data from one or more tables or views, then select NEXT

14 The Select Source Tables and Views window appears
Check Source and all source tables will be selected.

Select NEXT to go to next window

15 On the Save and Run Package window select Run Immediately and click on NEXT

16 Select FINISH on the next window and the window will begin to process the importing
                                                                        The resulting progress
                                                                        window looks similar to

17 Close

18 The SQL Server Management Studio window appears and you may not see any tables added.

NOTE: This is very common problem later, when you can’t see any data in the cube after you have
made corrections or changes. You need to REFRESH the page

      Look for this symbol at the top of Object Explorer column.

Don’t forget to refresh the page and then expand tables to see the following.
19 Right click on any table (Ex. Sales Fact) and choose Select Top 1000 Rows and the centre pane
will show the SQL and table contents. The data might look a little different, but this is your proof it was
After Transferring Access tables to SQL Server
You can view them and apply relationships

Right Click Database Diagrams and select New Database Diagram if you want to develop
relationships between tables.
(There might be an error or warning message asking to create … select YES or OK)

On the Add Table window select all the tables to be added and CLOSE the window.

NOTE: When you close the Add Table window, the tables spread out and you can’t see all of them on the middle portion
or pane of the window. You will have to scroll and drag them back into a better grouping. You can also drag the left and
right side bars outward to expand the middle pane. You may also want to reduce the box size a little.

Apply Primary Keys to each table if none existed after the importing. Right Click the attribute and
apply a Primary Key. For the SalesFact Table there are 4 attributes that make up the key, Month,
State_ID, Product_ID and Employee_ID (This is tricky to do until you learn it.)

Apply Relationships using one of these 2 methods

Method 1
Drag and drop the relationships similar to Access.
Drag Subcategory_ID from the Product table to the Product Subcategory table. These screens will
appear.  NOTE: You always start at the FK and go to the PK in the other table.

Note the foreign key is in the Product table and the Primary key is in Product Subcategory.
Select OK and OK.
Method 2
NOTE: You always start at the FK and go to the PK in the other table.

Highlight Subcategory_ID in the Product table and right click

Select Relationships. The Foreign Key Relationships box opened. Click on ADD

Click on Tables and Columns
Click on … at the end of the line

Change Primary Key Table to Product Subcategory and the key to Subcategory_ID.

Change Foreign Key table to Product to Subcategory_ID

Click OK

Click CLOSE and the relationship is built.

Do the rest of the relationships.
At the end your screen should look like similar to this

 You will get lots of practice doing this and it will take only a few minutes each time.

 NOTE: The type of Data Warehouse you have is called SNOWFLAKE SCHEMA

SAVE your work. (File  Save All)

If you get an error message about saving all

Tools  Options –> Designers –> Tables and Designers
Uncheck the Prevent Saving Changes that require table re-creation option.

For those working on Seneca machines you will need to save your work and off-load it so that you
can retrieve it later when you do the other labs.


Continue to the other labs

To save the files, do a search to find where it put your files. Remember the path name so you can put
them back later. Copy them to your USB

Might be
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

Shared By: