| Author |
 Topic Search  Topic Options
|
Pilsener 
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Yet again: Converting from Access to MS-S
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.
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
Pilsener
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
Post Options
Thanks(0)
Quote Reply
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 ?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
Pilsener
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
Post Options
Thanks(0)
Quote Reply
Posted: 18 September 2006 at 4:52pm |
|
Can you give me a quick description on how to attach the MDE file to MSSQL ?
|
|
balearicjobs
Groupie
Joined: 13 July 2002
Location: United Kingdom
Status: Offline
Points: 86
|
Post Options
Thanks(0)
Quote Reply
Posted: 18 September 2006 at 8:04pm |
 -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,
Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
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'
|
|
|
|
balearicjobs
Groupie
Joined: 13 July 2002
Location: United Kingdom
Status: Offline
Points: 86
|
Post Options
Thanks(0)
Quote Reply
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,
Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie
|
|
