Using VB.NET to convert an Access database to a SQL server database

• • Reply

  13 years ago

  by jender624

  Derek Kohlhagen

  , United States

  Joined 13 years ago

  Is there an easy way to do this?  Thanks!

  jender624

  Report abuse
• • Reply

  13 years ago

  by myoungbl

  Marshall C Youngblood

  Mississippi, United States

  Joined 13 years ago

  What version of SQL server?

  If this will be a one time job, you could perform a table import for each table in the
  Access database into the desired SQL database using the Enterprise Manager. In SQL 2000 you can even save the import tasks as DTS jobs. That will allow you to test the transform, and if it works just copy the job from the test server to the productiion server. I'm sure there is something similar in SQL 2005.
  

  If it is something that will be repeated on the same Access database structure each time, then the DTS/SSIS solution is your best bet. If it's SQL 2000, then use the DTS engine. For SQL 2005, you are better off using SSIS manager. Both of these have transform services that let you select the source tables, and then will build the SQL tables in the database of your choice.

  If you want an VB.NET application that can import any Access database into a SQL database, you need to learn how to use table schema in Jet. And the create statement in SQL.  It's fairly straight foward, but will involve a lot of work.

   

  Report abuse
• • Reply

  13 years ago

  by jender624

  Derek Kohlhagen

  , United States

  Joined 13 years ago

  Thanks for the reply!  Do you need the full version of SQL server 2005 to use SSIS?  We are currently using SQL Server 2005 Express, and I'm not sure that we have access to SSIS through it.

  If not, I'm guessing we'll have to resort to your last suggestion.  We are looking to have a VB.NET application that will read data/schema from an Access database, and convert it to a SQL Server 2005 format.  This sounds like what you are suggesting.  I was looking for an easy way to do this, but if we need to re-create the tables in the way you say, I guess I'll have to resign myself to it.

  Report abuse
• • Reply

  13 years ago

  by myoungbl

  Marshall C Youngblood

  Mississippi, United States

  Joined 13 years ago
  I really can;t say if SQL Express includes SSIS, but if it doesn'td you have to resort to dtoing it the 'hard' way, I think you'll be surprised at how easy it is. The key, of course, is to query the jet database table schema instead of the actual table.
  Report abuse