Print Page | Close Window

Yet again: Converting from Access to MS-S

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=21326
Printed Date: 28 March 2026 at 5:58pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Yet again: Converting from Access to MS-S
Posted By: Pilsener
Subject: Yet again: Converting from Access to MS-S
Date Posted: 17 September 2006 at 5:05pm
I used an XP-computer running DTS Server Import/Export wizard to convert an Access base to MS-SQL, for running on my server, that runs on Win 2003.
 
The wizard reports no errors, and the forum and the posts are displayed right.  However, I'm not able to add new topics or reply to topics.
 
I get an error msg saying that some column in some table doesn't allow NULL.  I tried to edit the settings for the tables (in MSSQL) that were reported, so that they would accept NULL in the columns mentioned.  This worked well for the first 2-3 tables that had this error-message, until I got a message saying "Illegal use of NULL".
 
Any ideas for solving this ?
 
Btw: The upsizing wizard in Access is not working for me at all, so don't suggest trying that. 



Replies:
Posted By: WebWiz-Bruce
Date Posted: 17 September 2006 at 5:32pm
It sounds like the method you are using is not moving across the default values to SQl Server.

There are different options you can select when using DTS, so you just need to mess around till you find one that works for you.


-------------
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: Pilsener
Date Posted: 17 September 2006 at 5:46pm
This DTS-Wizard doesn't have many configuration settings really.
 
I need some hints here:
- What should I set as destination ?  I have used SQL Native Client so far.
- Is it neccecary to fiddle around with the settings for the tables ?
- Can I use WebWiz to set up an empty database, and then fill in only the contents of trhe tables ?   If yes: How do I copy only the contents, and not the settings ?
 


Posted By: WebWiz-Bruce
Date Posted: 18 September 2006 at 9:44am
You wouldn't be able to just copy across the data to an empty database due to the auto numbering fields.

If you use Access 2003 it has an option to make an MDE file from the Access database, an MDE file should be able to be attached to SQL Server as a database, so maybe worth giving that option ago.


-------------
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: Pilsener
Date Posted: 18 September 2006 at 4:52pm
Can you give me a quick description on how to attach the MDE file to MSSQL ?


Posted By: balearicjobs
Date Posted: 18 September 2006 at 8:04pm
Originally posted by -boRg- -boRg- wrote:

You wouldn't be able to just copy across the data to an empty database due to the auto numbering fields.

If you use Access 2003 it has an option to make an MDE file from the Access database, an MDE file should be able to be attached to SQL Server as a database, so maybe worth giving that option ago.


Hi Borg

As you have probably gathered by now, I am OK with HTML and ASP as well as IT in general. However I'm a complete novice with databases, espically SQL.

If you don't mind and if you have the time, could you please explain to me how you go about attaching the MDE file? I've managed to export to an MDE without problem and also I have created a blank database. I've not got Enterprise manager however I have got that SQL Management Express from the Microsoft website.

I appreciate any help!


-------------
Thanks for your help!!!
Kind Regards
Rob,
http://www.balearic-jobs.com" rel="nofollow - Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie


Posted By: WebWiz-Bruce
Date Posted: 19 September 2006 at 10:53am
Sorry I made a mistake, an SQL Server file is an mdf file not an mde file, so you probably can not attach it to SQL Server.

If you have an SQL Server mdf file it can be attached and detached from SQL Server by right clicking the database in SQL Studio Manager and selecting 'attach' or 'detacth'


-------------
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: balearicjobs
Date Posted: 19 September 2006 at 1:26pm

Thanks for letting me know.

I suppose the next obvious question is do you or anybody else know how to convert/export an Access file into a "mdf" file?



-------------
Thanks for your help!!!
Kind Regards
Rob,
http://www.balearic-jobs.com" rel="nofollow - Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie


Posted By: balearicjobs
Date Posted: 19 September 2006 at 3:22pm
I found an intresting forum post regarding mdb to mdf on the web
http://www.blueshell.com/forum/?display=_632211872554900000#active - http://www.blueshell.com/forum/?display=_632211872554900000#active
 
I'm going to try that tonight. Will post if I have any sucess.


-------------
Thanks for your help!!!
Kind Regards
Rob,
http://www.balearic-jobs.com" rel="nofollow - Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie


Posted By: balearicjobs
Date Posted: 20 September 2006 at 7:40am
Oh I give up! LOL - Well not totally, but I feel like I am getting there!
 
I used that Blueshell program, but when you try and import the access database into the SQL database, you appear to have to do it table by table, however when you try this it just says that there are bad arguments and I don't know what that means. I look into it and it says that its to do with either invalid table entries or something to do with trying to enter nullable entries into non-nullable fields.
 
Basically people, you have to be an ASP and Database genius to convert Webwiz forums forums from Access to SQL, espically if you don't have access to Enterprise Manager (which appears to be no longer available) on an existing and populated forum.  Which is incredibly frustrating when you are been told not to use Access as it is likely to break down in the future, and I have over 15,000 members - I feel like one day its going to fail and 5 years of hard work will be lost (although I do backup regularly, so something would have to go really wrong) and all I will get is "I told you so", even after hours upon hours of hard work trying to follow the advice to use SQL.
 
I wish somebody would offer a service to convert databases, there are probably many people out there who would pay for such a service, myself including.
 
Also, if anybody does figrue out a way of doing this, which doesn't require a degree in ASP and Databases with software that is currently available, could we have it posted up on webwiz.net as an article to assist current customers.
 
How does that saying go? - I'm stuck between a rock and a hard place.
 
Rant over! Wink
 
Thank you for your continuing hard work...


-------------
Thanks for your help!!!
Kind Regards
Rob,
http://www.balearic-jobs.com" rel="nofollow - Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie


Posted By: WebWiz-Bruce
Date Posted: 20 September 2006 at 8:31am
Have you gone through the topic forum_posts.asp?TID=12975 - Access to SQL Server - Step by Step ?

-------------
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: balearicjobs
Date Posted: 20 September 2006 at 10:08am
Originally posted by -boRg- -boRg- wrote:

Have you gone through the topic forum_posts.asp?TID=12975 - Access to SQL Server - Step by Step ?
 
Yes but I can't even get past point 1, as I don't have Enterprise Manager and I don't know of any way to obtain it at the moment. From what I have been told its no longer available.
 
Unfortunetly my techincal abalities, although not complete novice do not extend to been able to transfer these instrution into the SQL Manager Express.


Posted By: dpyers
Date Posted: 20 September 2006 at 3:03pm
Do a google for
SQL Server Developer Edition
EM comes with the 2000 version and I believe a slightly less capable version comes with the 2005 version. You should also be able to findthe  2000 developer edition  on amazon or ebay.

EDIT:
I believe dj air does access to mssql upgrades for a fee.

-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: Pilsener
Date Posted: 24 September 2006 at 2:50pm
I have obtained Enterprise Manager. 
Can someone give me a guide to how to convert the database to get this thing right ?


Posted By: negrisoli
Date Posted: 25 September 2006 at 7:31pm
Originally posted by -boRg- -boRg- wrote:

Have you gone through the topic forum_posts.asp?TID=12975 - Access to SQL Server - Step by Step ?
 
Hi boRg,
 
I made this procedure and it was a success !
 
Now , I have a little problem.
 
My Hosting Provider , donīt accept connection via Enterprise Manager, only accept via "ASP.Net Enterprise Manager" - http://mssql.zipweb.com.br/mssql/app/connect.aspx - http://mssql.zipweb.com.br/mssql/app/connect.aspx   , via the Plesk Control Panel.
 
Well, now how I do this migration of the Datas from SQL Local to SQL in the Hosting via this Control Panel ?
 
Thank you very much for your attention.
 
Rodrigo


Posted By: WebWiz-Bruce
Date Posted: 26 September 2006 at 11:23am
As far as I know you can not do this with ASP.NET Enterprise Manager

-------------
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



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net