Why would you need database advice from anywhere else?!
I’ve been working on code to cross-reference data sources, tables or queries, against queries, tables, and forms. And I finally have a workable solution. I will publish it here, but it needs a little cleanup first.
I recently generated such a report and found a surprising number of reports that weren’t using a query for their recordsource, they had SQL statements instead. There’s nothing wrong with that, but all of a sudden I had to use a memo field for the cross-reference, which you can’t sort on.
The biggest offender? A select statement that named 22 fields, complete with the full name of the query, which was upwards of 40 characters long. And after all of those field names? A *. And there’s only the one table for the report.
I don’t know what I was thinking. Shouldn’t be too difficult to clean up.
I thought of a way to improve data security in my current project, but to implement it safely, I needed to review what linked tables were being used for what forms and reports. It’s kind of a daunting task, and if anyone knows of a free tool that could provide this for me, I’d be greatly appreciative. I’m working on some code, but it’s a slow path.
Under the Database Tools tab in later editions of Access is the Object Dependencies tool. Unfortunately, if you click on a linked table, the button goes gray and is unavailable. Fortunately, I found a workaround.
Click on a local table first, then open the tool. So far, so good. Now click on the table that you actually want to look at dependencies for, and click the Refresh link in the top right of the Object Dependencies pane that opened when you clicked on the tool’s button.
My thanks to Avon4026 on Access-Programmers.co.uk (a web site that I highly recommend) for pointing out this workaround.
I recently came across this post from Brent Ozar. CLR can represent a serious attack surface, and since Azure is a massively shared environment, MS decided this was not good and it must be secured. So CLR will be turned off for a while, I expect it’ll be available again in a month or two.
On March 31 Microsoft announced that SQL Server 2014 Developer Edition will now be free, though you have to be registered for their free Visual Studio Dev Essentials. So free, but you do have to be registered for their other free stuff. Not too bad of a deal. They explicitly state that SQL 2016 will also be released for free when it finally hits market, presumably all future editions will likewise be free.
I didn’t mind spending $50 for a DVD of Developer Edition, I thought the price was perfectly reasonable for what you get. Free is even better, but you need to manage your archives to preserve what you’ve downloaded. And we’ve always had the Express edition available for limited free deployment, as long as your app could be content with 1 gig of RAM and 10 gig of database size. And the absence of the Agent, forcing you to use the Task Scheduler to get your jobs running at scheduled times. It was a good way to learn alternative ways of doing things.
Does anyone remember Borland’s ‘Turbo’ books? They were really cool in the mid/late ’80s. Get a full compiler plus a text book on how to write in that language for a pretty good price. It’s a little silly to miss things like that with the free compilers that are available, especially if you have a Linux box running, and all of the free language tutorials that are all over the Web. Still, I kinda miss them.
The illustrious Phil Factor just released an interesting script over at Simple Talk that looks over your database and analyzes your tables for smells. In this case, table smells have a variety of definitions: a table that is wide (perhaps indicating it needs to be normalized), doesn’t participate in any relationships, doesn’t have a clustered index, etc. A variety of smells exist, it’s an outgrowth of a free ebook published by Red Gate called 119 SQL Code Smells that describe a variety of potential problems.
The book is a good read to drill in some best practices in SQL development by acknowledged luminaries in our field, and the script by Phil, along with some additional scripts in the comments where people would say ‘It would be great if it did X!’ and Phil provided just such a script. The script itself is an interesting study in examining some cool things that you can do by digging in to metadata.