Eric M Russell (7/17/2014)[hrRegarding SSIS, there are situations where I'll implement all my ETL as parameter and expression driven T-SQL tasks and just rely on Control Flow to tie all the workflow and auditing together. One reason is client specific schemas and column definitions, and the Data Flow tasks can be too rigid. The problem with WYSIWYG is that we can't see everything, unless we dig around inside all the dialogs and often times what options we're given are limited.

I agree.I make use of parameters and I try to limit SSIS to control flow only, but the darn program sometimes gets in the way.

Now that I am building ETL apps in SSIS, I long for the days when I could script out my ETL process and did not have to deal with the limitations of a wysiwyg app.

--

Have you investigated BIML?

I just read through the BIML Stairway ( http://www.sqlservercentral.com/stairway/100550/ ).The author did a great job of introducing BIML and showing how it can be a useful tool.

I played with BIML a bit last night and it feels a lot like a band-aid.Combining a wysiwyg tool with XML with SQL scripts/code with n level graphical dropdown lists just seems like MS trying to force their graphical only tool set mantra into a script based world. Not an optimal solution.Although I am not very impressed with the implementation or user friendliness of BIML, it certainly seems to be a much more intelligent way for one to write complicated SSIS type packages as opposed to using only SSIS.

Thanks for pointing it out.I am going to try and find a project at work that I can use BIML on.

I started my career as an underwriter in an insurance company, with the side job of maintaining the Access 2.0 database (with a VB front end). After about 6 months in production I told management we would need to do something soon, as performance was starting to deteriorate, and at an increasing speed. For months they denied it would ever be a problem as their "highly paid consultants" said it would work fine for at least 5 years at the rate the AS/400 had been increasing the number of policies.

What they did not forecast was that the new system could give out quotes in 1/5th the time, causing us to get a lot more customers (we tripled the number of quotes we could do in a day the first week after the switch). After getting one of the network engineer to prove to the rest why it was getting slower the CIO turned to me "ok Anders, looks like you are right. What do we do?" At that point I was 6 months out of college, had never seen a professional relational database engine in my life. "Well, I hear about this new product called Microsoft SQL Server, I think we should look at it"

And that is how I became a SQL DBA. So while I do not like Access, I do owe my career to it ;)

Dalkeith (7/18/2014)I personally like MS Access. Its sweet spot is definitely complicated internal systems with limited distribution and users but that covers a great deal of systems.

I would prefer to use SQL Server as the back end and just use Access for UI design.

I also believe it is an excellent tool for teaching database design principles and UI design principles.It is probably undervalued as a UI design tool.

I'd have to agree. There was a lot of power built-in, if you only know where to go to tap into it. Interestingly enough there were also a lot of tricks to "dummy-proof" applications, if you only went looking for those: the ability to use external MDE's as data and UI "libraries" made it easy to deploy ongoing changes to everyone, without giving the end-users unfettered access to every product table.

Of course - you had to WORK to leverage some of these items: it's easier to build a giant security violation thant is it not to :)

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt, I think that's true of a lot of tools. Few have security baked-in in a way that leads/forces users to build something secure. Security is almost always something you have to know to do, rarely something you're prompted for. Imagine if every create table launched a security settings dialog - maybe the world would be a safer place!

Agreed! Any time you're in a dev tool, you can ultimately make something secure or insecure.

That said - I do think that some of the newer VS improvements (especially in some of the "drag and drop" type UI controls) would tend to yield a somewhat higher level of security than those you get out of the comparable wizards in Access. Might be a good time to revisit those functions in Access and "help" the users along more; If you believe the adage that devs by default will follow the quickest and easiest path, simply making it harder to make something NOT secure would increase the overall security of the application :)

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

[b]GoofyGuy (7/17/2014)The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.

I'm also an Access MVP but I wanted to comment on this point in particular. I totally agree with it. And therein lies the problem. IT in particular has had a disdain for Access, for just that reason, because it takes control away from them. But the answer is NOT to disdain Access, but to embrace it. To allow and support its use by controlling how its used.

The days when a user can come to IT with an application request and IT can take it under advisement and return a 12-18 month time frame for implementation are gone and IT needs to realize it. If they tell the user this, the user can think well I can build this in Access in 12-18 DAYS. And so what do you think they are going to do?

As many here have acknowledged Access is a great tool, when used properly. With best practices forced on the user and IT oversight, it can be a very workable tool that can rapidly develop applications that can make workers more productive.

[b]GoofyGuy (7/17/2014)The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.

IT in particular has had a disdain for Access, for just that reason, because it takes control away from them. But the answer is NOT to disdain Access, but to embrace it. To allow and support its use by controlling how its used.Scott

In an ideal world, I'd agree with you, Scott.

The problem is, it's not an ideal world - particularly when business users 'develop' in Access, without first informing IT. This happens even to engaged, valued IT departments - not just those sorry ones which lost the business users' trust and confidence.

Ergo, it's a little hard to control how it's used. Our IT director has let other department heads know that if they want to use Access without our support, they're on their own.