Chapter 5. Tables with Foreign Keys

Let's add a worksheet, rename it to Payments, and connect to the Payments table.

As expected, we see foreign key values. Let's change this.

Switch to the payments-configuration workbook, select the EventHandlers worksheet and add the configuration:

Click the Save button to save the configuration.

Let's switch to the Payments worksheet and click Reload, Reload Data and Configuration:

The add-in replaces id values with names and adds validation lists:

 

Moreover, the add-in activates the separate List Editor that allows users to select values from large lists in a comfortable way using search.

You can turn on/off the List Editor using the Options, Show List Editor Task Pane option.

 

Let's change a couple of rows in the table and click the Save, View Save Changes SQL.

In my case, the add-in generates the following SQL commands:

As we can see, the SaveToDB add-in uses id values instead of names as it should be.

Creating the Payments table using VBA

The code, used to create the payments table, is the same as described above.

' Creates the Payments worksheet, Chapter 5

Sub Chapter05_1_CreatePaymentsWorksheet()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Call addIn.InsertAddInSheets(wb)

    Dim connString As String
    connString = GetConnectionString()

    Dim ws As Worksheet
    Set ws = GetOrCreateConnectedWorksheet(wb, _
            "Payments", connString, "dbo67.Payments", "TABLE", True)

    ws.Select

End Sub