none
Is it possible to convert a Access database to SQL Server database programmatically? RRS feed

  • Question

  • Hi everyone!

     

    At the moment our firm is in a phase where we have started to use SQL Server 2005 databases instead of Access databases. The main problem is that some of our existing softwares are being transformed into SQL Server basis, so our clients would need a tool that they can use to convert their old Access databases to SQL Server.

     

    I know how to use Microsoft SQL Server Management Studio to do the converting but our clients would need a coded tool for the job. Is it possible to convert Access databases to SQL Server 2005 programmatically?

     

     

    Friday, August 17, 2007 8:57 AM

Answers

  • There is an Access migration tool but your client can only use it if the Access database can be migrated without the need for ETL(extraction transformation and loading) because migrating Access can be very complicated task. If ETL is needed then you or a skilled person have to use SSIS to move the databases for your customers. Try the link below for the Access migration assistant.

     

    http://www.microsoft.com/sql/solutions/migration/access/default.mspx

    Friday, August 17, 2007 2:28 PM

All replies

  • do you mean just the Data itself or the entire structure/schema of the database?

     

    if its data then yes of course it is, just generate the correct select commands and insert commands - so select from the database and insert into the other database.

     

    you could also use SQL Server itself to import data from an existing database - this would be much easier and simpler for you than having to create an application that transfers data from one to the other - no need to re-invent the wheel ;-)

     

    Friday, August 17, 2007 11:05 AM
  •  

    Yes I know that I can use the server itself to import data from Access database but it would be a hell of a job to convert every of our clients databases ( there are more than 100 clients and they have way too much databases to be converted manually). So much easier would be if the client himself does it with a tool we would provide.

     

    Hmm...easiest way might be what you said, just to select the data needed and insert them to the new database. I guess that would do the job if the new database would be given to the clients with the software.

     

    Thanks! 

    Friday, August 17, 2007 11:26 AM
  • Have you tried the Access builtin SQL Server upsizing wizard?

    http://support.microsoft.com/default.aspx/kb/237980
    Friday, August 17, 2007 2:01 PM
  • There is an Access migration tool but your client can only use it if the Access database can be migrated without the need for ETL(extraction transformation and loading) because migrating Access can be very complicated task. If ETL is needed then you or a skilled person have to use SSIS to move the databases for your customers. Try the link below for the Access migration assistant.

     

    http://www.microsoft.com/sql/solutions/migration/access/default.mspx

    Friday, August 17, 2007 2:28 PM