Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Step by Step v7.x Access to v8.01 SQL
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Topic ClosedStep by Step v7.x Access to v8.01 SQL
 Post Reply Post Reply Page  12>
Author
  Topic Search Topic Search  Topic Options Topic Options
superlative View Drop Down
Groupie
Groupie

Not Comparative, I m Superlative :)

Joined: 26 November 2004
Location: Turkey
Status: Offline
Points: 125 		Direct Link To This Post Topic: Step by Step v7.x Access to v8.01 SQL
    Posted: 14 January 2006 at 8:51pm
Hii Everyone;
 
I want to write my upgrade process. I hope helpful for who migrate to WWF v7.7 to WWF v8 beta 1. This is not offical migrate document.
 
My WWF Scheme and System
 
Currently I use WWF 7.7 (MS Access) in our web portal. But I fix some dangerous bugs (I changed some asp pages newest one)
 
My hosting company supply SQL. But I use MS Access version. Because I m waiting final version of V8 for Access to SQL. My portal server Windows 2003 Server.
 
In my home, I have got Windows 2003 Server Enterprise and SQL 2000 SP4. Currently I am working my copy of portal. Settings and paths same as My hosting company server's. This very important for improving.
 
Today, I download WWF V8 Beta SQL and Access editions. First, I extract Access version. And take copy my original v7.7 access db. Then I put V8 forum as "forum2" folder. v7.7 as still "forum" folder. I examine new db structure. First Borg says in latest version history file:
 
Quote Changes to tblForum
New fields:-
1. Sub_ID - data type = interger - default value = 0
2. Display - data type = interger
3. Last_post_author_ID - data type = interger - default value 1
4. Last_post_date - data type = date/time - default value = now()/GetDate()
 
I can not find Display column in tblForum. I changed my v7.7 db structure to V8. While I do this, I open V8 mdb and v7.7 mdb and copy from one to other in design view. After all of completed. I change db files. And I test in my web server. First I can not see any forum in forum main page. This problem occur when your group/forum permission wrong. If yo dont give forum moderator/ Approve permission to any forum, you don't see this forum in forum main page.
 
I change my old forum permissions to new ones. New permission system perfectly. Thanks Brouce great work, but you debtor left mouse button to me Wink. I advice, you set group permissions page instead of forum admin page. Because this is very easy, I select first Administrators. And set all forum permissions for this group. Then i select guests and etc... New permission system is great Clap. Looks like NTFS ACL (Access Control List).
 
Permissions is not enough for the see all forums in forum main page. You must change tblForum data check it bellow :
  • Last_post_author_ID, Last_post_date and Sub_ID colums must do not empty. I set all entries in tblForum :
  • Last_post_author_ID = 1 (Not important you can set any Author)
  • Last_post_date = 14.01.2006 21:01:23   (Not important you can set any time)
  • Sub_ID = 0
 
Currently I can see my all old (v7.7 db) forums on forum main page. But I see another problem. When I click any forum in the main page, I dont see any topic. I know reason. New columns in tblTopic :
  • Start_Thread_ID
  • Last_Thread_ID

I stoped in this situation. I examine how to fill this values. I want to write an asp page find start Thread Id and Last Thread ID and write them to suitable columns.

I can see my old PM's I search members and I can see member profiles no problem. I try testing. When I perfectly migrate my 7.7 mdb to 8 beta mdb, I try integration of my portal then I migrate to SQL V8. All of steps will here. 
 


Edited by superlative - 14 January 2006 at 8:56pm

Back to Top
Darbs View Drop Down
Newbie
Newbie


Joined: 09 December 2005
Location: United Kingdom
Status: Offline
Points: 38 		Direct Link To This Post Posted: 14 January 2006 at 10:44pm
I got a old version and a new running in the same SQL DB - here is a SQL cursor script I wrote to do this but it's completely at your own risk as it was for me Wink.  If you are using access then I'm
sure you can read the info and work out what you have to update (and how) from this SQL script....
 
declare @@TID as int
declare @@iMin as int
declare @@iMax as int
BEGIN
-- Create a cursor to scroll down tblThread and pull each Topic_ID
DECLARE c1 CURSOR FAST_FORWARD READ_ONLY FOR SELECT Topic_ID FROM tblTopic
OPEN c1
FETCH NEXT FROM c1 INTO @@TID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Update min and max in tblThread here!!   
select @@iMin = (select min(thread_ID) from tblThread where Topic_ID=@@TID)
select @@iMax = (select max(thread_ID) from tblThread where Topic_ID=@@TID)
update tblTopic    set Start_Thread_ID = @@iMin, Last_Thread_ID = @@iMax where Topic_ID=@@TID
-- End Update   
FETCH NEXT FROM c1 INTO @@TID
END
CLOSE c1
DEALLOCATE c1
END


Edited by Darbs - 14 January 2006 at 10:59pm
Back to Top
superlative View Drop Down
Groupie
Groupie

Not Comparative, I m Superlative :)

Joined: 26 November 2004
Location: Turkey
Status: Offline
Points: 125 		Direct Link To This Post Posted: 15 January 2006 at 9:29am

Today I test Cem's upgrade script. It works. Check it his topic :

 
Darbs :
 
Your script old SQL db to new SQL db isnt it ?


Edited by superlative - 15 January 2006 at 9:30am

Back to Top
Darbs View Drop Down
Newbie
Newbie


Joined: 09 December 2005
Location: United Kingdom
Status: Offline
Points: 38 		Direct Link To This Post Posted: 15 January 2006 at 12:41pm
Yep is is mate....
Back to Top
superlative View Drop Down
Groupie
Groupie

Not Comparative, I m Superlative :)

Joined: 26 November 2004
Location: Turkey
Status: Offline
Points: 125 		Direct Link To This Post Posted: 26 March 2006 at 4:37pm
I start upgrade process again. I move my old 7.7 forum to new RC1 V8. Currently I translate forum to Turkish. I think group and member permissions have a bug. I explain problem new topic.

Back to Top
superlative View Drop Down
Groupie
Groupie

Not Comparative, I m Superlative :)

Joined: 26 November 2004
Location: Turkey
Status: Offline
Points: 125 		Direct Link To This Post Posted: 16 April 2006 at 6:26pm
Hi All;
 
My upgrade process is still continue. May be this topic become a guide who wanna upgrade V7.x Access to V8.01 SQL.
 
  1. First, I upgrade my v7.x access forum to v8.0 access via Borg's upgrade tool.
  2. Then, I download latest version of WWF SQL version (v8.01)
  3. I install this to the new folder (forum-sql)
  4. Start installation process, Now I have got a blank forum (SQL v8.01). This db name forum-sql
  5. I open SQL Enterprise Manager and run DTS tool.
  6. First I import my upgraded access db (v8.0) to new SQL db (This db is totaly blank.This is not same db in the step 4). This db name is original
  7. After, re-run DTS tool but this time SQL DB to SQL DB. (original to forum-sql)
  8. DTS ask me : Specify Table Copy or Query
  9. I select Copy Objects and data between SQL Server databases, then click Next button
  10. In next step I select default options + I select Include extended properties
  11. Next Next....

Successfuly upgrade my forum v7.x access to v8.01 SQL Tongue

DTS : Data Transformation Services, this tool in the SQL Enterprise Manager under Tools Menu.


Edited by superlative - 16 April 2006 at 6:28pm

Back to Top
superlative View Drop Down
Groupie
Groupie

Not Comparative, I m Superlative :)

Joined: 26 November 2004
Location: Turkey
Status: Offline
Points: 125 		Direct Link To This Post Posted: 16 April 2006 at 8:29pm

My forum doesn't upgrade Embarrassed, I see all forums and users but when I create new forum or any changes i recieve database errors. I think only one way.

Originally posted by -boRg- -boRg- wrote:

The simplest way I have found, but requires MS SQL Server and Access installed on your local machine and a second SQL Server at your web host, is:-
  1. Create the database a blank SQL Server database at your web host
  2. Run the 'msSQL_server_setup.asp' file to create the database tables, Stored Procedure's etc. on your SQL Server database at your web host
  3. Use Enterprise Manager to delete ALL the newly created tables on the SQL Server database created at your web host, just leaving the Stored Procedures.
  4. Now on your local machine open your original Access database in Access
    1. From the Tools menu in Access, select the 'Upsize Wizard'
    2. Choose the Upsize option to 'Create a new database'
    3. Choose 'local as your SQL Server and 'Use Trusted Connection'
    4. Next select ALL the tables to upsize
    5. Leave all other options as default
  5. Once your database tables have been upsized using this method, use the 'Export Tool' in Enterprise Manager to upload the tables to the SQL Server database at your web host which you created the Stored Procedures in earlier
  6. That should be it
This is the only method that I have found so far that works for me.

I did look into witting an ASP application that could transfer files across, but the problem is that the primary key for many tables is an Auto number, which means if there are any deleted posts, topics, members, etc. it would screw up the transfer process.
 
I try this after step 5. Access push datas to SQL and currently works very well. But I dont test all moduls yet.

Back to Top
fatih.koz View Drop Down
Newbie
Newbie
Avatar

Joined: 04 June 2004
Location: Turkey
Status: Offline
Points: 15 		Direct Link To This Post Posted: 17 April 2006 at 2:59pm
Hi Superlative ,

Upgrading and Upsizing is very easy ,  at least I did not face any problems till today ;

Here is what I did (nearly similiar to Borg's )

Updating From 7,xx Access to V8 ; ( Done On Local Server )

- Download the Upsizing asp page from the forum
- Run the Access_Update.asp file
- Install the v8 Access Edition
- Test ( Works OK )

Updating from v8 Access to v8 SQL ( Local Server )

- Run Access Upsizing Wizard
- Install v8 SQL edition
- Test ( Works OK )

Uploading to public server and going online

- Do general maintenance on local sql server
- Backup the database
- Zip it ;)
- Upload to public sql server
- Create a blank database with same details for V8
- Restore your full database
- Upload v8 SQL to public server
- Test

If you can not restore backups on online SQL server , contact your host tech support for this . It will take a little time for them ;)

It worked without errors with the power and speed of SQL Database .

If you have a backup copy of your older v7.x database give it a try .

Best wishes ,
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.