Resize query columns

My MS-Access application has several buttons that display a query in datasheet view, so users can sort, filter or export the results. I'm frequently disappointed in the appearance when the columns are either too narrow or wide; of course you can easily resize columns with a double-click, but Access doesn't work the same as Excel, i.e. Access considers only the visible rows when it resizes.

I Google'd for a solution and found the article:
You Can Do That with Datasheets?
and the same code is presented here:
Resizing column to best fit vba
...and the answer relies on two important concepts:

• When you open a form in datasheet view, if you set ColumnWidth = -2 then Access will resize the columns to their proper widths.
• You can open a table or query in datasheet view, and then Dim and Set a Form object to it.
  

The code was a little wordy so I distilled it into the following:

Sub resize_query(query_name As String)
Dim frm As Form, ctl As Control
'
DoCmd.OpenQuery query_name, acViewNormal
Set frm = Screen.ActiveDatasheet
For Each ctl In frm.Controls
ctl.ColumnWidth = -2
Next ctl
DoCmd.Close acQuery, query_name, acSaveYes
End Sub

Now, if you have a Forms parameter in the query, you will need to have the corresponding form open when you run this code. Otherwise it will prompt you for the parameter(s).

EDIT on August 29:

I have been testing this code on a variety of queries and found that it does not work 100% of the time. Thinking that I might have left out something important, I returned to the original article mentioned above, and it also does not work 100% of the time. Will continue to test this out & hopefully post the final solution.

Stored procedure text

SQL Server has a system view sys.syscomments that holds the sql text for all of your stored procedures, views, triggers, UDF's, default constraints and computed columns.

I was considering using BULK INSERT to import a file but hadn't used it for a couple years, so I wanted to search some old scripts to get the basic syntax. I Google'd "SQL Server stored procedure text" and found a short article by Pinal Dave SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object which mentions sp_helptext, but you need to know the name of the sp in order to find it; catch-22.

So I opened up sp_helptext and found it was using sys.syscomments to get the sql text; from there it was easy to write:

SELECT OBJECT_NAME(id), [text]
FROM sys.syscomments
WHERE [text] LIKE '%BULK INSERT%'

...which in my case returned 7 rows. Eureka!

It is important to note that [text] is nvarchar(4000) so in the event that your sql text is larger than that, there will be multiple rows for that item, and the order is found in the column colid. So then, to get the complete text for an sp named "my_sp" you would use this:

SELECT object_name(id), colid, [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'my_sp'
ORDER BY 2

Now, sys.syscomments view does not have an identifier for the type of object, so if you normally use prefixes when naming your objects e.g. viw for views, trg for triggers etc. that will make it much easier to locate the desired sql text.

Drop temp table

By definition, when a batch completes in SQL Server, any local temp tables created are automatically dropped. So then, if your stored procedure creates a local temp table such as #temp it will be dropped when the sp completes, and you don't need to include the statement DROP TABLE #temp to release resources in tempdb.

However, when you are developing a stored procedure, and the batch fails due to some type of error, the temp table will remain; if you re-run the batch you will get a message that #temp already exists, and you need to drop the temp table before you can run it again.

You can include a statement like this at the beginning of your batch, to prevent that from happening:

IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL
DROP TABLE #temp