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
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 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
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
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.
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 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
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA