Excel as Front End Application for SQL Server

By: Sebastiao Pereira | Updated: 2024-08-12 | Comments (4) | Related: > Microsoft Excel Integration

Problem

Due to limited budgets, developing custom front-end applications is difficult for small- to medium-sized projects. In some cases, the additional expense associated with custom applications is not justified, even though the benefits of having this real-time data access are ideal.

For those cases, can Excel be used as a front-end application to interact with SQL Server as its data source?

Solution

As a front end for an SQL Server database, Excel can be useful for simple applications due to its familiarity with users, cost-effectiveness, ease of data manipulation, possibility of data integration with Power Query or VBA, and data visualization capabilities (graphs). In this article, we walk through how to setup a simple Excel interface to work with SQL Server data.

SQL Server Setup

Let's create an example application to control a car's fuel efficiency to show how Excel can function as a front end to an SQL Server database.

Create a Table for the Automobile Data

CREATE TABLE [dbo].[Automobile](
   [EventId] [int] IDENTITY(1,1) NOT NULL,
   [AutomobileId] [nvarchar](50) NULL,
   [Day] [date] NOT NULL,
   [Place] [nvarchar](50) NULL,
   [Amount] [money] NULL,
   [Price] [money] NULL,
   [Odometer] [int] NULL,
   [Comments] [nvarchar](50) NULL,
   [Mileage] [int] NULL,
   [Consumption] [money] NULL,
   [FuelEfficiency]  AS ([Mileage]/[Consumption]),
   [ModifiedDate] [datetime] DEFAULT GETDATE(),
   [ModifiedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Automobile] PRIMARY KEY CLUSTERED 
(
   [EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Create a Table for the Automobile Data Log

CREATE TABLE [dbo].[AutomobileLog](
   [ControlItem] [int] IDENTITY(1,1) NOT NULL,
   [EventId] [int] NULL,
   [AutomobileId] [nvarchar](50) NULL,
   [Day] [date] NOT NULL,
   [Place] [nvarchar](50) NULL,
   [Amount] [money] NULL,
   [Price] [money] NULL,
   [Odometer] [int] NULL,
   [Comments] [nvarchar](50) NULL,
   [Mileage] [int] NULL,
   [Consumption] [money] NULL,
   [FuelEfficiency] [money] NULL,
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_AutomobileLog] PRIMARY KEY CLUSTERED 
(
   [ControlItem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Create the Stored Procedure

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20240719
-- Description: Automobile Follow-up
-- =============================================
CREATE PROCEDURE [dbo].[uspAutomobile] 
         (@EventId int
         ,@AutomobileId nvarchar(50)
         ,@Day date
         ,@Place nvarchar(50)
         ,@Amount money
         ,@Price money
         ,@Odometer int
         ,@Comments nvarchar(50)
         ,@ModifiedBy nvarchar(50))
WITH EXECUTE AS CALLER
AS
BEGIN
   SET NOCOUNT ON;
   BEGIN TRY
      BEGIN TRANSACTION;
 
         DECLARE @ModifiedDate datetime = GETDATE();
 
         IF LEN(@Place) = 0
            SET @Place = NULL;
 
         IF LEN(@Comments) = 0
            SET @Comments = NULL;
 
         -- UPDATE
         IF @EventId > 0
            BEGIN
               UPDATE [dbo].[Automobile]
                  SET [AutomobileId] = @AutomobileId
                     ,[Day] = @Day
                     ,[Place] = @Place
                     ,[Amount] = @Amount
                     ,[Price] = @Price
                     ,[Odometer] = @Odometer
                     ,[Comments] = @Comments
                     ,[ModifiedDate] = @ModifiedDate
                     ,[ModifiedBy] = @ModifiedBy
                  WHERE [EventId] = @EventId; 
            END
 
         -- INSERT
         IF @EventId = 0
            BEGIN
               INSERT INTO [dbo].[Automobile]
                     ([AutomobileId]
                     ,[Day]
                     ,[Place]
                     ,[Amount]
                     ,[Price]
                     ,[Odometer]
                     ,[Comments]
                     ,[ModifiedDate]
                     ,[ModifiedBy])
                  VALUES (@AutomobileId
                     ,@Day
                     ,@Place
                     ,@Amount
                     ,@Price
                     ,@Odometer
                     ,@Comments
                     ,@ModifiedDate
                     ,@ModifiedBy);
 
               SET @EventId = SCOPE_IDENTITY();
            END
 
         IF @EventId >= 0
            BEGIN;
               WITH CTEAUT (ev,diff) AS
                  (SELECT [EventId]
                        ,CASE 
                           WHEN LAG([Automobile].[Odometer]) OVER (ORDER BY [Automobile].[AutomobileId], [Automobile].[EventId]) IS NULL 
                           THEN 0
                           ELSE [Automobile].[Odometer] - LAG([Automobile].[Odometer]) OVER (ORDER BY [Automobile].[AutomobileId], [Automobile].[EventId])
                         END
                     FROM [dbo].[Automobile]
                     WHERE [AutomobileId] = @AutomobileId)
                  UPDATE  [dbo].[Automobile] 
                     SET  [Mileage] = x.diff
                        ,[Consumption] = [Amount] / [Price]
                     FROM CTEAUT x
                     WHERE [EventId] = x.ev;
            END
 
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
 
         INSERT INTO [dbo].[AutomobileLog]
               ([EventId]
               ,[AutomobileId]
               ,[Day]
               ,[Place]
               ,[Amount]
               ,[Price]
               ,[Odometer]
               ,[Comments]
               ,[Mileage]
               ,[Consumption]
               ,[FuelEfficiency]
               ,[ModifiedDate]
               ,[ModifiedBy])
         SELECT @EventId
               ,[AutomobileId]
               ,[Day]
               ,[Place]
               ,[Amount]
               ,[Price]
               ,[Odometer]
               ,[Comments]
               ,[Mileage]
               ,[Consumption]
               ,[FuelEfficiency]
               ,[ModifiedDate]
               ,[ModifiedBy]
            FROM [dbo].[Automobile]
            WHERE [EventId] = ABS(@EventId);
 
         -- DELETE
         IF @EventId < 0
            BEGIN
               DELETE FROM [dbo].[Automobile]
                  WHERE [EventId] = ABS(@EventId); 
            END;
 
      COMMIT TRANSACTION;
 
      SELECT [EventId]
               ,[AutomobileId]
               ,[Day]
               ,[Place]
               ,[Amount]
               ,[Price]
               ,[Odometer]
               ,[Comments]
               ,[Mileage]
               ,[Consumption]
               ,[FuelEfficiency]
               ,[ModifiedDate]
               ,[ModifiedBy]
         FROM [dbo].[Automobile]
         ORDER BY [AutomobileId]
               ,[EventId] DESC;
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         BEGIN
            ROLLBACK TRANSACTION;
         END
         
      -- Print error information. 
      PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State: ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line: ' + CONVERT(varchar(5), ERROR_LINE()) + 
          ', User: ' + CONVERT(varchar(5), @ModifiedBy);
 
      PRINT ERROR_MESSAGE();
   END CATCH;
END
GO

Excel Setup

Create a new Excel spreadsheet named Automobile.xlsm on your computer. Click File > Options > Customize Ribbon > Developer to allow VBA buttons on the sheet.

Rename the sheet tab to "Automobile" and fill the cells with the following:

Working with VBA

In Excel, press ALT-F11 to open the VBA panel, right-click on Microsoft Excel Objects, and choose Insert > Module.

Click Tools > References, look for Microsoft ActiveX Data Objects X.X Library, and click OK.

Click on Module1. Insert the code below and modify the DatabaseName and ComputerName with the respective names of the computer running SQL Server and the database being used. For numeric values, I am forced to only accept dot as a decimal separator.

Public Sub AutoInsEvent()
    If Len(Cells(2, 2)) = 0 Then Exit Sub
    
    Dim conex As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
  
    conex.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=***DatabaseName***;Data Source=***ComputerName***"
        sql = "EXECUTE [dbo].[uspAutomobile] " & _
            Cells(2, 2) & ",'" & _
            Cells(3, 2) & "','" & _
            Format(Cells(4, 2), "yyyy/MM/dd") & "','" & _
            Cells(5, 2) & "'," & _
            Replace(Cells(6, 2), ",", ".") & "," & _
            Replace(Cells(7, 2), ",", ".") & "," & _
            Cells(8, 2) & ",'" & _
            Cells(9, 2) & "','" & _
            Cells(10, 2) & "';"
        rs.Open sql, conex, adOpenKeyset, adLockOptimistic
    conex.Close
    Range("B2:B10").ClearContents
 
    Range("E2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    For i = 2 To 10
        If Cells(i, 3) > 0 Then
            Cells(i, 2) = Cells(i, 3)
        End If
    Next i
    
    Columns("E:Z").Select
    Columns("E:Z").EntireColumn.AutoFit
    Range("B2").Select
End Sub

Go to the Developer tab, choose Insert > Form Control > Button, and click on the desired cell where you want to place the button and associate it to the macro above. After that, you can change the name of the button to "GO" or whatever you want. To change the name of a button, right-click over the button and choose Edit Text.

Now, it is ready to enter some data, which I did for some items.

Including a Table to View the Data Entered

Since it is always good to see what is happening, let's include a query to show the data in our database. Go to Data > Get Data > From Database > From SQL Server Database.

Fill out the server name and the database name and use the following SQL statement: "SELECT * FROM [dbo].[Automobile]".

Provide your credentials.

Update the data visualization based on your needs and click Load.

This will insert a new sheet called QUERY.

Move this table generated from the QUERY sheet to the AUTOMOBILE sheet at cell E1.

Observe that I entered July 8^th twice. To delete it, select the data, copy item 9 from columns EVENTID to COMMENTS, paste it using transpose into cell B2, change EVENTID 9 to -9, and press GO. Negative numbers are a command to exclude the item.

Also, if you want to change an Item, copy and transpose it to change the value. I will do so for item 7, and press GO. This repeated task of transposing can also be done using VBA to automate this process.

If you want to know who did what and when, repeat the process of Including a table to view the data entered above but change the SQL STATEMENT to "SELECT * FROM [DBO].[AutomobileLog] ORDER BY ABS([EventId]),[ControlItem]".

Many improvements can be implemented, but this is enough to show how to interact with SQL Server using Excel.

Next Steps

If you need more information about ADO objects, please visit ADO Fundamentals.
I wrote an article that explains the use of log for change tracking in a SQL store procedure: Single SQL Stored Procedure for all CRUD Operations and Change Tracking

About the author

Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-08-12

Setup Excel as Front End Application for SQL Server