Web Wiz Forums - converting from access database

converting from access database

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=28349
Printed Date: 02 April 2026 at 12:24am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: converting from access database

Posted By: Binji
Subject: converting from access database
Date Posted: 23 February 2010 at 2:39am

First of all hello to all and i hope you will be able to help me.

I have tried upscaling access database to MSSQL server and the data in the database seems ok but when i open the forum none of the special utf8 characters are displayed properly. Its the same thing with MySQL.

I tried fresh install and posting something with special characters which displays fine. However in the database its not displayed as it should be.

So Im guessing this forum uses a different encoding for database connection? If so which one and can i use utf8 to connect to my database?

Replies:

Posted By: WebWiz-Bruce
Date Posted: 23 February 2010 at 8:52am

There is not a different connection string for the database for UTF-8 encoding.

You did not mention which tolls you used to migrate the database, as this has a lot to do with keeping the encoding the same.

I've always found the upsize wizard in Access is very good at upsizing the database, however for SQL Server 2008 you need to use 'Microsoft SQL Server Migration Assistant 2008 for Access' which is more tricky as you have to go through the database after and remove any constraints that should not have been created.

You should also check the collocation of your database. Unless you changed it by default it shoudl be Latin1_General_CI_AS which I find works fine for UTF-8. You should also check that the data type for string fields is nvarchar as this also supports UTF-8.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Binji
Date Posted: 23 February 2010 at 12:18pm

Sorry... the server version is 2005.
I imported the data as latin1 and it works so far. Except for a few errors in import process anyway. I imported it in mySQL with Navicat Premium.
Gonna try to import it in MS SQL next...

Posted By: WebWiz-Bruce
Date Posted: 23 February 2010 at 12:37pm

The mySQL version uses a very different schema than the Access and SQL Server versions and so you are unlikely to migrate the database from Access to mySQL and have a stable forum.

You should instead go from Access to SQL Server as the database schemas are very much the same.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Binji
Date Posted: 23 February 2010 at 7:43pm

I will if you tell me how to do that. If i upscale from Access it migrates fine but all the special characters are messed up. And i cant find any way to first convert everything to latin before migrating. Not even if i set the mssql database collation to latin.
With MySQL i dumped the database, converted it into ANSI and reimported it. Here it seems id sooner invent my own database than accomplish this....

Posted By: WebWiz-Bruce
Date Posted: 24 February 2010 at 10:20am

I already said in a previous post regarding the datatypes used for the storage of text data using nvarchar

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Binji
Date Posted: 31 March 2010 at 11:26pm

Hi its me trying my hand at it again..
So this is what i did:
created new MSSQL database (it created it as Slovenian_CI_AS as default so i changed it to Latin1_General_CI_AS)
used migration assistant to migrate data
installed forum as an upgrade
result: still not ok :( If i set encoding to Win-1250 its almost right but i dont get any č, ć characters. I did set the data types translations to nvarchar where possible.
Which constraints would i have to remove and how do i remove them?

Then i created MySQL database as UTF8 and imported database structure. Then i imported data from Access DB using Navicat 8 but it could import all of it because of errors (probably due to encoding differences - data too long for some columns). I know i already managed to import it without errors but i have no idea which encodings i used...
What i did get still had same problems as MSSQL database so i tried replacing all characters with characters forum recognizes. Which worked except that search doesnt work (i assume this is responsible because i tried searching on a fresh install and it works) and of course the fact of missing records...

Right now im probably gonna try also replacing characters in database in MSSQL with Win-1250 and UTF8 characters and see it if works...

edit: turns out some of the errors are also of type "Duplicate entry 'some entry' for key 3" for tblauthor for example... seems database restraints are the problem since if i import directly without first creating a structure the import goes fine (except still for some invalid values errors...). The forum also doesnt work properly aftewards of course...

edit2: seems the "Duplicate entry" is due to the fact some of the users have special characters in their username and something in the import process cannot handle that. For example it seems to take boštjan and bostjan as same value... Im not 100% but fairly certain that is the case..