| Author |
 Topic Search  Topic Options
|
lodogg 
Groupie
Joined: 22 August 2003
Location: United States
Status: Offline
Points: 161
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Upsizing hell.....................:)
Posted: 21 November 2003 at 6:27am |
Sorry for the title but that's what I知 in. I have done searches on Upsizing, access to mssql, acces to sql, access upgrade and none of them had a step by step on how to upsize to MSSQL! So I got mssql loaded and I have version 7.6 working just fine with mssql. I ran the config wizard and that created all the necessary tables inside of mssql database that I had created. Now it's time to use the upsizing wizard! I went to access then I went to tools - database utilities - upsizing wizard then this is where everyone drops off with a good description on what to do? So I choose use existing database because I already created one through MSSQL as per previous posts! At this point is where I got lost and I'm sure others have too! So I decided to mess around to see the best way I could see to get the database over to SQL. So at the File Data Source window I decided to click New then at the Create New Data Source I choose SQL Server. Then I jut put in the sql server name the and the admin account for SQL so it would have full access to do the conversion. I tested the connection and everything seemed wonderful? IS THIS STEP CORRECT THAT I DID? Ok after this step I took the defaults to put the data from the access database over to the SQL one. It went through but has some errors. The only thing I could think of was that the tables were already created on my SQL database when I ran the msSQL_server_setup.asp. And during the import it would not allow me to over write the existing tables? Should I delete the tables in SQL then just import over all the access tables? I知 not sure what to do at this point? Are the steps I have done correct so far? This upsizing procedure is alittle harder than I expected. But then again I知 not a professional when it comes to development!!!! Please help because I知 going to write a procedure out there for all the newbie痴 so maybe I can help somebody else!
Thanks
Lo
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 6:33am |
|
As I remeber there is another option other than using a data source as you loose default values by using this option.
|
|
|
|
lodogg
Groupie
Joined: 22 August 2003
Location: United States
Status: Offline
Points: 161
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 8:55am |
The two tabs are File Data Source or Machine Data Source. Should I create a new connection under Machine Datasource. Once I pin this down then I will document a complete step by step procedure and send you the document and maybe you could put it on the site? Or I can publish it on my site it's up to you It get's confusing? I'm wondering should I delete any tables in SQL because I'm importing the same tables from Access won't this kick up errors? Or will this just integrate the data into SQL?
Thanks Again
Lo
|
|
lodogg
Groupie
Joined: 22 August 2003
Location: United States
Status: Offline
Points: 161
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 12:38pm |
Any suggestions 
|
|
lodogg
Groupie
Joined: 22 August 2003
Location: United States
Status: Offline
Points: 161
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 12:47pm |
One more thing is it possible to download the sql version of 7.5a because I still want to use my Invision Skin? I can't find the 7.5a version any where to download. Until someone fixes that skin I'm staying with ver 7.5a
Ok I'm done filling up this forum. The access to sql problem is a big thing for me! So if anyone can help please do
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 5:15pm |
|
Personally I am not a huge fan of the upsizing wizard, I prefer doing it in DTS which is part of SQL Server. You can just use the import wizard from within Enterpise Manager, thus before run the SQL Script for setting up the databases (which I believe are the same betweek v75a to 7.6) This will create all tables, default data and Stored Procedures. Now using the import wizard you can select the source as you access database, and destination the sql database. select all tables. You will have to go into the transformation window for each table to make sure that identity insert as well as overwrite destination data is selected. Sort the tables so no idents wil conflict so Authors, Categories, Forums, Threads, Topics etc (in that order). Hope that makes sense, if not, let me know and I make some screenshots for you.
|
|
|
|
lodogg
Groupie
Joined: 22 August 2003
Location: United States
Status: Offline
Points: 161
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 10:50pm |
michael you sound like your on to something:) I'mgoing to need some screen prints I'm by no means a database guru.. I tried to run the wizard on the enterprise manager and I selected my database but i wasn't sure what options to use so I signed in with the sql admin password and begun the import and I got 13 errors six tables did import! But when i go to the forum there is no data! Here is a few screen prints
All the errors have the above explanation.
just give me a few prints and i will be on my way! I heard sql is the way to go so I would love to have it as my database server. And I have learned so much just administrating the software but of course there is so much more to learn!!!!!
Thanks!!!
Lo
Edited by lodogg
|
|
lodogg
Groupie
Joined: 22 August 2003
Location: United States
Status: Offline
Points: 161
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 November 2003 at 11:03pm |
Ok i did try another thing! I chose the upsize wizard and said to make a new database this created a new database on the sqlserver. I can now attach to the database via the forum but am I getting the full functionalty of sql? That would be the question. With this method there is less stored procedure then creating a data base right from the 'msSQL_server_setup.asp'. I went into the database created from scrath and there seemed to be more store procedures is this going to hinder the performance that I'm looking for?
Sorry for so many questions I'm just looking for the best method. I would love to create an HTML page just for this procedure I think it really needs to be done! Newbies like myself need help or even have our hand held throught the process.
Thanks again
Lo
|
|
