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