Importing Data from Access 2002 in SQL Express

Hi All,

At the moment i have a piece of software that uses Access to store the data into a number of tables.

I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.

Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb

The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.

Any ideas?

Thanks in advance

Martin

Hi,

here is a good article to refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx you may also use SSMA to migrate data from Access to SQL Express.

Hemantgiri S. Goswami

Importing Data from access 2000 in SQL

Hi All,

At the moment i have a piece of software that uses Access to store the data into a number of tables.

I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.

Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb

The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.

Any ideas?

Thanks in advance

Martin

I do not know if you can write it with the help of query. Yes, if you can import the Access data, that would be great.

Well, you might like to write a small application for your data migration. The application should be able to read data from your access database and will push the data in SQL.

|||Create a "linked server" to the access file, and just use TSQL queries to pull the data from the linked server.|||

Excuse my ignorance, but where can i find the linked server option ?(i am using SQL 2005 Managment Studio Express) and cant find the option to link servers (the version i am running also has no help!!)

Thanks Again

Martin

|||It is under: ServerName -> Server Objects -> Linked Servers. Right click and select "New Linked Server".

Give it a name, like "oldaccess". Then select the MS Jet 4.0 OLEDB driver. Put in the full path of the Access database in the "Data Source".

To insert into your SQL table use:

INSERT INTO tablea
SELECT * FROM oldaccess.tablea

See this tutorial: http://www.databasejournal.com/features/mssql/article.php/3103331