With only the Select and View Definitions permission on a view, can SQL queries be sent from Excel without needing to type the query each time?

Question

I have views that my users often only need to check for one particular person at a time. To do this, they do the following in Excel 365 desktop:

1. Open a blank workbook
2. Click on the 'Data' ribbon
3. Click 'Get Data'
4. Click 'From Database'
5. Click 'From SQL Server Database'
6. Fill in the 'Server' and 'Database' fields
7. In the advanced options, type SELECT * FROM [VEIWS].[VIEW_NAME] WHERE [EMP.ID] = '123456'
8. Click OK.

This is tedious for my users. If they want to check another person, they have to repeat the entire process. I'd love for them to just be able to use the query editor and change the only line that matters (see step 7), but they've only got the Select and View Definitions permission, which causes the query editor to complain. I'm afraid that I don't have the specific error message, but it's certainly to do with permissions.

Is there a less-repetitive way to do this from Excel? In an ideal world, I'd just make a sheet that lets them type in the EMP.ID immediately and then fetches the info. I think that it can be done with macros, but they're never my first choice and seem to require that I save passwords in the workbook.

Note that my users can't just fetch the entire view and filter it down in Excel. There are too many rows for Excel to handle.

Answer 1

I have no idea what permissions error you’re hitting, but people commonly use Windows credentials instead of Database credentials and get stuck. Power Query saves credentials on each computer, so you are relying on them signing in correctly. The first time someone connects to a data source, they are prompted for credentials. The default is for a Windows credential, and likely they need to enter a Database credential. If they get this wrong, they have to go into the Data Source settings to edit or clear the credential to fix it.

As far as changing the value in the SQL, you can easily have a parameter in Excel that changes the EMP.ID value in your query. Ken Puls has a nice write up on the process here. Reply back if you’re stuck.

Answer 2

You could use a SSAS Cube with a PivotTable in Excel with a filter on EMP.ID.

I guess it is not possible to change the query in Excel without Power Query Editor and I think it was not intended to do so (regulary).

If it does not need to be Excel you cloud just use SSMS or any similar alternative.

Answer 3

did you try to Un-tick the box that says "Require user approval for new native database queries" ?

you can set the ID as a parameter as suggested above... check my sample file for running an SQL query with a parameter. Sample File

also you can automatically refresh the worksheet with something like :

Private Sub Worksheet_Change(ByVal Target As Range)
     
    
    If Intersect(Target, Me.Range("datachange")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False 'to prevent endless loop
    
    'Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    Range("EMP_ID").ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    ActiveWorkbook.RefreshAll
    Application.EnableEvents = True

End Sub

Answer 4

I had a similar requirement in the past. My solution was to use the QueryTables object to send a query to the database using user-supplied data from a cell on the worksheet. It does use a macro, but I didn't have to save the credentials in the workbook.

This solution requires an ODBC driver for SQL Server.

(I seem to recall that I also had to check the references in Visual Basic - in toolbar Tools>References - but it was a while ago and I don't remember the exact details.)

Add the vb code below to a new workbook. Then if you enter the [EMP.ID] value in cell A1 of Sheet1 and run the macro 'ReadData', it will pull out the records and display them starting in cell A3.

Save the workbook as macro-enabled .xlsm and it can be shared with your users. (You could also assign the macro to a keyboard shortcut or command button to speed things up for your users.)

(This approach attempts to connect to the database using a trusted connection, i.e. using windows log-in credentials. I also use another database which requires separate credentials. I have another example below for that scenario.)

The vb code for the macro is below. Check the connection string that it has the correct driver and server IP address etc. and that the query string it reading the correct table.

Sub ReadData()
''' read database using filter supplied in cell A1

Dim ConnectionString As String
Dim QueryString As String


' Create connection string using credentials
ConnectionString = "ODBC; DRIVER={SQL Server}; SERVER=XX.XX.X.XXX; DATABASE=XXXXXXXXX; SCHEMA=dbo; REGION=yes;"
    
' Create query string to read data using value of cell A1
QueryString = "SELECT * FROM [VEIWS].[VIEW_NAME] WHERE [EMP.ID] = '" & Range("Sheet1!A1").Value & "'"

' The lines below can be un-commented if you get errors - it might help with debugging
'Range("Sheet1!C1").Value = ConnectionString
'Range("Sheet1!C2").Value = QueryString

' This code sends the query to the database and drops the results starting at cell A3
With Sheets("Sheet1").QueryTables.Add(Connection:=ConnectionString, _
        Destination:=Range("Sheet1!A3"), Sql:=QueryString)
    .RefreshStyle = xlOverwriteCells    ' this stops excel from inserting new columns when the query is re-run
    .Refresh False
End With
    
' Remove connections to avoid wasting memory
For Each con In Sheets("Sheet1").QueryTables
    con.Delete
Next

End Sub

When the database requires different credentials For this I created a user form to get the username and password, which I then incorporated into the connection string.

The steps I followed were:

1. In a new workbook, go to Visual Basic and create a new user form. Re-name it LoginForm
2. Create 2 text boxes on the form, named Username and Password. (You can also add labels and set the PasswordChar to '*' to make it look more like a login window.)
3. Create a command button ('OK' or 'Done'). Right click on it and select View Code. Enter the line Me.Hide in the code window so it looks like:

    Private Sub CommandButton1_Click()
        Me.Hide
    End Sub

4. The vb code for the macro changes to :

Sub ReadData()
''' read database using filter supplied in cell A1

Dim ConnectionString As String
Dim QueryString As String

' First time you run, need to show form to get credentials
If LoginForm.Username = "" Or LoginForm.Password = "" Then LoginForm.Show

' Create connection string using credentials
ConnectionString = "ODBC; DRIVER={SQL Server}; SERVER=XX.XX.X.XXX; DATABASE=XXXXXXXXX; SCHEMA=dbo; REGION=yes; uid=" _
    & LoginForm.Username & "; pwd=" & LoginForm.Password
    
' Create query string to read data
QueryString = "SELECT * FROM [VEIWS].[VIEW_NAME] WHERE [EMP.ID] = '" & Range("Sheet1!A1").Value & "'"

' The lines below can be un-commented if you get errors - it might help with debugging
'Range("Sheet1!C1").Value = ConnectionString
'Range("Sheet1!C2").Value = QueryString

' This code sends the query to the database and drops the results starting at cell A3
With Sheets("Sheet1").QueryTables.Add(Connection:=ConnectionString, _
        Destination:=Range("Sheet1!A3"), Sql:=QueryString)
    .RefreshStyle = xlOverwriteCells    ' this stops excel from inserting new columns when the query is re-run
    .Refresh False
End With
    
' Remove connections to avoid wasting memory
For Each con In Sheets("Sheet1").QueryTables
    con.Delete
Next

End Sub

Now, the first time the user runs the code, it will prompt them for username and password, but for the rest of their session it will keep using these values. They will not be saved when the workbook is closed. (If the macro hits an error they will probably be asked for credentials again next time it is run).

Hopefully this helps you. I did this work some time ago and I may have forgotten if there were any other set-up requirements needed.