<img src=../images/mxzone/forum/icon_smile_question.gif border=0 align=middle> Has anyone here upgraded the Access database that comes with Learning Site to a SQL database?

I am currently working on the conversion, and would like to discuss the process with someone who has been through it before.

Thanks in advance!

Jhan

Hi Jhan,

I did this one and to be honest, it couldn't be easier.
Just startup the enterprise manager, connect to your SQL instance, expand the databases folder and Right click on the databases folder to select All tasks -&gt; Import data.
In the DTS wizard (Data Transformation Services) press next on the first screen;
<ul><li>In the DTS wizard (Data Transformation Services) press next on the first screen; </li>
<li>In the second screen we select the Access driver as datasource and navigate to the .mdb file and we enter username and password</li>
<li> In the third screen we select the destination source and server instance and we fill in our login info then we select new database, in the popup window we give it a name and a starting size for data files and log files </li>
<li> in the next screen we tell it to copy tables and views (if any) </li>
<li> in the next screen we want to select ALL objects</li>
<li> and in the last window we select "run now" and maybe save the DTS package for future use</li>
<li> in the last window we press on Finish and SQL imports the database with everything intact</li></ul>

When SQL is done, we press ok and we right click the databases folder and select Refresh and our new SQL learning Dbase is available. All that's left now is to create user and (if you don't have any yet) a SQL server login for the IUSR account so the webserver can access the SQL server. You can Finetune the permissions that the webserver will have to work with the data.
I prefer to grant NO permissions on the tables for the webserver, I do queries through views which has some benefits as well.

All that is left now is edit learningsite (from the site panel) and alter your database connection to make contact with the SQL server which can be done through the datasource.

If you have any questions feel free to ask, I'll do my best to assist (and i'm sure others will too).
Also alot of learning questions are asked in the extension forums:
webforums.macromedia.com/dreamweaver/categories.cfm?catid=190
webforums.macromedia.com/dreamweaverultradev/categories.cfm?catid=192
Here is another good source for eLearning info if you are just getting started:
www.trainingcafe.com/macromedia/elearning/index.htm

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Thanks Dennis!

I'm keeping your note for future reference, as my MIS department finally decided that the Access database is okay for now. They didn't like the Access database and were after me to get Macromedia to provide a script to create the DB from scratch on SQL.

Your method seems pretty straight forward and simple, and I'll tuck it away for future reference when we outgrow our Access database (which won't be too long from now!)

Jhan

You're very welcome Jhan, this is for your MIS department:

---&gt; begin &lt;---

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'SQL_Learning')
DROP DATABASE [SQL_Learning]
GO

CREATE DATABASE [SQL_Learning] ON (NAME = N'SQL_Learning_dat', FILENAME = N'E:\Microsoft SQL Server\MSSQL\data\SQL_Learning.mdf' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'SQL_Learning_log', FILENAME = N'E:\Microsoft SQL Server\MSSQL\data\SQL_Learning.ldf' , SIZE = 2, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'SQL_Learning', N'autoclose', N'false'
GO

exec sp_dboption N'SQL_Learning', N'bulkcopy', N'true'
GO

exec sp_dboption N'SQL_Learning', N'trunc. log', N'true'
GO

exec sp_dboption N'SQL_Learning', N'torn page detection', N'true'
GO

exec sp_dboption N'SQL_Learning', N'read only', N'false'
GO

exec sp_dboption N'SQL_Learning', N'dbo use', N'false'
GO

exec sp_dboption N'SQL_Learning', N'single', N'false'
GO

exec sp_dboption N'SQL_Learning', N'autoshrink', N'false'
GO

exec sp_dboption N'SQL_Learning', N'ANSI null default', N'false'
GO

exec sp_dboption N'SQL_Learning', N'recursive triggers', N'false'
GO

exec sp_dboption N'SQL_Learning', N'ANSI nulls', N'false'
GO

exec sp_dboption N'SQL_Learning', N'concat null yields null', N'false'
GO

exec sp_dboption N'SQL_Learning', N'cursor close on commit', N'false'
GO

exec sp_dboption N'SQL_Learning', N'default to local cursor', N'false'
GO

exec sp_dboption N'SQL_Learning', N'quoted identifier', N'false'
GO

exec sp_dboption N'SQL_Learning', N'ANSI warnings', N'false'
GO

exec sp_dboption N'SQL_Learning', N'auto create statistics', N'true'
GO

exec sp_dboption N'SQL_Learning', N'auto update statistics', N'true'
GO

use [SQL_Learning]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Activities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Activities]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Activity_Detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Activity_Detail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Activity_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Activity_Status]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Admin_Data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Admin_Data]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Session_IDs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Session_IDs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student_Data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student_Data]
GO

CREATE TABLE [dbo].[Activities] (
[ActivityID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ActivityName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActivityURL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActivityDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Activity_Detail] (
[ActivityID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Question] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserResponse] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Result] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuestionTime] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuestionDate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeInteraction] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CorrectResponse] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseValue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Latency] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectiveID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Activity_Status] (
[ActivityID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActivityName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalTime] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActivityDate] [smalldatetime] NULL ,
[Score] [float] NULL ,
[Location] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Admin_Data] (
[UserID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Session_IDs] (
[SessionID] [int] NOT NULL ,
[UserID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActivityID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Student_Data] (
[UserID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

---&gt; END &lt;---

Just keep in mind that if you start your production site in Access then you're best off to upsize it, if you run this script you will end up with everything but the data and upsizing it will also import existing data <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
And offcourse change the name and file locations before you run this script.

Good luck !

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Jhan,

if you're still having problems with upsizing, look here please:
www.dmxzone.com/go?4131
let me know if you have other SQL issues you want to discuss <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Hungry ? <a href="www.thepattysite.com/dwg_main.cfm" target="_blank">Visit
Dreamweaver Gourmet</a>