0

I have created a windows form project which is based in vb.net in Visual Studio. This form acts as the front end to the database.

I decided this time round to try use the Dataset Designer in Visual Studio and found it convenient. Using the Wizard I linked up the MS Access database to create the Dataset in the project. This then automatically generated all the relevant table adapters, datasets, bindingsources and so on.

Now, the aim is to transition the same Access database to SQL-server. The Question is whether it is possible to simply swap the Dataset in Visual Studio to from Access to SQL which have the same table names, structure, relations... I tried modifying the connection string in Settings.settings with no success.

This would save me a lot of time from going through each object and updating the datasource to the new SQL database.

steambop
  • 13
  • 4

1 Answers1

0

You should be able to establish a separate connection to sql server using ado. You can find typical connection strings easily: try http://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/ for example. If the tables exist in sql server, you can SELECT from Access and INSERT into SSvr using 2 different connections, 2 sql command objects. If you have trouble with the SQL (which is very possible, I have not done this with Access), create a dataset from the Access SELECT and use that as an intermediate step. If the tables do not exist yet, get the script for CREATE TABLE in Access or in SSMS, and execute that before doing the SELECT/INSERT operation.

Bob Fischer
  • 1
  • 1