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, SQL Server 2005, Stored Procedures
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!

Software/Hardware used:
ASKED: November 19, 2007  10:30 AM
UPDATED: November 24, 2010  4:42 PM
RELATED QUESTIONS
Answer Wiki:
What about using the <a href="http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en">SQL Server Migration Assistant for Access. </a> Once converted, set your app to use the SQL Server database instead of the Access DB.
Last Wiki Answer Submitted:  November 19, 2007  6:34 pm  by  Kurthp   195 pts.
All Answer Wiki Contributors:  Kurthp   195 pts.
To see all 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.