Over the last few days, I was asked to move a company program over from an Access back-end, to SQL Server.
There are 2 copies of the program, the live data version, on the server, and the local version on my PCs C: Drive, to ensure if I make a mistake, it doesn't affect the live data.
So, I managed to migrate the Access database, tables and data over to SQL Server 2008, and the local version of the program now works.
The easiest way, or so I'm informed, to now do the same to the live version of the program, is to write an imports program, which wipes all of the data from each table in the SQL Server database, and then copies over the data from the live Access database. However, I've never done this before, so I'm not really even sure where to begin.
Could anybody point me in the right direction on how to begin or do this, so that I only have to change the connection path in the program, rather than go through the whole process again?
PS, I work in vb.net, so that's the language I would need any responses in!
I added a new path to the ini file for the database to read. This connected to the live database. Once this connection is open in the project, proceed to step 2.
Create a new class, where the imports and exports will happen.
Put a button, or some sort of control in the program to initiate the import/export. For example, I had a button which, when clicked, asked the user to confirm that they wanted to import a new database and overwrite the existing one. If yes, call the function which does this, in the newly made imports class.
Now that you know how to get this set up, the code would be something like
Public Function importdatabase(/connections go in here/) Declare transaction Create sql variable Try Begin the transaction here sql to delete the data from one table sql to select all data from database that is being imported For loop to iterate over each record in the database table Declare a variable for each field in the database variable1 = ("fieldname1") variable2 = ("fieldname2") sql statement to insert the new values call to the function which runs the sql query Next commit transaction Catch ex As Exception Throw End Try
Step 5; Repeat the delete/insert process for each database table
Below this, I have other functions.
One function created a new datatable, this is referenced as
For each dr as datarow in /functionname(parameters).Rows
Next one is to execute the sql statement (not required, any command to execute it will do)
Next one is used for parameterising my SQL query
The rest are to replace null values in the database with empty strings, set dates, etc