Excel as Front End to SQL server

[ajocius1]

I used to keep data in MS Access and used Excel as front end. I do read parameters user enters into excel, pass it to query and then return query result back to Excel.

I try to migrate this solution to SQL server. First impressions, SQL data selection is way faster. So I started build up Excel Front End. So I first tried to use MS Query. I can set up simple parameters and read parameters from Excel, then MS Query sends SQL request to SQL server. However I find MS Query difficult to use, especially on complicated parameters, when I use Like statement, addressing empty parameter issue (if user leaves parameter field empty, query should disregard it). So then I moved to VBA and then I have complete freedom, when it comes on reading users input in Excel sheet, converting it to SQL string and sending it through MS Query.

Next time I run VBA code it executes line:

Please Login or Register  to view this content.

Statement WHERE has anything from 1 to MANY criterias, depending on how many values user enters. So far so good, it works now.

My question is if this method is bullet proof? Will I not run into some kind of problem later? I think SQL string part should be safe to use, but path to SQL server was kind of hardcoded once I first connected to SQL server via MS Query and did my first retrieve. Can sending file to another user via email create issues (even if he has access to server ) . Not sure where that link between my excel file and SQL server resides. Does MS query save all necessary data within Excel file, or can something be on my computer, so when I share file it will not work?

Any thoughts about using this method?

[slx]

You will have the same file sharing issues for any file on network. I.e. if you hard code the an IP address and your ip address changes, well it is not going to work, or if the host name or filename changes, well its not going to find it. Unless your db access is IP restricted, you should be able to run that excel file from any workstation with Excel as long as that workstation is connected to the same network as the db.

Using an excel file to query a db means you have all credentials in that excel file. Generally the biggest issue is that it is a security risk. Someone gets the file opens up VBA and see the sql login information.

[ajocius1]

thanks!
Server address is like \\mmasdfa\asdfads and so on... So this is address that i reachable within company network, can't be reached from outside. But also this should be safe to use, as server name should not change. Many applications are addressing same server by name, so I hope it is safe to use server name.

Server access is via Windows authentication, so I need to enable users one by one on server side. Since I do not enter password when I access SQL server, I guess it uses my windows log in details. And so it will use other users windows log in details to access SQL server (provided I enable them on server side)