Moving Access Database To Sql Server

Hi folks

I want to move Access database to Sql server but I don't want to use Import/Export Wizard. I want to use DTC Designer.

When I tried to do it I can move only one table between Access and Sql db for every connection.

So, what should I do?

I want to move some tables e.g. ten tables to temp db and then I want to put P.K and Uni. constraints on the main tables then move them and the rest of the tables to the currect destination database.

Did DTC Designer help me on this or should I use Import/Export Wizard.

Thanks guysI would use the Import Wizard or the appropiate Upsizing wizard in Access if applicable for your version of Access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for reply

The issue here that Access database contain data not correct. Due to

wrong data, I have to repair them before moving

it. Therefore, I have to design a temporary database and then set the conditions

on the database. The issue here I want to build DTS that move strong entity and

check the data and the conditions. When an error occurs, I want DTS to primate a

message to correct the data manually.

Can you give me any suggestion?

Thank you

|||Thank all for answring

I Moved data by using standard SQL statment

Select , Insert .....

Thnaks

Bye

Moving Access Database To Sql Server

Hi folks

I want to move Access database to Sql server but I don't want to use Import/Export Wizard. I want to use DTC Designer.

When I tried to do it I can move only one table between Access and Sql db for every connection.

So, what should I do?

I want to move some tables e.g. ten tables to temp db and then I want to put P.K and Uni. constraints on the main tables then move them and the rest of the tables to the currect destination database.

Did DTC Designer help me on this or should I use Import/Export Wizard.

Thanks guysI would use the Import Wizard or the appropiate Upsizing wizard in Access if applicable for your version of Access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for reply

The issue here that Access database contain data not correct. Due to

wrong data, I have to repair them before moving

it. Therefore, I have to design a temporary database and then set the conditions

on the database. The issue here I want to build DTS that move strong entity and

check the data and the conditions. When an error occurs, I want DTS to primate a

message to correct the data manually.

Can you give me any suggestion?

Thank you

|||Thank all for answring

I Moved data by using standard SQL statment

Select , Insert .....

Thnaks

Bye

Move table

How can i move tables from one database to another without losing any of the PK, FK or the identity column constraints?

I currently use wizard and thought maybe you can do this with some Tsql code...

Is it possible?

Thanks for any help

You can do the following in TSQL:

1. Use SELECT INTO to create the tables with data (identity property of column will be copied over including the NULLability constraints)

2. Add the constraints using metadata from INFORMATION_SCHEMA.TABLE_CONSTRAINTS, CHECK_CONSTRAINTS etc.

3. Create defaults, computed columns using syscomments information

In SQL Server 2005, you can use different set of catalog views to get the metadata. Since this is lot of work, you are probably better off using SSIS/DTS Transfer task.

Move Database

We are running out of space on our D: drive and are going to add an
additional drive (E:). I was thinking that there was a wizard in which we
could move our database from its location on the D: over to drive E:. We
would keep our log files on d:, and just move the database to the new E:
drive.
JohnThis is a multi-part message in MIME format.
--=_NextPart_000_016F_01C38D8E.0D24BDD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Check out sp_attach_db and sp_detach_db in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Navara" <jnavara@.nospam.com> wrote in message =news:#JyNb8ajDHA.1656@.tk2msftngp13.phx.gbl...
We are running out of space on our D: drive and are going to add an
additional drive (E:). I was thinking that there was a wizard in which =we
could move our database from its location on the D: over to drive E:. =We
would keep our log files on d:, and just move the database to the new E:
drive.
John
--=_NextPart_000_016F_01C38D8E.0D24BDD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_attach_db and =sp_detach_db in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Navara" wrote in =message news:#JyNb8ajDHA.1656=@.tk2msftngp13.phx.gbl...We are running out of space on our D: drive and are going to add =anadditional drive (E:). I was thinking that there was a wizard in which =wecould move our database from its location on the D: over to drive E:. Wewould keep our log files on d:, and just move the database to the =new E:drive.John

---

--=_NextPart_000_016F_01C38D8E.0D24BDD0--