09 – Migrating Tables And Data To SQL Server

In this post we will be explaining how to migrate your MS Access database to your new SQL Server application.

Featured Videos:

• Connecting To SQL Server
• Migrating To SQL Server
• Linking Tables In Access

Featured Downloads

• HotelBookings.accdb

Connecting to the SQL Server Migration Assistant

In order to connect to the SQL Server Migration Assistant we will need to know the SQL Server Instance, username and password from the SQL Server 2012 installation.

Connecting isn’t terribly difficult but it can be the source of frustration if it doesn’t work as it should!

Hotel Bookings (Original)

Migrating To SQL Server

Now we are able to connect to the SQL Server Migration Assistant, it is time to turn the back-end of your Access database into SQL tables complete with existing data.

The migration we do can be described as quick and dirty. We aren’t interested in moving any of the queries over or in worrying too much about the correct data type for the dates.

Once we have migrated the tables to SQL Server, we open up the SQL Server Management Studio and alter the data types in the new SQL tables that are inaccurate.

Linking Access To SQL Server

There are two ways to link to SQL Server, through a Data Source Name (DSN) and through a DSN-less connection. Using a DSN tends to be flaky and the database can often lose its connection and need refreshing. For this reason we always use a DSN-less connection. We don’t believe they are more work to set up and the results are far better, in our opinion.

Here is the code for the Splash screen:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Dim myArray(11) As String

myArray(0) = "tblBookingDetails"
myArray(1) = "tblBookings"
myArray(2) = "tblDates"
myArray(3) = "tblExtraCharges"
myArray(4) = "tblGuests"
myArray(5) = "tblInvoicePayments"
myArray(6) = "tblInvoices"
myArray(7) = "tblReports"
myArray(8) = "tblRooms"
myArray(9) = "tblSettings"
myArray(10) = "tblUsers"

'loop through array and add tables

Dim i As Integer

For i = 0 To UBound(myArray) - 1
    If AttachDSNLessTable(myArray(i), _
        "dbo." & myArray(i), _
        "ROBERTAUSTIN-PC\SQLEXPRESS", _
        "TestDB1", _
        "sa", _
        "password1") Then
    Else
        MsgBox "Could not relink " & myArray(i)
    End If
Next i

End Sub

And here is the code for the modSQLServer module:

Option Compare Database
Option Explicit

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Please modify the code with the name of your SQL Server instance, username and password.

Adding A Foreign Key Constraint

In a previous post we gave you some code to create a foreign key constraint in SQL Server. Now we get to do it for real.

Click on new query and add this code:

Use HotelBookingsDB
ALTER TABLE dbo.tblBookingDetails
ADD CONSTRAINT fk_BookingsForBookingDetails
FOREIGN KEY (BookingID_FK)
REFERENCES dbo.tblBookings(BookingID);

ALTER TABLE dbo.tblBookingDetails
ADD CONSTRAINT fk_RoomsForBookingDetails
FOREIGN KEY (RoomID_FK)
REFERENCES dbo.tblRooms(RoomID);

ALTER TABLE dbo.tblBookings
ADD CONSTRAINT fk_GuestsForBookings
FOREIGN KEY (GuestID_FK)
REFERENCES dbo.tblGuests(GuestID);

ALTER TABLE dbo.tblExtraCharges
ADD CONSTRAINT fk_BookingDetailsForExtraCharges
FOREIGN KEY (BookingDetailsID_FK)
REFERENCES dbo.tblBookingDetails(BookingDetailID);

ALTER TABLE dbo.tblInvoicePayments
ADD CONSTRAINT fk_InvoicesForInvoicePayments
FOREIGN KEY (InvoiceID_FK)
REFERENCES dbo.tblInvoices(InvoiceID);

ALTER TABLE dbo.tblInvoices
ADD CONSTRAINT fk_BookingsForInvoices
FOREIGN KEY (BookingID_FK)
REFERENCES dbo.tblBookings(BookingID);

Click on execute and all these constraints will be added. This is a very important step as it will go some way to bullet proofing your database.