4

I have a table on SQL Server that is relatively huge (approx 300,000 rows). This data is used as a pool of data to validate what users do in an Excel spreadsheet.

One way to ensure that users are always using the most up-to-date data is to set up a VBA to auto pull data from SQL server whenever the spreadsheet is open.

Code:

Private Sub Workbook_Open()
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server Name;Initial Catalog=Database;User ID=User;Password=Password; Trusted_Connection=no"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "SELECT * FROM [Database]"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL

    'Copy Data to Excel'
        Sheets("TEPSD").Range("A1").CopyFromRecordset (objMyRecordset)

        objMyConn.Close

End Sub

The issue with this is it takes a very long time. Sometimes, it takes too long and I end up having to ctrl+break it.

I was wondering whether there is a quicker way of doing this? Or another way to validate user data without having to pull the entire table to Excel?

Any input would be very much appreciated.

Oday Salim
  • 1,129
  • 3
  • 23
  • 46
  • How many columns? – Vityata Jun 03 '18 at 20:01
  • (1) "The most up-to-date" - how often is the baseline data changed? (2) And is all the data really required or can a subset be used? (3) Finally - are all of the users trying to access the server simultaneously (i.e. everyone turns up at 9 am and grabs it) – Alan Jun 03 '18 at 20:06
  • 7 columns and over 300,000 rows. – Oday Salim Jun 03 '18 at 20:06
  • @Alan baseline data can be changed at any moment without notice. Not all the data is required, but I am not sure how to validate what a user would input without having all the data in the background somewhere. Finally, users will be using this at anytime, there will be a chance to have everyone doing it at one point. – Oday Salim Jun 03 '18 at 20:09
  • Sounds like you need to shift from pulling all of the data through in an auto-open macro over to a Worksheet_Change macro. Your strSQL would then be changed to check just the appropriate value which would be more efficient, especially if the lookup column is indexed on the server. I'd have to double-check the safe way to build the sql parameter - technically you shouldn't just concat strings for queries - but this SO answer may help you: https://stackoverflow.com/questions/17678856/querying-a-sql-server-in-excel-with-a-parameterized-query-using-vba – Alan Jun 03 '18 at 20:17
  • Also, does that mean users are constantly opening and closing the workbook? – Alan Jun 03 '18 at 20:23
  • @Alan Yes - this is an interim solution as we are still setting up a proper web form to take user data. There are no indicators to when users use the sheet, how many at the same time, and how many times they open and close. Now that I think about it, perhaps setting up this as auto run on open is not a good idea... But I am really not sure how to proceed. – Oday Salim Jun 03 '18 at 20:51
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172358/discussion-between-alan-and-oday-salim). – Alan Jun 03 '18 at 20:57
  • Consider using a Power Query instead of a VBA solution. – teylyn Jun 03 '18 at 21:24

2 Answers2

3

Following our discussion.

At the moment, every user is trying to pull large amounts of data from the server in a short space of time, and then the data will get stale as it is not updated unless the user closes and reopens the workbook.

Instead, switch to a Worksheet_Change() macro. This will refresh the data every time a change is made.

This will allow you to send a customised query every time the user makes a change - e.g. presses enter.

Change your strSQL from a static query drawing out all the data to strSQL = "SELECT name FROM [Database] WHERE name = " & Target.Value

N.B. You should really look up how VBA recordset wants you to do parameter concatenation in order to avoid Little Bobbie Tables.

If the query is successful - i.e. a valid entry - then it should return the same name. If there is no corresponding entry - i.e. not a valid selection - then you should get an empty recordset. This means you can test the result of the query with If rsObj.RecordCount < 1 Then in order to gracefully exit (or prompt the user etc.) when there is no valid match.

Alan
  • 2,914
  • 2
  • 14
  • 26
0

You should consider something like this.

Connecting Excel to SQL Server

Devart Excel Add-in for SQL Server allows you to connect Excel to SQL Server databases, retrieve and load live SQL Server data to Excel, and then modify these data and save changes back to SQL Server. Here is how you can connect Excel to SQL server and load SQL Server data to Excel in few simple steps.

To start linking Excel to SQL Server, on the ribbon, click the DEVART tab and then click the Get Data button. This will display the Import Data wizard, where you need to create Excel SQL Server connection and configure query for getting data from SQL Server to Excel: Devart ribbon tab

  1. Specify Connection Parameters

In the Connection Editor dialog box, you need to enter the necessary connection parameters:

Login details - select whether to use Windows authentication or SQL Server authentication. In the latter case you need to enter your SQL Server user name and password.
User name - your SQL Server user name. Required only for SQL Server authentication.
Password - your SQL Server password. Required only for SQL Server authentication.
Database - the name of SQL database to connect to Excel.

If you need to configure your Excel SQL Server connection in more details, you can optionally click the Advanced button and configure advanced connection parameters. There you can configure connection encryption, resiliency parameters, etc.

To check whether you have connected Excel to SQL Server your connection correctly, click the Test Connection button.

Excel SQL connection settings 2. Select whether to Store Connection in Excel Workbook

You may optionally change settings how the connection and query data are stored in the Excel workbook and in Excel settings:

Allow saving add-in specific data in Excel worksheet - clear this check box in case you don't want to save any Excel add-in specific data in the Excel worksheet - connections, queries, etc. In this case, if you want to reload data from SQL Server to Excel or save modified data back to SQL Server, you will need to reenter both the connection settings and query.
Allow saving connection string in Excel worksheet - clear this check box if you want your SQL Server connection parameters not to be stored in the Excel. In this case you will need to reenter your connection settings each time you want to reload SQL Server data or modify and save them to SQL Server. However, you may share the Excel workbook, and nobody will be able to get any connection details from it.
Allow saving password - it is recommended to clear this check box. If you don't clear this check box, all the connection settings, including your SQL Server password, will be stored in the Excel workbook. And anyone having our Excel Add-in for SQL Server and the workbook will be able to link Excel to the SQL Server, get data from it, and modify them. But in this case you won't need to reenter anything when reloading data from SQL Server to Excel or saving them to SQL Server.
Allow reuse connection in Excel - select this check box if you want to save this connection on your computer and reuse it in other Excel workbooks. It does not affect saving connection parameters in the workbook itself. You need to specify the connection name, and after this you will be able to simply select this connection from the list
  1. Configure Query to Get Data

You may either use Visual Query Builder to configure it visually, or switch to the SQL Query tab and type the SQL Query. To configure query visually, do the following:

In the Object list select the SQL Server table to load its data to Excel.

In the tree below clear check boxes for the columns you don't want to import data from.

Optionally expand the relation node and select check boxes for the columns from the tables referenced by the current table's foreign keys to add them to the query.

In the box on the right you may optionally configure the filter conditions and ordering of the imported data and specify the max number of rows to load from SQL Server to Excel. For more information on configuring the query you may refer to our documentation, installed with the Excel Add-ins.

After specifying the query, you may optionally click Next and preview some of the first returned rows. Or click Finish and start data loading. Query for retrieving SQL Server data to Excel

Editing Live SQL Server Data

After the data is loaded from SQL Server to Excel, you can work with these data like with usual Excel worksheet. You can instantly refresh data from SQL Server by clicking Refresh on the Devart tab of the ribbon, and thus, always have fresh live data from SQL Server in your workbook.

If you want to edit SQL Server data in Excel and save them back to SQL Server, you need to click Edit Mode on the Devart tab of the ribbon first. Otherwise, the changes you make cannot be saved to SQL Server.

After you start the Edit mode, you can edit the data as you usually do it in excel - delete rows, modify their cell values. Columns that cannot be edited in SQL Server, will have Italic font, and you cannot edit values in these columns. To add a new row, enter the required values to the last row of the table that is highlighted with green. Editing SQL Server data in Excel

To apply the changes to actual data in the database, click the Commit button. Or click Rollback to rollback all the changes. Please note that the changes are not saved to the database till you click Commit, even if you save the workbook.

Also, consider this as well.

https://www.mssqltips.com/sqlservertip/3436/passing-dynamic-query-values-from-excel-to-sql-server/

I use these techniques in my office, and I'm moving close to 900k rows with around 24 columns, from SQL Server to Excel. It takes lest than 60 seconds to do the refresh.

ASH
  • 20,759
  • 19
  • 87
  • 200