Access to SQL Server

[GUJUm0deL]

Hi all, I have a client who's been using Access dB for a few years. Now he wants to convert over to SQL Server.

The Access dB has roughly 15 tables (and on average about 20 columns in each table). How can I port over the tables/columns into SQL Server? Or, do I need to re-create the tables/columns from scratch?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[mrdenny]

You can look into the upsizing wizzard on Microsoft's web site. However the upsizing wizzard isn't perfect as it can cause data type issues. As a second option which is usually the preferred option would be to recreate the database objects manually.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GUJUm0deL]

I'm surprised there isn't a better way to convert the Acces dB into a MS SQL Server.

I confirmed with the client, he is not concerned with transporting the records from Access to SQL Server. They says the site will going through major changes and they wants to start with a clean dB.

I just now need to transport the tables/columns only.



_____________________________
Just Imagine.

http://www.imajinarts.com

 

[onpnt]

SQL Servers ability to migrate from file sources like a mdb is much more intuitive atually than the other major database server players. The wizard is hoever as mrdenny stated a qizard and prone to having a small percentage of conversion issues. There are several methods you can take all the way down to opening the mdb and querying it directly from T-SQL and pulling the data into tables

The size of a mdb you're talking about is nothing and the T-SQL method or SSIS method would be a stable way to do it I think. Even the Import wizard will do this fairly easily. Just be sure as with any process to double check and make sure your data types have come over correctly. It will make the best decision based on what it thinks the data type can be so if that computed decision is not matched to your design it will need to be added to the transform


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[GUJUm0deL]

Thanks onpnt. I only need to convert over the table/column names and not the data.

Does Access have a feature which can create the SQL code to create table and columnnames? If so, then i can just run that on the SQL server to create the tables/columns...




_____________________________
Just Imagine.

http://www.imajinarts.com

 

[onpnt]

That I do not know. I haven't worked in access sense 97 and kind of left it there thankfully

There is one small trick to get the scripted tables out of access in T-SQL format using the Import wizard. Basically use the import wizard to do it for you. However I would really rather and suggest learning the Create Table syntax without that help.

Right click DB in SSMS
Tasks-->Import Data
Select MS Access as teh data source (fill in the blank)
Destination is your SQL Server
leave default in next "Cpy data..."
Click the Edit button next to each table it finds
Click Edit SQL and you will see the create satement. You can also alter the data types here if you just want to keep going with the data transfer


SSIS has a realyl good object transfer task also in which you can jsut move the objects rate over. Doesn't sound like you want to go all that far for this task though.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]