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:
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?
Bookmarks