Home > IT Answers > Microsoft Windows > import data from access into sql server 2005
77
15 pts.
 import data from access into sql server 2005
Migration, Stored Procedures, SQL Server 2005
hello,

I have a large database on ms access with more than 100 tables.
I am planning to do migrate this database into ms sqlserver 2005.
I am receiving info into database access on a daily basis.
so I have to do an auto job to import data from access into sql server 2005.
I think that creating a stored procedure to have an automated job per day may fit my aim, do you have any idea about how can I do it??????

Please a help waiting your reply as soon as possible!

thanks!
ASKED: Nov 19, 2007  10:30 AM GMT
UPDATED: November 24, 2010  4:42:16 PM GMT
RELATED QUESTIONS
ITKE
16,745 pts.
Answer Wiki:
What about using the SQL Server Migration Assistant for Access.

Once converted, set your app to use the SQL Server database instead of the Access DB.
Last Wiki Answer Submitted:  Nov 19, 2007  6:34 PM (GMT)  by  ITKE   16,745 pts.
Latest Answer Wiki Contributors:  Kurthp   195 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

can I use this command extended procedure xp_cmdshell to copy data from access (refer to its path) and set them inside the database on sqlserver 2005 which is taking the same format database structure as access????
As I am receiving data on a daily basis which are set into access database and I have to implement front end pages and reports. For this aim, I want to create a stored procedure to generate the data from access into sql server 2005 in an auto schedualed job. Please a help for this!!!!!!!!!!!!!!!

77
 15 pts.
 

To the best of my knowledge, you can’t do it this way. Your 100+ table Access database is inside one Access .mdb file. Using xp_cmdshell to copy the .mdb file will not create the database structure/data within SQL Server.

Using the SQL Server Migration Assistant for Access would allow you to convert the tables/data from Access to SQL Server. Best would be to then change your front end/reports to be directed toward the SQL Server database.

Having said that, once the base structure/data exists within SQL Server, you could then create a Linked Server within SQL Server to your Access database and then write procedures to bring in new data from Access.

Kurthp
 195 pts.