Moving Data From Access to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Moving Data From Access to SQL Server

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-08-04 : 11:43:29

I'm creating a db in SQL Server which is quite similar to an Access DB. IT is the same except for one column name. In Access it was References. In SQL sever i decided to leave it as Reference to avoid the square braces for the reserved word References.

I could of course change the Access table column name to make this easier. The structure is the same.

How do i move the data in my sql server db to my better designed SQL Server DB?

Thanks!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-04 : 12:23:44

you could use DTS to move the data. DTS is an application that is included with SQL Server.

here is the information from BOL on DTS http://msdn.microsoft.com/library/en-us/dtssql/dts_basic_5zg3.asp

btw, www.sqldts.com is another good online resource for DTS info.

-ec

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-08-04 : 12:35:48

Thanks. I found this article also.

How to Migrate from Access to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

I will have some questions in a minute.

Thanks!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-08-04 : 12:57:48

You can also make a .adp for your new SQL DB. and then use Access export function.

Jim
Users <> Logic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-08-04 : 13:15:42

The article that is provided above.

"How to Migrate from Access to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx"

seems to reference only the UpSizing Wizard. It doesn't discuss DTS. I will read the article on DTS next. Which one of these should i use?

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-08-04 : 13:16:52

A .adp? what is that?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-08-04 : 13:23:20

A .adp is a development with a SQL backend and a Access front end.
Supported by access 2000 and above.

[url]http://office.microsoft.com/en-us/assistance/CH063655171033.aspx[/url]

Jim
Users <> Logic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-08-04 : 13:42:54

How to use data transformation services (DTS) to export data from a Microsoft Access database to an SQL Server database
http://support.microsoft.com/default.aspx?scid=kb;en-us;285829

Differences between UpSizing Wizard and DTS

You can use the DTS Import and Export Wizard to automatically create tables on SQL Server, and then copy data from Access to the new SQL Server tables. DTS can move data at a faster rate than the Microsoft Access Upsizing Wizard can, but DTS does not provide all of the features of the Access Upsizing Wizard. The following is a list of actions that the Access Upsizing Wizard can perform, but that the DTS Import and Export Wizard cannot perform: • If an Access table has a primary key, the Access Upsizing Wizard automatically re-creates the primary key on the table that it generates on SQL Server; DTS does not.
• The Access Upsizing Wizard automatically migrates all rules and defaults that exist in a table to SQL Server; DTS does not.
• If tables in an Access database are related, the Upsizing Wizard automatically re-creates these relationships on SQL Server; DTS does not.
• DTS does not upsize any queries that exist in an Access database.

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-08-04 : 13:43:24

Thanks Jim. I will take a look at that also!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources