Import data from Access into SQL Server 2005

15 pts.
Tags:
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 SQL Server 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!

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 77
    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!!!!!!!!!!!!!!!
    15 pointsBadges:
    report
  • Kurthp
    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.
    195 pointsBadges:
    report
  • watabastid
    I have the same issue. I cannot convert my Access DB to SQL cause I don't have access to the front end of it. I just need to move some data over on a daily basis. I need to import data using a stored procedure.
    10 pointsBadges:
    report
  • Subhendu Sen
    There r several ways to do that:
    From sql client tool (i .e sql server management studio for 2005), right click the database that u want to add data to & select import data. This wizard guides u to select the ms access file as a data source while possible to map one/ more/ all tables into sql database.
    Else u can use ODBC to create data source based on sql server.
    or u can navigate here : http://office.microsoft.com/en-in/access-help/import-or-link-access-to-sql-server-data-HA010341762.aspx

    28,600 pointsBadges:
    report
  • Genderhayes
    an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with xsd schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database
    7,440 pointsBadges:
    report
  • Genderhayes
    Integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server, as well as combining a broad group of graphical tools and rich script editors that provide access to SQL Server to developers
    7,440 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following